Import CSV file into SQL Server 3

[WelshyWizard]

Hi all,

I'm using Visual Studio 2003 and SQL Server 2000.

I am looking to insert the contents of a csv file into an existing SQL Server table. I would like to do this via VB.NET.

Could someone post some code to show me how to do this? Is it easier if I import the csv into a datagrid and then update the SQL table from there? If it is I already use the following code to populate a datagrid with the csv file.

        If OpenFileDialog1.ShowDialog(Me) = DialogResult.OK Then
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName
            Dim objConn As New OleDbConnection(sConnectionString)
            objConn.Open()
            Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & fi.Name, objConn)
            Dim objAdapter1 As New OleDbDataAdapter
            objAdapter1.SelectCommand = objCmdSelect
            Dim objDataset1 As New DataSet
            objAdapter1.Fill(objDataset1, "test")
            DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
            objConn.Close()
        End If

How would I then get the datagrid contents into the table.
If this isn't the best way of going about it... what is?

Cheers

Today is the tomorrow you worried about yesterday - and all is well.....

 

[computerjin]

WelshyWizard,

1...
It is a valuable resource for you but in C# and not in VB.NET. If you can translate it to VB.NET, it will be helpful to you.

http://www.codeproject.com/aspnet/ImportExportCSV.asp

2...
Other way is to use an OleDbCommand object and assign it the BULK INSERT sql statement. In this way, you can insert thousands of records in CSV file with just a single statement.

Examples:

-- CSV file is comma delimited.
BULK INSERT [TABLE_NAME] 
    FROM 'c:\file.csv' 
    WITH 
    ( 
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

-- CSV file is tab delimited.
BULK INSERT [TABLE_NAME] 
    FROM 'c:\file.csv' 
    WITH 
    ( 
        FIELDTERMINATOR = '\t', 
        ROWTERMINATOR = '\n' 
    )

For reference visit

http://sqlserver2000.databases.aspfaq.com/how-do-i-load-text-or-csv-file-data-into-sql-server.html

Hope it helps.

 

[WelshyWizard]

Thanks for the tips.

I've actually cracked it and thought it might be useful to post how I did it.

Firstly I imported the csv file into a datagrid:

        If OpenFileDialog1.ShowDialog(Me) = DialogResult.OK Then
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;Data Source=" & fi.DirectoryName
            Dim objConn As New OleDbConnection(sConnectionString)
            objConn.Open()
            Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & fi.Name, objConn)
            Dim objAdapter1 As New OleDbDataAdapter
            objAdapter1.SelectCommand = objCmdSelect
            objAdapter1.Fill(objDataset1, "test")
            DataGrid1.DataSource = objDataset1.Tables(0).DefaultView
            objConn.Close()
        End If

Then, I take the datagrid data and insert it into a SQL Server table usinga for loop to scan through the datatable:

SqlConnection1.Open()
                For Each dr As DataRow In objDataset1.Tables(0).Rows
            SqlCommand2.Parameters("@F1").Value = dr("Cont#")
            SqlCommand2.Parameters("@F2").Value = dr("Str#")
            SqlCommand2.Parameters("@F3").Value = dr("Modeller       ")
            SqlCommand2.Parameters("@F4").Value = dr("Created")
            SqlCommand2.Parameters("@F5").Value = dr("Mark")
            SqlCommand2.Parameters("@F6").Value = dr("Quantity")
            SqlCommand2.Parameters("@F7").Value = dr("Section")
            SqlCommand2.Parameters("@F8").Value = dr("Grade")
            SqlCommand2.Parameters("@F9").Value = dr("Length")
            SqlCommand2.Parameters("@F10").Value = dr("Weight")
            SqlCommand2.Parameters("@F11").Value = dr("Area")
            SqlCommand2.Parameters("@F12").Value = dr("NHG")
            SqlCommand2.Parameters("@F13").Value = dr("USER11")
            SqlCommand2.Parameters("@F15").Value = dr("USER2")
            SqlCommand2.Parameters("@F16").Value = dr("USER12")
            SqlCommand2.Parameters("@F17").Value = dr("NAME")
            SqlCommand2.ExecuteNonQuery()
        Next
        SqlConnection1.Close()

There... Easy!!!

Today is the tomorrow you worried about yesterday - and all is well.....

 

[onpnt]

WelshyWizard, I'm glad to see you came up with a solution but I wanted to add that the process you did come up with however working is probably the slowest and least stable (not to mention the one a DBA would dread overall)

I highly recommend looking into the BULK INSERT techniques. SQLDMO, bcp etc..

If upgrading is an option Data.SqlClient.SqlBulkCopy is the better choice also

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[computerjin]

I strongly agree with onpnt.

 

[WelshyWizard]

Ok,

I looked at BULK INSERT. Can I just write the SQL into a SQLCommand like I would a normal insert?
When I tried to creat the BULK INSERT command in the graphical SQLCommand tool it told me that:
"the designer does not graphically support the BULK SQL INSERT construct"
This is why I did it the way I did.

Any advice?

Cheers for the feedback.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[computerjin]

Use OleDbCommand object to BULK INSERT your data into database like a normal INSERT. You cannot use bulk insert in data designer.

 

[WelshyWizard]

Ok....

Note to myself and everyone else... Try before you ask! I was coming back on here to say I'd done it but you beat me to it! Ok, so that worked a treat.

My next question is this.

When I was putting each row in at a time using the FOR loop I was going to create additional information for each row by splitting out certain fields to create new ones and also create new rows to insert. Now that I've bulk inserted everything it is all already in the table.

My question is this. What is the best way for me to now carry out any changes I require? i.e, do I take the info into a datagrid and edit it there, inserting it back into a table or do I do it some other way?


Today is the tomorrow you worried about yesterday - and all is well.....

 

[onpnt]

Who is going to be doing the updating on the data you have bulk inserted? If it will be a back end process then you should again keep this on the database level. That's the purpose of it and not the purpose of your UI.

Let us know what it is you need to change. Sample data would greatly help us get you going also.



[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]

 

[AlexCuse]

It sounds like you want to bulk insert into a 'staging area' and then mess around with the data a bit, no?

If so, I would bring everything into a staging table via your bulk insert. Then, you can have a stored procedure that performs any necessary manipulations to your data, and also puts it into your final table (and clears out your staging table)

Good Luck,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[WelshyWizard]

Staging table... Yea, that sounds like exactly what I want. That was how I was going to go about this but wasn't too sure if it was the right way.

Ok. One of the 1st things I need to do is to split out a field into two seperate fields. The field will read something like:

900X50FLAT
9123X100PLT
1234X12WEB

Basically I want to split out the numbers only into 2 seperate fields. so one field would contain 900 and another would contain 50. However, not all values need to be split out and this will depend on a value held in another field. i.e. If USER1 = Y then split else don't split.

Cheers for you help so far guys, much appreciated.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[WelshyWizard]

Actually, that last question is probably one for a different forum as It'll be done in SQL.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[onpnt]

>> Actually, that last question is probably one for a different forum as It'll be done in SQL.

Yeah thanks for taking that one over there

[sub]____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood[/sub]