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.

 

[Lucas Tam]

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.

 

[Bruce Barker]

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]

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!

 

[Paul Clement]

¤ 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/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[George]

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.

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.