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