Software

Ask something
Question Locked

How to transfer CSV content to MS SQL Table

0 helpful
8 replies

by jeffreyalejo

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

How can I transfer the contents of a CSV file to MS SQL Table?

I have made a connection string but I don't know how to proceed..

Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;Extended Properties="txt;HDR=Yes;FMT=Delimited";"

8 replies

by jeffreyalejo

I was able to tranfer the CSV content to MS SQL Table but I'm having problems with the columns.

Example:

The CSV file contains:

1, Brown, Arnold, VA
2, Bosh, Zack, California
3, Ford, Ericson, Washington

It is actually a 3-Column table with columns ID, NAME, LOCATION.

The problem occurs with the column NAME. It is treated as two columns because of the comma between the Last Name and First Name. How will I solve this? Thanks.

by tony hopkinson

Change the csv format to

1, "Brown, Arnold","Washington"

etc

if you can't get to it.

Import to a temporary table then write a query to square it up.

Of course if you have to that, all you need is one location with a comma in it, or an extra one in name, and you are stuffed.

by jeffreyalejo

Thanks for helping me out your suggestion somehow gave me an idea. I've converted the CSV file to an Excel file first and then transfer it to SQL table.

by tony hopkinson

are often configurable in csv import tools, and usually have locale/platform, even application defaults

" , [CRLF] and # being the usual suspects

You can all sorts of fun with them.
Presumably this isn't an automated solution?

by jeffreyalejo

I'm actually using it for a Web Application. The users will upload a CSV file, then the application will transfer the content of the uploaded CSV file to the corresponding SQL table.

I decided to transfer the CSV content to an Excel file first, then BulkCopy the Excel content to SQL table. However, I encountered a problem. Some of the cells in the Excel file are not NULL but after transferring it to the SQL table, those cells becomes NULL.

by jeffreyalejo

I made a better solution to this problem. I decided to remove the Excel process since it won't work on a server-side application. I just made a connection string directly to the CSV File and used the INSERT Method to transfer each row from the CSV File to the SQL Table.

This discussion is closed to new replies.