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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

hi

Here I see this and it may work for you

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

Thanks
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!
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
GavinMannionCommented:
Sorry I don't do VB :)... Try this one

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

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

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

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

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

Index was outside the bounds of the array.
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)
stevegingellAuthor Commented:
How do I do that. can you be more specific. Thanks.
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
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.
GavinMannionCommented:
Glad to see you got it working, sorry it took so long....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.