473,320 Members | 1,902 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

CSV Import

Jim
I have an asp.net (vb) application in which users upload csv files to a
SQL Server 2000 database.

I've been using SQL Server DTS and stored procs to do this however I'd
like the solution to be a bit more flexible.

I've developed code to pull the csv file into a dataset using
oledbConnection/oledbCommand/oledbDataAdapter however I am unsure how
to insert the data into an existing SQL server table.

So, I've got....

Dim strFileName As String = "MyFile.csv"
Dim strPath As String = "C:\"
Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + strPath + ";Extended
Properties='text;HDR=Yes;FMT=Delimited'"
Dim con As New OleDbConnection(strCon)
Dim cmdGetCsv As New OleDbCommand("SELECT * from " +
strFileName, con)
Dim csvAdapter As New OleDbDataAdapter(cmdGetCsv)
'Dim csvReader As OleDbDataReader
con.Open()

Dim dsCsv As DataSet = New DataSet

csvAdapter.Fill(dsCsv)

....

Clearly I now need to make a connection to my SQL db but how do I
insert all the records from the dataset?

The csv file could contain thousands of records. Are there performance
issues that would dictate one method over another?

Thanks in advance for any assistance.

Nov 19 '05 #1
5 1542
"Jim" <em********@yahoo.com> wrote in news:1129216898.289022.174330
@g47g2000cwa.googlegroups.com:
Clearly I now need to make a connection to my SQL db but how do I
insert all the records from the dataset?

The csv file could contain thousands of records. Are there performance
issues that would dictate one method over another


You need to open another connection, and basically copy the dataset
contents to SQL server.

Couple things to note:

Datasets use memory - so if you have a large CSV file, it could suck up
all of your servers memory.

Bulk Inserting via ADO.NET is slow.

You can also use a textreader and read each line and insert the data
into SQL server - again this is slow.

Ideally, you would use DTS, BCP or Bulk Inserts to copy data into SQL
server quickly. BCP and Bulk Inserts should work fine in a ASP.NET
environment.

--
Lucas Tam (RE********@rogers.com)
Please delete "REMOVE" from the e-mail address when replying.

Newmarket Volvo Sucks! http://newmarketvolvo.tripod.com
Nov 19 '05 #2
you have a couple options,

1) loop thru the dataset and issue a insert for each row
2) copy the data to a new dataset (to get the row status = added), then use
a dataadapter.

this will be much slower than DTS which does a BCP insert. .net (nor ado)
does not support the bcp library, you would need to use the native ODBC
library to do this.

-- bruce (sqlwork.com)

"Jim" <em********@yahoo.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
I have an asp.net (vb) application in which users upload csv files to a
SQL Server 2000 database.

I've been using SQL Server DTS and stored procs to do this however I'd
like the solution to be a bit more flexible.

I've developed code to pull the csv file into a dataset using
oledbConnection/oledbCommand/oledbDataAdapter however I am unsure how
to insert the data into an existing SQL server table.

So, I've got....

Dim strFileName As String = "MyFile.csv"
Dim strPath As String = "C:\"
Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + strPath + ";Extended
Properties='text;HDR=Yes;FMT=Delimited'"
Dim con As New OleDbConnection(strCon)
Dim cmdGetCsv As New OleDbCommand("SELECT * from " +
strFileName, con)
Dim csvAdapter As New OleDbDataAdapter(cmdGetCsv)
'Dim csvReader As OleDbDataReader
con.Open()

Dim dsCsv As DataSet = New DataSet

csvAdapter.Fill(dsCsv)

...

Clearly I now need to make a connection to my SQL db but how do I
insert all the records from the dataset?

The csv file could contain thousands of records. Are there performance
issues that would dictate one method over another?

Thanks in advance for any assistance.

Nov 19 '05 #3
Jim
Lucas and Bruce,

Thanks for the replies. I'm already using DTS from my asp.net page via
stored proc and was hoping there was another way.

Would converting the csv to XML allow for any additional options
without some of the performance issues?
Thanks!

Nov 19 '05 #4
On 13 Oct 2005 08:21:38 -0700, "Jim" <em********@yahoo.com> wrote:

¤ I have an asp.net (vb) application in which users upload csv files to a
¤ SQL Server 2000 database.
¤
¤ I've been using SQL Server DTS and stored procs to do this however I'd
¤ like the solution to be a bit more flexible.
¤
¤ I've developed code to pull the csv file into a dataset using
¤ oledbConnection/oledbCommand/oledbDataAdapter however I am unsure how
¤ to insert the data into an existing SQL server table.
¤
¤ So, I've got....
¤
¤ Dim strFileName As String = "MyFile.csv"
¤ Dim strPath As String = "C:\"
¤ Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
¤ Source=" + strPath + ";Extended
¤ Properties='text;HDR=Yes;FMT=Delimited'"
¤ Dim con As New OleDbConnection(strCon)
¤ Dim cmdGetCsv As New OleDbCommand("SELECT * from " +
¤ strFileName, con)
¤ Dim csvAdapter As New OleDbDataAdapter(cmdGetCsv)
¤ 'Dim csvReader As OleDbDataReader
¤ con.Open()
¤
¤ Dim dsCsv As DataSet = New DataSet
¤
¤ csvAdapter.Fill(dsCsv)
¤
¤ ...
¤
¤ Clearly I now need to make a connection to my SQL db but how do I
¤ insert all the records from the dataset?
¤
¤ The csv file could contain thousands of records. Are there performance
¤ issues that would dictate one method over another?
¤
¤ Thanks in advance for any assistance.

Why not just cut out the middle man by using BULK INSERT?

http://msdn.microsoft.com/library/de...ba-bz_4fec.asp
Paul
~~~~
Microsoft MVP (Visual Basic)
Nov 19 '05 #5
You have 3 options listed bellow. Number 3 is my favorite because of flexibility

1. If you already have DTS package that does it then you can run it from your program.
Do not remember how but you can simply look it up by scheduling the DTS package and then check the SQL the scheduled job is going to run.

2. Use BULK INSERT - very fast. Read the topic about BULK INSERT to minimize the logging. It will work only if SQL server is on the same machine where your CSV file is.

3. Use bcp.exe which is the same as BULK INSERT but can work with the local file. Meaning that if SQL server resides on different machine then IIS it's probably your best choice. Try to minimize the logging as well here.
George.

"Jim" <em********@yahoo.com> wrote in message news:11**********************@g47g2000cwa.googlegr oups.com...
I have an asp.net (vb) application in which users upload csv files to a
SQL Server 2000 database.

I've been using SQL Server DTS and stored procs to do this however I'd
like the solution to be a bit more flexible.

I've developed code to pull the csv file into a dataset using
oledbConnection/oledbCommand/oledbDataAdapter however I am unsure how
to insert the data into an existing SQL server table.

So, I've got....

Dim strFileName As String = "MyFile.csv"
Dim strPath As String = "C:\"
Dim strCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + strPath + ";Extended
Properties='text;HDR=Yes;FMT=Delimited'"
Dim con As New OleDbConnection(strCon)
Dim cmdGetCsv As New OleDbCommand("SELECT * from " +
strFileName, con)
Dim csvAdapter As New OleDbDataAdapter(cmdGetCsv)
'Dim csvReader As OleDbDataReader
con.Open()

Dim dsCsv As DataSet = New DataSet

csvAdapter.Fill(dsCsv)

...

Clearly I now need to make a connection to my SQL db but how do I
insert all the records from the dataset?

The csv file could contain thousands of records. Are there performance
issues that would dictate one method over another?

Thanks in advance for any assistance.

Nov 19 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Stian Søiland | last post by:
all examples performed with: Python 2.3+ (#2, Aug 10 2003, 11:09:33) on linux2 (2, 3, 0, 'final', 1) This is a recursive import:
0
by: Vio | last post by:
Hi, I've been trying to embed (statically) wxPy alongside an embedded py interpreter on a linux/gtk box. At one point, for some reason misc.o linking reported "multiple definitions of...
5
by: Steve Holden | last post by:
This is even stranger: it makes it if I import the module a second time: import dbimp as dbimp import sys if __name__ == "__main__": dbimp.install() #k = sys.modules.keys() #k.sort() #for...
7
by: Ron Adam | last post by:
from __future__ import absolute_import Is there a way to check if this is working? I get the same results with or without it. Python 2.5 (r25:51908, Sep 19 2006, 09:52:17) on win 32 ...
9
by: rsoh.woodhouse | last post by:
Hi, I'm trying to work out some strange (to me) behaviour that I see when running a python script in two different ways (I've inherited some code that needs to be maintained and integrated with...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.