Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Python > parsing flat file to mssql (odbc)

Reply
Thread Tools

parsing flat file to mssql (odbc)

 
 
.d.hos
Guest
Posts: n/a
 
      07-08-2003
ok, fairly new to python, relatively familiar w/ ms-sql. here's my
issue:

my .py script parses the contents of a (tab delim.) flat file, then
attempts to insert the information into the db. I've been fighting
this for a day or so, and i'm stuck on the db insertion...

basically the script uses .readlines() to capture the flat file
contents, and stick the *record* into a container list...

something to the effect of:
for l in lines:
fields = string.split(l,'\t')
dic.append(fields)

then, I just want to loop over that container list and do a simple
INSERT statement. I've tried using the .executemany() method to no
avail. .execute(sql, tuple) seems to be working better.

sql statement (lots of columns):
---------------------------------
INSERT INTO tbl_pyDev
(COURSE_SECTIONS_0,Term_1,Synonym_2,Section_Name_3 ,Location_4,Bldg_5,Room_6,
Days_7,Start_Time_8,End_Time_9,Start_Date_10,End_D ate_11,Add_Start_Date_12,
Drop_Start_Date_13,Add_End_Date_14,Faculty_15,Shor t_Title_16,Prerequisite_17,
Required_18,Coreq_Noncourses_19,Course_20,Cred_Typ e_21,SEC_CRS_DESC_22,
Long_Title_23,Depts_24,Fee_25,Meeting_Days_26,Prin ted_Comments_27,Subject_28,
Supplies_29,Transfer_Status_30,Course_Cost_31,Stat us_32,Capacity_33,
COURSE_SECTIONS_34,Corequisite_Sections_35,Section _36,Min_Cred_37,
Instr_Methods_38,SEC_FACULTY_FIRST_NAME_39,SEC_FAC ULTY_LAST_NAME_40,
Refund_41,CoReq_Name_42,Drop_End_43) values (?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?, ?,?,?,?,?,?,?)

ugly eh?
well, here's an example of what I'm trying to feed this beast:
(the first row of data is col. headers, and thy insert fine. This is
actually the chunk of data the script is choking on (dic[1]))
---------------------------------------------------------------
('2475', '03/FA', '25000', 'AAA-010-AS01', 'AS', 'AS', '116', 'TTH',
'05:00PM', '06:50PM', '09/09/03', '10/02/03', '09/09/03', '09/09/03',
'09/26/03', 'Rameil, Lesley J', 'Aca Achieve Pre-College', '', '', '',
'1139', 'UG', "Meets the requirements of the Comprehensive Student
AssessmentSystem and the Secretary's Commission on Achieving Necessary
Sklls, as well as work and postsecondary enrollment skills. Enabes
the student to review and improve in reading, writing, matheatics,
science, and social studies in preparation for the GED tst.",
'Academic Achievement in Pre-College', 'AAA', '', 'T', '', 'AAA', '',
'NT', '', 'A', '35', '2475', '', 'AS01', '1.00', 'LEC', 'Lesley',
'Rameil', '09/12/03', '', '09/26/03')

I'm executing in this sort of fashion:
--------------------------------------
for data in dic:
cursor.execute(insertSQL, tuple(data))

Error:
---------------------------------------
Traceback (most recent call last):
File "C:\Python22\parse_flat.py", line 101, in ?
cursor.execute(insertSQL, tuple(data))
dbi.internal-error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Location: record.cpp:2253
Expression: m_futureSize == 0 || rec.Size () == m_futureSize
SPID: 58
Process ID: 1136 in EXEC



so, if the column headers make into the db, it has to be the
formatting of the following data?!?! single quotes?!?! i'm fried on
this...if anyone has any input I would be very grateful.

thanks - d
 
Reply With Quote
 
 
 
 
.d.hos
Guest
Posts: n/a
 
      07-14-2003
>
> Strange error message you have there...
>
> Have you tried using mxODBC at this ? I'd bet you get much better
> results.
>
> --
> Marc-Andre Lemburg
> eGenix.com



Mark,
thanks for your suggestion, it seems to be working w/ mxODBC.
 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
save a flat export data file as an excel file c676228 ASP General 3 07-17-2008 06:02 PM
XSLT to transform a "flat" XML file into a structured text file R. P. XML 3 06-22-2006 02:26 AM
Problems parsing a flat text file with pointers.. sort of named.conf layout.. Hugh C Programming 4 06-03-2005 10:22 PM
parsing a fixed-width flat file to sql fields clintsy Java 1 03-05-2004 10:15 PM
DBI problem inserting html into mssql db Kristian Perl 0 11-13-2003 12:23 PM



Advertisments