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

[Michael Long]

Chuck Anderson wrote:
> On Wed, Jul 25, 2007 at 09:29:43AM -0400, Stephen Daukas wrote:
>> 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...
> 
> So you are selecting records out of a CSV file using SQL?  How about 
> this:
> 
> select columnB,columnG,columnA,columnC,columnF from foo;
> 
> That will take the columns and return them in the given order.
> _______________________________________________

That will work if the column order of the csv file is known. If the 
files come in with the columns in different orders and you have to 
examine the headers to determine the order or the data then it becomes 
more complex.

If he really wants to do this in sql then you could bulk load the data 
into a worktable with all columns defined and char or varchar. When the 
data is loaded make sure you have a unique key for each row by defining 
one of the columns as an identity column.

create table foo (
	col1 identity(1,1), col2 varchar(25), col3 varchar(25)
)

Once the data is imported you could right a sql script that will move 
the data based on the value found in the first record.

if (select col2 from foo where col1 = 1) = 'myHeader'
begin
	insert into myTable(myHeader)
	select col2 from foo
end

if (select col3 from foo where col1 = 1) = 'myHeader'
begin
	insert into myTable(myHeader)
	select col3 from foo where col1 > 1
end

Repeat this as necessary. Also be careful to keep the rows ordered as 
you move the data around. You will also need to reset the identity value 
between loads or come up with a method to deal with an incrementing number.