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