How to bulk insert CSV file in ASP.NET

Question

0.00/5 (No votes)

See more:

I want to bulk insert a csv file into sql database (asp.net with vb.net) with the code below but it gives me this error (Incorrect syntax near 'C:'.)

VB

Using con1 As New  SqlConnection(ConfigurationManager.ConnectionStrings("connection").ConnectionString)
           Dim csvPath As String = Server.MapPath("~/excel/") + Path.GetFileName(FileUpload1.PostedFile.FileName)
           FileUpload1.SaveAs(csvPath)
           con1.Open()
           Dim queryimg As String = "BULK INSERT trycsv FROM " + csvPath + ""
           Dim cmdimg As New SqlCommand(queryimg, con1)
           cmdimg.ExecuteNonQuery()
           con1.Close()
       End Using

Please save my day.

Posted 7-Dec-14 2:35am

Member 10316149

Updated 7-Dec-14 2:59am

Kornfeld Eliyahu Peter

v6

Add a Solution

Comments

DamithSL 7-Dec-14 9:11am

please update the full exception with stack trace details.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Accepted Answer · 2014-12-07T02:57:00

Solution 1

try below

VB

Using con1 As New  SqlConnection(ConfigurationManager.ConnectionStrings("connection").ConnectionString)
           Dim csvPath As String = Path.Combine(Server.MapPath("~/excel/") , Path.GetFileName(FileUpload1.PostedFile.FileName))
           FileUpload1.SaveAs(csvPath)
           con1.Open()
           Dim queryimg As String = "BULK INSERT trycsv FROM '"+ csvPath + "'"
           Dim cmdimg As New SqlCommand(queryimg, con1)
           cmdimg.ExecuteNonQuery()
           con1.Close()
       End Using

I have include single quotes for file path
UPDATE:
As below Kornfeld Eliyahu Peter pointed out about path,
above will work if your web server and the sql server both run on same sever/PC
check Handling BULK Data insert from CSV to SQL Server[^]
you can find sample code which you can bulk insert using csv reader and that will solve your issue.

Posted 7-Dec-14 2:57am

DamithSL

Updated 7-Dec-14 3:25am

v3

Comments

Kornfeld Eliyahu Peter 7-Dec-14 9:02am

You probably right about the quotes as the source of the syntax error, however the main problem is that OP saves the files on the WEB SERVER's disk where BULK INSERT will look for it on the DB SERVER's disk...

DamithSL 7-Dec-14 9:10am

is it? then how does windows form applications do the bulk insert?

Kornfeld Eliyahu Peter 7-Dec-14 9:13am

"data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name. A UNC name has the form \\Systemname\ShareName\Path\FileName. For example, \\SystemX\DiskZ\Sales\update.txt."
Quoted from here: http://msdn.microsoft.com/en-us/library/ms188365.aspx
It means you have to save the file in a shared place to BULK INSERT to work...

DamithSL 7-Dec-14 9:25am

Thanks @Kornfeld Eliyahu Peter, I have updated my answer.

Kornfeld Eliyahu Peter 7-Dec-14 9:27am

:thumbsup:

DamithSL 7-Dec-14 10:02am

Thank you, Peter

Abhinav S · Accepted Answer · 2014-12-07T02:58:00

Solution 2

Try
Handling BULK Data insert from CSV to SQL Server[^]
Bulk CSV to SQL server tutorial - VB.NET and C# sample[^]
Bulk Insert into SQL Server using SqlBulkCopy[^]

Posted 7-Dec-14 2:58am

Abhinav S

Comments

Member 10316149 7-Dec-14 12:15pm

Thank you very much guys
all the above works eccept using the csvreader which I think will solve the problem better. error (csvreader not defined), any link to download the csvreader and how to add it to the project?