I do a fair amount of data processing migrating between CSV and Oracle and mysql databases on both windows and linux and the weapon of choice on the windows side is ActiveState perl.
You might want to persuade the IT people to let you use perl instead of VBscript. I can think of a couple compelling arguments right off the top of my head.
1. VBscript is crap!
2. Perl offers you amazing flexability to deal with CSV files. Either through direct manipulation using split/map/pack/unpack, to the CSV libraries, to the DBD libraries so you can treat the CSV file as a database and write SQL against it.
3. Perl offers equally great flexibility using DBI/DBD to interface and maniuplate databases, including MSSQL.
4. All your code will be immediately multiplatform.
I'm sure it'll all fall on deaf eyes... just my two cents...
Thanks,
Tim.
On 7/25/07, Stephen Daukas <scd@daukas.com> wrote:
There are a lot of things I'd rather use, but I have to stick with the IT standards...
On 7/25/07, Michael Zarozinski
<
MichaelZ@aiguru.com> wrote:I think you'd be far better off using a scripting language such as perl,
it's far easier to manipulate text files with perl.
Michael Z
Stephen Daukas wrote:
> Good morning!
>
> In the continuing saga of automating various activities where I
> work, I have taken on processing data files. (Automating data upload
> is still somewhere in the air.)...
>
> This is rather simple in concept, but a bit less obvious in practice
> (aren't they all!):
>
> I would like to open up a CSV file and rearrange the columns according
> to a standardized order. Not every data file will have all of the
> standard columns (different probes collect different data), but column
> headers are standardized. So, I need to inspect each column header so
> that I can place it and its entire column of data into the
> proper position in the table, with empty columns inserted where needed
> to preserve the standard sequence of columns. In other words:
>
> A, D, C, G, H becomes A, [B], C, D, [E], [F], G, H
> where [ ] denote inserted empty columns
>
> There are literally hundreds of files of data in CSV format, and I
> intend to process each by itself, preserving the original, and
> creating a new "adjusted" file for the next step. (The next step
> would be to concatenate all files together for numeric processing.)
> Original files must be preserved (chain of custody requirements), so I
> am opening the file, selecting records using SQL, and am now ready to
> do the rearranging/writing to a new file...
>
> I didn't know if there is a "well known" SQL way of doing this (
e.g.,
> data manipulation statements of some sort), so I thought I'd see if
> any WLUGgers are also SQL-types. I have a few ideas for how to get
> this done, but they are "programmer-centric" algorithms, which tend to
> be different from "SQL-centric" algorithms...
>
> The reason for SQL is that I have to do this on a MS platform because
> that is the standard. But, if most of the real work is done in SQL,
> then I can easily move to another platform in the future and only have
> to change the "shell code". I am using VBscript as the "shell
> language" because it works with all MS apps and file system objects
> inclusive of CSV files.
>
> Thanks for any help!!!
>
> Best regards,
> Steve
> ------------------------------------------------------------------------
>
> _______________________________________________
> Wlug mailing list
> Wlug@mail.wlug.org
>
http://mail.wlug.org/mailman/listinfo/wlug
>
_______________________________________________
Wlug mailing list
Wlug@mail.wlug.org
http://mail.wlug.org/mailman/listinfo/wlug
_______________________________________________
Wlug mailing list
Wlug@mail.wlug.org
http://mail.wlug.org/mailman/listinfo/wlug
--
I am leary of the allegiances of any politician who refers to their constituents as "consumers".