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

[Eric Martin]

does awk compile on windows?

On 7/25/07, Michael Long <mlong at datalong.com> wrote:
>
> 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.
>
>
> _______________________________________________
> Wlug mailing list
> Wlug at mail.wlug.org
> http://mail.wlug.org/mailman/listinfo/wlug
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.wlug.org/pipermail/wlug/attachments/20070725/0ff309cf/attachment-0001.html