Import a textfile to MS SQL with python

[Steve Holden]

joel.sjoo at gmail.com wrote:
> I'm a dba for SQL server and I Will import a textfile to SQL. For
> example I use a file with 3 columns. ID, Name and Surname and the
> columns are tab separated. I don't know much about programming.
> Anyway, I use this code below. It works, but it will not split the
> columns. I have tried to change the argumnts in str(alllines[]) Some of
> the columns can include many characters and some not. For exampel names
> can be Bo or Lars-Ture.
> 
> I be glad if some can help me with this.
> 
> Regar Joel
> 
> import pymssql
> import string,re
> 
> myconn =
> pymssql.connect(host='lisa',user='sa',password='AGpu83!#',database='junk')

Thanks for letting us know the administrator password for your database. 
You might want to consider changing it (unless you modified this line 
before posting).

> mycursor = myconn.cursor()
> 
> inpfile=open('c:\\temp\\test.txt','r')
> for alllines in inpfile.read().split('\n'):
> 	stmt="insert into python (id, namn, efternamn) values ('%s', '%s',
> '%s')" %(str(alllines[0]),str(alllines[2:10]),str(alllines[3:10]))
> 
> mycursor.execute(stmt)
> 	print stmt

This is much better expressed as something like the following (untested):

stmt = "insert into python (id, namn, efternamn) values (?, ?, ?)"
for line in inpfile:
     mycursor.execute(stmt, tuple(line.split()))

Note that the "(?, ?, ?)" list of parameter markers assumes that pymssql 
uses the "qmark" paramstyle, you'll have to check the documentation if 
you get SQL syntax errors or similar - I couldn't easily find a 
reference on the web.

The point of passing the tuple of data values as a second argument to 
the .execute() method is to have the DB module take care of any 
necessary quoting and representation issues. Otherwise values that (for 
example) include a singel quotes, such as "O'Reilly" can be problematical.

> inpfile.close()
> myconn.commit()
> myconn.close()
> 

regards
  Steve
-- 
Steve Holden       +44 150 684 7255  +1 800 494 3119
Holden Web LLC/Ltd          http://www.holdenweb.com
Skype: holdenweb       http://holdenweb.blogspot.com
Recent Ramblings     http://del.icio.us/steve.holden