WinBatch® Technical Support Forum

All Things WinBatch => WinBatch => Topic started by: stanl on March 17, 2017, 07:20:11 AM

Title: Old School Processing
Post by: stanl 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.
Title: Re: Old School Processing
Post by: JTaylor 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
Title: Re: Old School Processing
Post by: stanl 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
Title: Re: Old School Processing
Post by: JTaylor on March 17, 2017, 03:06:57 PM
May need a

        ChrUnicodeToString( Unicode-string )

in there as well.

Jim
Title: Re: Old School Processing
Post by: stanl on March 18, 2017, 03:51:27 AM
Perfect.  Thanks
Title: Re: Old School Processing
Post by: stanl on March 19, 2017, 03:43:02 AM
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

Title: Re: Old School Processing
Post by: JTaylor 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
Title: Re: Old School Processing
Post by: stanl 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.
Title: Re: Old School Processing
Post by: td on March 20, 2017, 06:36:49 AM
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...
Title: Re: Old School Processing
Post by: stanl 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.
Title: Re: Old School Processing
Post by: td on March 20, 2017, 12:54:29 PM
Not a "moot" point for other readers.