how to import a csv file to sql in vb.net

How many times do you need to do this?

Easiest way is to do this straight from inside sql server enterprise manager. Right click the DB and say import data. Follow the instructions from there.

If the user is actually uploading the file then you need to use the System.IO functionality to read the file, split the rows and then insert from there.

If you could let me know what the case is I could help you further. Reading files in .Net is very simple if that is what is required.

hi

Here I see this and it may work for you

http://www.codeproject.com/useritems/Import_CSV_File_Into_SQL.asp

Thanks

Hi GavinMannion,

Ok here's what i need. I have a datagrid which displays these fields on the frontend:

Invoice Date
SO #
PO #
Carrier
Package #
Tracking #

These are pulled from the ShippingData table in sql server. I need an import functionalty on the admin (backend) side. Here every morning a tech would first browse, the attach a csv file and then hit export. On successfully importing, it should append the data into the ShippingData table, and not overwrite it.

Basically this process is repeated every morning, the "Invoice Date" would be today's date. And the datagrid would show the records only with today's date. The older ones would just not show - but still be in the database, as i am also building a search function which can search through older data.

Is this helpful? If not I can try to explain it differently.

Thanks much!

I have tried this code, but it shows an error for this line: string() Record = line.Split(",")

It says "String is  a class type, so is not a valid expression." Any ideas as to what i should do differently.

Sorry I don't do VB :)... Try this one

Dim Record() as String
Record = line.Split(",")

When i try this it keeps giving me this error:

"Object reference not set to an instance of an object."

Any ideas?

On which line?

try
Dim Record() as new String

Any VB people who see this could you please tell me where my C# --> VB code is going wrong?

It still gives me the same error. Driving me nuts. I am pretty sure thats how you declare a string  in vb. Not sure why getting the error though.

Actually its a different error, says :

Arrays cannot be declared with'New'

Ah,

Try
Dim Record() as String

Thats what you had asked me to try earlier and i kept getting error.

:)... Talk about not paying attention....

Apologies, as I mentioned I don't do much VB programming, I found out what I was doing wrong and all you actually need to do is

Dim Record = Split(line, ",")

Then Record will automatically become an Array that you can loop through

http://www.theukwebdesigncompany.com/articles/article.php?article=151

That seemed to work but now gives me another error.

Index was outside the bounds of the array.

That would mean you are trying to access an item in the array which is not there.

Remember all arrays start from 0 and to see how many items are in your array just look at

UBound(Record)

How do I do that. can you be more specific. Thanks.

Its dying at this line: objCmd.Parameters.Add("@SONumber", Record(1))

here is my code:

Sub Import(ByVal Sender As Object, ByVal E As EventArgs)
        If Not (txtUpload.PostedFile Is Nothing) Then
            Dim postedFile As HttpPostedFile = txtUpload.PostedFile
            Dim filename As String = Path.GetFileName(postedFile.FileName)
            Dim filepath As String = "C:\Inetpub\wwwroot\shipping\uploads\" & filename
            postedFile.SaveAs(filepath)
            lblMessage.Text = "File has been uploaded"

            Dim objStreamReader As StreamReader
            Dim line As String

            objStreamReader = File.OpenText(filepath)
            line = objStreamReader.ReadLine()

            While (line <> Nothing)
                lblResult.Text &= line & "<br>"
                line = objStreamReader.ReadLine()

                Dim Record() = Split(line, ",")

                Dim objConn As New SqlConnection(ConfigurationSettings.AppSettings("DSN"))
                Dim objCmd = New SqlCommand("INSERT INTO ShippingData (InvoiceDate, SONumber," & _
                "PONumber, CarrierID, PackageNumber, TrackingNumber) " & _
                "VALUES (@InvoiceDate, @SONumber, @PONumber, @CarrierID, " & _
                "@PackageNumber, @TrackingNumber)", objConn)

                objCmd.Parameters.Add("@InvoiceDate", Record(0))
                objCmd.Parameters.Add("@SONumber", Record(1))
                objCmd.Parameters.Add("@PONumber", Record(2))
                objCmd.Parameters.Add("@CarrierID", Record(3))
                objCmd.Parameters.Add("@PackageNumber", Record(4))
                objCmd.Parameters.Add("@TrackingNumber", Record(5))
                objConn.Open()
                objCmd.ExecuteNonQuery()
                objConn.Close()
                lblMessage.Text = "Shipment has been Imported!"
        End While
            objStreamReader.Close()

        End If
    End Sub

I am going to call this one good. I got the final answer by reposting this. However thanks for bearing with me. I'll accept you earlier answer as that was pretty close to my final solution.

Glad to see you got it working, sorry it took so long....