Formatting question.

[M.-A. Lemburg]

Dennis Lee Bieber wrote:
> On Wed, 21 Nov 2007 08:36:28 -0800 (PST), mike5160 <mike5160 at gmail.com>
> declaimed the following in comp.lang.python:
> 
>> Thanks to you for your reply. I  am a newbie to Python and appreciate
>> you helping me. Now, I am importing data from an excel sheet and
>> getting it ready for a derby database. I am to use netbeans, since our
>> research team uses that. However, derby database uses sql entries to
>> update the database. And I m trying to format all the excel data I
>> have, which I got from using labview. I suggested that we use awk/perl/
>> python etc. and finally after looking at the documentation available I
>> figured Python would be best. However, (see my reply above) I am
>> looking for a sample book/document etc. somebody suggested we try
>> Python Phrasebook. But that one covers a lot of different fields
>> whereas for my purposes I need a book with examples on using Python in
>> the above manner. If you or anybody knows about this kind of book
>> please let me know.
>>
> 	Unfortunately, you probably won't find any single book...
> 
> 	Parsing fixed format (if still variable line length) text files is
> simplified by Python's string.split() and slicing, but those are just
> built-in functions for simple algorithms that are language independent.
> You might find it under the term "tokenizing"
> 
> 	Formatting SQL statements is... SQL... a totally separate language,
> hypothetically standardized but having lots of DBMS specific dialects.
> 
> 	Also, you appear to be looking at it from the direction of
> translating tab separated output file from Excel into a sequence of SQL
> insert statements which will be written to another file, then "batched"
> into some DBMS command line interpreter. That means that you will have
> to be responsible for knowing how to escape special characters, properly
> indicating nulls, etc.
> 
> 	Presuming http://db.apache.org/derby/ is the DBMS you mention, I
> wonder if you would not be better off converting the Excel data into an
> XML file of the type wanted by
> http://db.apache.org/derby/integrate/db_ddlutils.html
> 
> 	Otherwise, I'm afraid to say, I'd suggest coding the Excel parser
> /in/ Java, and use JDBC to directly insert the data... (If there were an
> ODBC compatible driver, I'd suggest using a Python ODBC adapter and
> doing it from Python).

FYI: There is an Excel ODBC driver for Windows which is included in
the Microsoft MDAC package. Using it, you can query Excel tables
with SQL. mxODBC works great with it. OTOH, if you're on Windows
anyway, you can also use the win32 Python package and then tap
directly into Excel using COM.

> 	If using the "ij" utility from a command line, please note that it
> supports multiple record insert; instead of
> 
> insert into <table> values (a, ..., z);
> insert into <table> values (a2, ..., z2);
> ...
> insert into <table> values (aX, ..., zX);
> 
> you can use
> 
> insert into <table> values
> (a, ..., z),
> (a2, ..., z2),
> ...
> (aX, ..., zX);
> 
> though there may be a limit to how long the statement can be -- maybe
> run in batches of 25-50 records at a time...
> 
> 
> 
> 
> 
>  
>> Thank you very much for your help,
>> Mike.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Source  (#1, Nov 22 2007)
>>> Python/Zope Consulting and Support ...        http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ...             http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________

:::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,MacOSX for free ! ::::


   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611