Old School Processing

Started by stanl, March 17, 2017, 07:20:11 AM

Previous topic - Next topic

stanl

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

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

I'll try your suggestion tomorrow. My backup would be to use an ADODB.Stream object.   Thanks

JTaylor

May need a

        ChrUnicodeToString( Unicode-string )

in there as well.

Jim

stanl


stanl

Oops... spoke to soon. ChrUnicodeToString( Unicode-string ) didn't work.

This did:
Code (WINBATCH) Select

#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

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

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

Quote from: stanl on March 19, 2017, 03:43:02 AM
Oops... spoke to soon. ChrUnicodeToString( Unicode-string ) didn't work.

That is because you didn't use the function correctly...
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

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

Not a "moot" point for other readers.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade