SQL Bulk import from CSV

Question

I need to import a large CSV file into an SQL server. I'm using this :

BULK 
INSERT CSVTest
        FROM 'c:\csvfile.txt'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO

problem is all my fields are surrounded by quotes (" ") so a row actually looks like :

"1","","2","","sometimes with comma , inside", "" 

Can I somehow bulk import them and tell SQL to use the quotes as field delimiters?

Edit: The problem with using '","' as delimiter, as in the examples suggested is that : What most examples do, is they import the data including the first " in the first column and the last " in the last, then they go ahead and strip that out. Alas my first (and last) column are datetime and will not allow a "20080902 to be imported as datetime.

From what I've been reading arround I think FORMATFILE is the way to go, but documentation (including MSDN) is terribly unhelpfull.

Answer 1

Try FIELDTERMINATOR='","'

Here is a great link to help with the first and last quote...look how he used the substring the SP

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

Answer 2

Another hack which I sometimes use, is to open the CSV in Excel, then write your sql statement into a cell at the end of each row. For example:

=concatenate("insert into myTable (columnA,columnB) values ('",a1,"','",b1,"'")")

A fill-down can populate this into every row for you. Then just copy and paste the output into a new query window.

It's old-school, but if you only need to do imports once in a while it saves you messing around with reading all the obscure documentation on the 'proper' way to do it.

Answer 3

Try OpenRowSet. This can be used to import Excel stuff. Excel can open CSV files, so you only need to figure out the correct [ConnectionString][2].

[2]: Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

Answer 4

I know this isn't a real solution but I use a dummy table for the import with nvarchar set for everything. Then I do an insert which strips out the " characters and does the conversions. It isn't pretty but it does the job.

Answer 5

Id say use FileHelpers its an open source library

Answer 6

Do you need to do this programmatically, or is it a one-time shot?

Using the Enterprise Manager, right-click Import Data lets you select your delimiter.

Answer 7

You have to watch out with BCP/BULK INSERT because neither BSP or Bulk Insert handle this well if the quoting is not consistent, even with format files (even XML format files don't offer the option) and dummy ["] characters at the beginning and end and using [","] as the separator. Technically CSV files do not need to have ["] characters if there are no embedded [,] characters

It is for this reason that comma-delimited files are sometimes referred to as comedy-limited files.

OpenRowSet will require Excel on the server and could be problematic in 64-bit environments - I know it's problematic using Excel in Jet in 64-bit.

SSIS is really your best bet if the file is likely to vary from your expectations in the future.

Answer 8

u can try this code which is very sweet if you want , this will remove unwanted semicolons from your code. if for example your data is like this :
"Kelly","Reynold","[email protected]"

Bulk insert test1
from 'c:\1.txt' with ( 
    fieldterminator ='","'
    ,rowterminator='\n')

update test1<br>
set name =Substring (name , 2,len(name))
where name like **' "% '**

update test1
set email=substring(email, 1,len(email)-1)
where email like **' %" '**

Answer 9

Firs you need to import CSV file into Data Table

Then you can insert bulk rows using SQLBulkCopy

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlBulkInsertExample
{
    class Program
    {
      static void Main(string[] args)
        {
            DataTable prodSalesData = new DataTable("ProductSalesData");

            // Create Column 1: SaleDate
            DataColumn dateColumn = new DataColumn();
            dateColumn.DataType = Type.GetType("System.DateTime");
            dateColumn.ColumnName = "SaleDate";

            // Create Column 2: ProductName
            DataColumn productNameColumn = new DataColumn();
            productNameColumn.ColumnName = "ProductName";

            // Create Column 3: TotalSales
            DataColumn totalSalesColumn = new DataColumn();
            totalSalesColumn.DataType = Type.GetType("System.Int32");
            totalSalesColumn.ColumnName = "TotalSales";

            // Add the columns to the ProductSalesData DataTable
            prodSalesData.Columns.Add(dateColumn);
            prodSalesData.Columns.Add(productNameColumn);
            prodSalesData.Columns.Add(totalSalesColumn);

            // Let's populate the datatable with our stats.
            // You can add as many rows as you want here!

            // Create a new row
            DataRow dailyProductSalesRow = prodSalesData.NewRow();
            dailyProductSalesRow["SaleDate"] = DateTime.Now.Date;
            dailyProductSalesRow["ProductName"] = "Nike";
            dailyProductSalesRow["TotalSales"] = 10;

            // Add the row to the ProductSalesData DataTable
            prodSalesData.Rows.Add(dailyProductSalesRow);

            // Copy the DataTable to SQL Server using SqlBulkCopy
            using (SqlConnection dbConnection = new SqlConnection("Data Source=ProductHost;Initial Catalog=dbProduct;Integrated Security=SSPI;Connection Timeout=60;Min Pool Size=2;Max Pool Size=20;"))
            {
                dbConnection.Open();
                using (SqlBulkCopy s = new SqlBulkCopy(dbConnection))
                {
                    s.DestinationTableName = prodSalesData.TableName;

                    foreach (var column in prodSalesData.Columns)
                        s.ColumnMappings.Add(column.ToString(), column.ToString());

                    s.WriteToServer(prodSalesData);
                }
            }
        }
    }
}

Answer 10

This is an old question, so I write this to help anyone who stumble upon it.

SQL Server 2017 introduces the FIELDQUOTE parameter which is intended for this exact use case.

Answer 11

Yup, K Richard is right: FIELDTERMINATOR = '","'

See http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file for more info.

Answer 12

You could also use DTS or SSIS.

Answer 13

Do you have control over the input format? | (pipes), and \t usually make for better field terminators.

Answer 14

If you figure out how to get the file parsed into a DataTable, I'd suggest the SqlBulkInsert class for inserting it into SQL Server.