Import a textfile to SQL

[Supersonic]

I’m a dba for a SQL and I’m trying to learn how to import data from a simple text file. The text file looks like "1, name" "2, name2" and so on. How is it possible to solve this import with python? It’s no problem to connect to the database and do some insert and select statement. It is when I put the read from text file and then insert this data to the database I got problem.

One script I have tried looks like this:

import pymssql
import string,re
 
myconn = pymssql.connect(host='lisa',user='sa',password='',database='junk')
mycursor = myconn.cursor()
 
 
inpfile=open('c:\\temp\\test.txt','r')
lines=inpfile.readlines()
while lines <>'':
    lines = inpfile.readline()
inpfile.close()
 
stmt=("""insert into table (id, name) values (%s, %s)""" % ("'"+str(lines)+"'")
 
print mycursor
mycursor.execute(stmt)
 
myconn.commit()
myconn.close()
 

Regard Joel

[ghostdog74]

I’m a dba for a SQL and I’m trying to learn how to import data from a simple text file. The text file looks like "1, name" "2, name2" and so on. How is it possible to solve this import with python? It’s no problem to connect to the database and do some insert and select statement. It is when I put the read from text file and then insert this data to the database I got problem.

One script I have tried looks like this:

import pymssql
import string,re

myconn = pymssql.connect(host='lisa',user='sa',password='', database='junk')
mycursor = myconn.cursor()


inpfile=open('c:\\temp\\test.txt','r')
lines=inpfile.readlines()
while lines <>'':
lines = inpfile.readline()
inpfile.close()

stmt=("""insert into table (id, name) values (%s, %s)""" % ("'"+str(lines)+"'")

print mycursor
mycursor.execute(stmt)

myconn.commit()
myconn.close()


Regard Joel

you could try this:

import pymssql
import string,re
myconn = pymssql.connect(host='lisa',user='sa',password='',database='junk')
mycursor = myconn.cursor()
inpfile=open('c:\\temp\\test.txt','r')
while 1:
         line = inpfile.readline()
         if line == '':
                  break
         num , name = line.split()
         stmt = ("""insert into table (id, name) values (%s, %s)""" % (num, "'"+str(name)+"'")
         mycursor.execute(stmt)
 
 
myconn.commit()
myconn.close()
 

Untested, so you have to try it yourself