By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,595 Members | 1,433 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,595 IT Pros & Developers. It's quick & easy.

Insert database rows from CSV file

P: n/a
Hello,
I have a really simple Access database table with a format similar to this:
CustomerName - ProductOrdered - QtyOrdered

I have a CSV file with the appropriate values as follows:
Customer1, Widget1, 1000
Customer2, Widget2, 3000
etc

I have figured out how to insert the data manually from the interactive
prompt:
cursor.execute(""" INSERT INTO "Table1" Values ('Customer1', "Widget1',
'1000') """)

What I would like to do is iterate over the CSV file like this:
for lines in file:
cursor.execute(""" INSERT INTO "Table1" lines """)

I have googled and found some examples that use string formatting, but
without documentation I can't seem to find the right formula. I don't have
any problem with the iteration part, I just can't seem to figure out how to
use a variable to insert an entire row with the INSERT statement. Can anyone
get me going in the right direction? I'm using odbc from win32all,
Python2.3, and Access2000 if it matters. Thanks.
Louis
Jul 18 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
You may want to take a look at this link. It should
be much faster than any programmatic technique.

http://www.its.niu.edu/its/CSupport/...p_080502.shtml

If you still want to do it programmatically, you will need to
look at csv module to parse the lines.

Larry Bates

3c273 wrote:
Hello,
I have a really simple Access database table with a format similar to this:
CustomerName - ProductOrdered - QtyOrdered

I have a CSV file with the appropriate values as follows:
Customer1, Widget1, 1000
Customer2, Widget2, 3000
etc

I have figured out how to insert the data manually from the interactive
prompt:
cursor.execute(""" INSERT INTO "Table1" Values ('Customer1', "Widget1',
'1000') """)

What I would like to do is iterate over the CSV file like this:
for lines in file:
cursor.execute(""" INSERT INTO "Table1" lines """)

I have googled and found some examples that use string formatting, but
without documentation I can't seem to find the right formula. I don't have
any problem with the iteration part, I just can't seem to figure out how to
use a variable to insert an entire row with the INSERT statement. Can anyone
get me going in the right direction? I'm using odbc from win32all,
Python2.3, and Access2000 if it matters. Thanks.
Louis

Jul 18 '05 #2

P: n/a
Thanks for the link, but this is the step I am trying to save (for someone
else). Every time he goes to run a report, he must stop and import any new
csv files. Since the files are generated by a Python script, I thought I
could just insert them into his table and save him some steps. I'm also just
trying to learn the basics Python and SQL . Thanks again.
Louis

"Larry Bates" <lb****@syscononline.com> wrote in message
news:Cu********************@comcast.com...
You may want to take a look at this link. It should
be much faster than any programmatic technique.

http://www.its.niu.edu/its/CSupport/...p_080502.shtml

If you still want to do it programmatically, you will need to
look at csv module to parse the lines.

Larry Bates

3c273 wrote:
Hello,
I have a really simple Access database table with a format similar to this: CustomerName - ProductOrdered - QtyOrdered

I have a CSV file with the appropriate values as follows:
Customer1, Widget1, 1000
Customer2, Widget2, 3000
etc

I have figured out how to insert the data manually from the interactive
prompt:
cursor.execute(""" INSERT INTO "Table1" Values ('Customer1', "Widget1',
'1000') """)

What I would like to do is iterate over the CSV file like this:
for lines in file:
cursor.execute(""" INSERT INTO "Table1" lines """)

I have googled and found some examples that use string formatting, but
without documentation I can't seem to find the right formula. I don't have any problem with the iteration part, I just can't seem to figure out how to use a variable to insert an entire row with the INSERT statement. Can anyone get me going in the right direction? I'm using odbc from win32all,
Python2.3, and Access2000 if it matters. Thanks.
Louis

Jul 18 '05 #3

P: n/a
On Mon, 4 Apr 2005 15:54:37 -0700, "3c273" <no****@nospam.com> declaimed
the following in comp.lang.python:
Thanks for the link, but this is the step I am trying to save (for someone
else). Every time he goes to run a report, he must stop and import any new
csv files. Since the files are generated by a Python script, I thought I
That information wasn't supplied in the original message. Your
original post implied that the data source /was/ the CSV file...

Show us the code segment that is writing the CSV file, and we
can probably show you the DB-API equivalent for "writing" a new record
to the table.

For short however:

aCustomer = "Customer1"
theWidget = "Widget1"
aQuantity = 1000

# I'm presuming the table only has the three columns, since you didn't
list fields
cursor.execute(""" INSERT INTO "Table1" Values (%s, %s, %s) """,
(aCustomer, theWidget, aQuantity))
-- ================================================== ============ <
wl*****@ix.netcom.com | Wulfraed Dennis Lee Bieber KD6MOG <
wu******@dm.net | Bestiaria Support Staff <
================================================== ============ <
Home Page: <http://www.dm.net/~wulfraed/> <
Overflow Page: <http://wlfraed.home.netcom.com/> <

Jul 18 '05 #4

P: n/a
Dennis Lee Bieber wrote:
On Mon, 4 Apr 2005 15:54:37 -0700, "3c273" <no****@nospam.com> declaimed
the following in comp.lang.python:

Thanks for the link, but this is the step I am trying to save (for someone
else). Every time he goes to run a report, he must stop and import any new
csv files. Since the files are generated by a Python script, I thought I

That information wasn't supplied in the original message. Your
original post implied that the data source /was/ the CSV file...

Show us the code segment that is writing the CSV file, and we
can probably show you the DB-API equivalent for "writing" a new record
to the table.

For short however:

aCustomer = "Customer1"
theWidget = "Widget1"
aQuantity = 1000

# I'm presuming the table only has the three columns, since you didn't
list fields
cursor.execute(""" INSERT INTO "Table1" Values (%s, %s, %s) """,
(aCustomer, theWidget, aQuantity))


Beware, however, that the parameter markers ("%s" in the example above)
will depend on which database module you use - some modules will expect
"?", for example. This depends on the module's "paramstyle".

Also, don't forget to commit the changes!

regards
Steve
--
Steve Holden +1 703 861 4237 +1 800 494 3119
Holden Web LLC http://www.holdenweb.com/
Python Web Programming http://pydish.holdenweb.com/

Jul 18 '05 #5

P: n/a

"Dennis Lee Bieber" <wl*****@ix.netcom.com> wrote in message
news:bp********************************@4ax.com...
On Mon, 4 Apr 2005 15:54:37 -0700, "3c273" <no****@nospam.com> declaimed
the following in comp.lang.python:
Thanks for the link, but this is the step I am trying to save (for someone else). Every time he goes to run a report, he must stop and import any new csv files. Since the files are generated by a Python script, I thought I


That information wasn't supplied in the original message. Your
original post implied that the data source /was/ the CSV file...

Show us the code segment that is writing the CSV file, and we
can probably show you the DB-API equivalent for "writing" a new record
to the table.

For short however:

aCustomer = "Customer1"
theWidget = "Widget1"
aQuantity = 1000

# I'm presuming the table only has the three columns, since you didn't
list fields
cursor.execute(""" INSERT INTO "Table1" Values (%s, %s, %s) """,
(aCustomer, theWidget, aQuantity))


Ah.. Many thanks. This is what I was looking for.
Louis
Jul 18 '05 #6

P: n/a

"Steve Holden" <st***@holdenweb.com> wrote in message
news:ma**************************************@pyth on.org...
Dennis Lee Bieber wrote:
On Mon, 4 Apr 2005 15:54:37 -0700, "3c273" <no****@nospam.com> declaimed
the following in comp.lang.python:

Thanks for the link, but this is the step I am trying to save (for someoneelse). Every time he goes to run a report, he must stop and import any newcsv files. Since the files are generated by a Python script, I thought I

That information wasn't supplied in the original message. Your
original post implied that the data source /was/ the CSV file...

Show us the code segment that is writing the CSV file, and we
can probably show you the DB-API equivalent for "writing" a new record
to the table.

For short however:

aCustomer = "Customer1"
theWidget = "Widget1"
aQuantity = 1000

# I'm presuming the table only has the three columns, since you didn't
list fields
cursor.execute(""" INSERT INTO "Table1" Values (%s, %s, %s) """,
(aCustomer, theWidget, aQuantity))


Beware, however, that the parameter markers ("%s" in the example above)
will depend on which database module you use - some modules will expect
"?", for example. This depends on the module's "paramstyle".

Also, don't forget to commit the changes!

regards
Steve


Thanks for the heads-up. I think I'm well on my way now.
Louis
Jul 18 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.