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

[Tim Keller]

I do a fair amount of data processing migrating between CSV and Oracle and
mysql databases on both windows and linux and the weapon of choice on the
windows side is ActiveState perl.

You might want to persuade the IT people to let you use perl instead of
VBscript.  I can think of a couple compelling arguments right off the top of
my head.

1. VBscript is crap!
2. Perl offers you amazing flexability to deal with CSV files.  Either
through direct manipulation using split/map/pack/unpack, to the CSV
libraries, to the DBD libraries so you can treat the CSV file as a database
and write SQL against it.
3. Perl offers equally great flexibility using DBI/DBD to interface and
maniuplate databases, including MSSQL.
4. All your code will be immediately multiplatform.

I'm sure it'll all fall on deaf eyes... just my two cents...

Thanks,
Tim.

On 7/25/07, Stephen Daukas <scd at daukas.com> wrote:
>
> There are a lot of things I'd rather use, but I have to stick with the IT
> standards...
>
> On 7/25/07, Michael Zarozinski < MichaelZ at aiguru.com> wrote:
> >
> > 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
> > >
> > _______________________________________________
> > Wlug mailing list
> > Wlug at mail.wlug.org
> > http://mail.wlug.org/mailman/listinfo/wlug
> >
>
>
> _______________________________________________
> Wlug mailing list
> Wlug at mail.wlug.org
> http://mail.wlug.org/mailman/listinfo/wlug
>
>


-- 
I am leary of the allegiances of any politician who refers to their
constituents as "consumers".
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.wlug.org/pipermail/wlug/attachments/20070725/c8b58dd0/attachment.html