Old School Processing

[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]

Perfect.  Thanks

[stanl]

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

This did:

Code (WINBATCH) Select Expand


#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]

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

That is because you didn't use the function correctly...

[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.