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
