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

Hi,

I am trying to import a csv file into sql server. My csv/sql table has 7 fields.

Invoice Date
SO #
PO #
Carrier
Package #
Tracking #

Every time the import is done i want it to append to and not overwrite to the existing data.

I have gone through a few solutions here, but they all seem too complicated. Can anyone please post some code on how this could be acheived. I am a newbie to asp.net and this is making my like hard.

Appreciate it!
stevegingell
stevegingellAsked:
Who is Participating?
 
GavinMannionCommented:
Okay first things first, if this file gets imported every morning without the need to be checked by a person then rather create a DTS package in SQL that will do this for you automatically.

The SQL TA guys could answer this one in a heartbeat.

If you want to do it in asp.net you can do the following

Once you have the file location
Dim sr as StreamReader = File.OpenText("C:\test.csv")
while sr.Read()
     string line = sr.ReadLine()
     string() Record = line.Split(",")
     'insert into db
     SqlCommand.CommandText = "INSERT INTO myTable ('InvoiceDate','SO....') VALUES (" & Record(0) & "," & Record(1)...
end while

That is very basic code that will not work but hopefully will get you in the right direction if you need more help just yell...

0
 
GavinMannionCommented:
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.
0
 
SystemExpertCommented:

hi

Here I see this and it may work for you

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

Thanks
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
stevegingellAuthor Commented:
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!
0
 
stevegingellAuthor Commented:
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.
0
 
GavinMannionCommented:
Sorry I don't do VB :)... Try this one

Dim Record() as String
Record = line.Split(",")
0
 
stevegingellAuthor Commented:
When i try this it keeps giving me this error:

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

Any ideas?
0
 
GavinMannionCommented:
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?
0
 
stevegingellAuthor Commented:
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.
0
 
stevegingellAuthor Commented:
Actually its a different error, says :

Arrays cannot be declared with'New'
0
 
GavinMannionCommented:
Ah,

Try
Dim Record() as String
0
 
stevegingellAuthor Commented:
Thats what you had asked me to try earlier and i kept getting error.
0
 
GavinMannionCommented:
:)... 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
0
 
stevegingellAuthor Commented:
That seemed to work but now gives me another error.

Index was outside the bounds of the array.
0
 
GavinMannionCommented:
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)
0
 
stevegingellAuthor Commented:
How do I do that. can you be more specific. Thanks.
0
 
stevegingellAuthor Commented:
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
0
 
stevegingellAuthor Commented:
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.
0
 
GavinMannionCommented:
Glad to see you got it working, sorry it took so long....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.