[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-04-12
19
Medium Priority
?
282 Views
Last Modified: 2010-07-27
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
0
Comment
Question by:stevegingell
  • 10
  • 8
19 Comments
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16443599
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
 
LVL 10

Expert Comment

by:SystemExpert
ID: 16443965

hi

Here I see this and it may work for you

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

Thanks
0
 

Author Comment

by:stevegingell
ID: 16447510
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Accepted Solution

by:
GavinMannion earned 2000 total points
ID: 16451444
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
 

Author Comment

by:stevegingell
ID: 16462801
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
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16467260
Sorry I don't do VB :)... Try this one

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

Author Comment

by:stevegingell
ID: 16494545
When i try this it keeps giving me this error:

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

Any ideas?
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16495470
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
 

Author Comment

by:stevegingell
ID: 16498761
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
 

Author Comment

by:stevegingell
ID: 16500886
Actually its a different error, says :

Arrays cannot be declared with'New'
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16505029
Ah,

Try
Dim Record() as String
0
 

Author Comment

by:stevegingell
ID: 16505036
Thats what you had asked me to try earlier and i kept getting error.
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16505197
:)... 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
 

Author Comment

by:stevegingell
ID: 16510558
That seemed to work but now gives me another error.

Index was outside the bounds of the array.
0
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16517708
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
 

Author Comment

by:stevegingell
ID: 16540937
How do I do that. can you be more specific. Thanks.
0
 

Author Comment

by:stevegingell
ID: 16540943
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
 

Author Comment

by:stevegingell
ID: 16541282
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
 
LVL 15

Expert Comment

by:GavinMannion
ID: 16541485
Glad to see you got it working, sorry it took so long....
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes in DotNetNuke module development you want to swap controls within the same module definition.  In doing this DNN (somewhat annoyingly) swaps the Skin and Container definitions to the default admin selections.  To get around this you need t…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question