CODEWITHSUNDEEP
sqlsql-servert-sqlbulkinsert
Radu094
Radu094

Reputation: 28424

SQL Bulk import from CSV

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.

Upvotes: 24

Views: 45476

Answers (14)

user10191093
user10191093

Reputation: 91

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.

Upvotes: 1

Kevin M
Kevin M

Reputation: 5496

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);
                }
            }
        }
    }
}

Upvotes: 1

PMJ
PMJ

Reputation:

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 **' %" '**

Upvotes: 1

roundcrisis
roundcrisis

Reputation: 17795

Id say use FileHelpers its an open source library

Upvotes: 2

cbp
cbp

Reputation: 25628

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.

Upvotes: 11

RKitson
RKitson

Reputation: 2013

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.

Upvotes: 0

Alex Andronov
Alex Andronov

Reputation: 1675

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.

Upvotes: 3

jason saldo
jason saldo

Reputation: 9950

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

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89671

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.

Upvotes: 1

&#181;Bio
&#181;Bio

Reputation: 10748

You could also use DTS or SSIS.

Upvotes: 0

K Richard
K Richard

Reputation: 1984

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

Upvotes: 13

Daren Thomas
Daren Thomas

Reputation: 70324

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;

Upvotes: 4

John Goering
John Goering

Reputation: 39030

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

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

Upvotes: 0

Dana
Dana

Reputation: 32957

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.

Upvotes: 1

Related Questions