471,119 Members | 1,469 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,119 software developers and data experts.

parsing flat file to mssql (odbc)

ok, fairly new to python, relatively familiar w/ ms-sql. here's my

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')

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):
(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,
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))

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
Jul 18 '05 #1
1 2559
Strange error message you have there...

Have you tried using mxODBC at this ? I'd bet you get much better

Marc-Andre Lemburg

thanks for your suggestion, it seems to be working w/ mxODBC.
Jul 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by mj | last post: by
2 posts views Thread by Josh Close | last post: by
3 posts views Thread by francisl | last post: by
14 posts views Thread by Kukurydz | last post: by
2 posts views Thread by Neural | last post: by
4 posts views Thread by kenneth.osenbroch | last post: by
reply views Thread by Gosth in the shell | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.