Author Topic: Old School Processing  (Read 463 times)

stanl

  • Pundit
  • *****
  • Posts: 612
Old School Processing
« on: March 17, 2017, 07:20:11 am »
I have a simple script that attempts to process SQL and .csv data into Access tables linked to Excel 2013 templates which have the data tables/queries linked but are refreshed by the script. Can all be done with Access Modules, but the sheer number of reports makes a compiled script work.  All is well except for one issue:

I get .csv data from a VDI where I have limited access and must use a web service to transmit .csv files to my local drive where they are saved.  With an Access macro I can use DoCmd() with Saved Import specs - but in order to use WB have to resort to ADO INSERT INTO SQL...  All is fine except the .csv files are saved as UTF-8 which is not recognized by the Ace Provider text driver - only ANSI.

So, probably a simple answer but would like a FileGet() .... FilePut()  that would re-save the .csv as ANSI once exported from email.

JTaylor

  • Pundit
  • *****
  • Posts: 721
    • Data & Stuff Inc.
Re: Old School Processing
« Reply #1 on: March 17, 2017, 09:11:22 am »
Tony will probably have a better answer but would FileGetW() and then FilePut() work?   Also, this topic might be relevant...

    http://forum.winbatch.com/index.php?topic=1798.0

Jim

stanl

  • Pundit
  • *****
  • Posts: 612
Re: Old School Processing
« Reply #2 on: March 17, 2017, 01:16:26 pm »
I'll try your suggestion tomorrow. My backup would be to use an ADODB.Stream object.   Thanks

JTaylor

  • Pundit
  • *****
  • Posts: 721
    • Data & Stuff Inc.
Re: Old School Processing
« Reply #3 on: March 17, 2017, 03:06:57 pm »
May need a

        ChrUnicodeToString( Unicode-string )

in there as well.

Jim

stanl

  • Pundit
  • *****
  • Posts: 612
Re: Old School Processing
« Reply #4 on: March 18, 2017, 03:51:27 am »
Perfect.  Thanks

stanl

  • Pundit
  • *****
  • Posts: 612
Re: Old School Processing
« Reply #5 on: March 19, 2017, 03:43:02 am »
Oops... spoke to soon. ChrUnicodeToString( Unicode-string ) didn't work.

This did:
Code: Winbatch
#DefineSubRoutine toansi(f)

S = CreateObject("ADODB.Stream")
S.Type = 1
S.Open()
S.LoadFromFile(f)
S.Type = 2
S.Charset= "utf-8"
strText = S.ReadText(-1)
S.Position=0
S.SetEOS()
S.Charset = "_autodetect"
S.WriteText(strText,0)
S.SaveToFile(f,2)
S.close()
S=0

Return(1)
#EndSubRoutine
 

JTaylor

  • Pundit
  • *****
  • Posts: 721
    • Data & Stuff Inc.
Re: Old School Processing
« Reply #6 on: March 19, 2017, 12:31:58 pm »
Sorry.   Wasn't sure if it would or not and why I referenced the other discussion.   What you did was probably as easy if not easier though.

Jim

stanl

  • Pundit
  • *****
  • Posts: 612
Re: Old School Processing
« Reply #7 on: March 20, 2017, 03:19:57 am »
There is some .net code I saw and I was thinking maybe trying it with the CLR (given some spare time). The one drawback of the stream method is with files over 40mb as it can take some time. The files I will be working with never exceed 5 mb and stream works incredibly well.

td

  • Tech Support
  • *****
  • Posts: 2055
    • WinBatch
Re: Old School Processing
« Reply #8 on: March 20, 2017, 06:36:49 am »
Oops... spoke to soon. ChrUnicodeToString( Unicode-string ) didn't work.

That is because you didn't use the function correctly...
"DON'T PANIC”
The Hitchhiker's Guide to the Galaxy, Douglas Adams


stanl

  • Pundit
  • *****
  • Posts: 612
Re: Old School Processing
« Reply #9 on: March 20, 2017, 10:53:01 am »
Probably right... but a moot point. The good news was that if I re-run the script after the files have already been re-saved as ANSI the stream code has no issues.

td

  • Tech Support
  • *****
  • Posts: 2055
    • WinBatch
Re: Old School Processing
« Reply #10 on: March 20, 2017, 12:54:29 pm »
Not a "moot" point for other readers.
"DON'T PANIC”
The Hitchhiker's Guide to the Galaxy, Douglas Adams