[Wlug] Any SQL-types out there? (more lab automation stuff)

[Michael Zarozinski]

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 at mail.wlug.org
> http://mail.wlug.org/mailman/listinfo/wlug
>