Importing a CSV file using BULK INSERT command into SQL Server table

Question

I have CSV file which have a couple of data columns.

The CSV file looks like

field1: Test1
field2: Test2
field3: Test3, Test4, Test5

In this case which library can I use as field terminator I mean if I use this query to insert CSV file into shopifyitem table as you assume that the data field inserted not correctly

BULK INSERT shopifyitem
FROM 'c:\test.csv'
WITH
(
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

So which field terminator can I use?

Thank you so much in advance....

That doesn't look like a CSV file to me at all. It should be `Test1,Test2,"Test3,Test4,Test5"` — Aaron Bertrand, Jan 29 '13 at 23:44
@AaronBertrand: though Bulk Insert won't parse that correctly either. — RBarryYoung, Jan 30 '13 at 06:39
@RBarryYoung right, I wasn't suggesting that format would magically work, just that what the OP has now is **not** CSV. Anyway when you have wacky format, embedded commas, etc. you either need to change the format or use something else - SSIS, a pre-parser, BCP with a format file, etc. Tough to tell for sure because I still don't think what we see in the question is exactly what the file looks like. — Aaron Bertrand, Jan 30 '13 at 12:09
@AaronBertrand sadly there is *nothing* built-in to Sql Server that can correctly parse a CSV with quoted fields, not BCP with a format file, not even SSIS. The best you can do is to roll-your-own with SQLBulkCopy, or import the rows as raw text into NVarchar(MAX) and then parse them with custom SQL. Sorry, it's a sore point for me... — RBarryYoung, Jan 30 '13 at 13:21
Please post a sample of the data you trying to insert. As Aaron Bertrand has commented, your data is not in CSV format. Also include the DDL for the table you are inserting into. — Tony, Jan 16 '14 at 12:50
Possible duplicate http://stackoverflow.com/questions/782353/sql-server-bulk-insert-of-csv-file-with-inconsistent-quotes — mas_oz2k1, Apr 17 '14 at 05:22

score 1 · Answer 1 · answered Jan 30 '13 at 03:04

I don't think you're going to be able to import that format without some type of pre-processing. As Aaron implied, that's not a standard CSV.

If you can't get the file re-formatted, there are several ways to get the data into an importable format:

http://www.timmitchell.net/post/2013/01/14/ragged-flat-file-processing-in-ssis/

http://www.rad.pasfu.com/index.php?/archives/38-Script-Component-as-Source-SSIS.html

http://msdn.microsoft.com/en-us/library/ms136060.aspx (scroll down to the "Flat File Source Example")

These are all SSIS solutions which leverage .NET for the bulk of the work. I prefer SSIS simply because of the built in productivity tools. It could be done with any text processor like a console app or powershell script (if you really have some time on your hands).

I prefer the script component source with a stream reader, but Tim Mitchell has an interesting alternative.

I cant understand how sql server cant import a file perfectly fine for excel while 'FREE' mysql can! — dvdmn, Jan 22 '14 at 18:53

score 1 · Answer 2 · edited Jan 16 '14 at 12:59

1

Try using:
ROWTERMINATOR = '0x0a'

edited Jan 16 '14 at 12:59

John Willemse

6,608
7
31
45

answered Jan 16 '14 at 12:39

hassam

11
1

Thanks for a newline character, I couldn't find it any where else but Stack overflow – hram908 Aug 19 '19 at 12:09

score 0 · Answer 3 · edited Jun 17 '13 at 12:37

0

This is the code for importing text or csv file into the database:

String SQL = "BULK INSERT [staging_db].[dbo].[TEST] FROM 'D:\\test.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')";

edited Jun 17 '13 at 12:37

Tiago Sippert

1,324
7
24
33

answered Jun 17 '13 at 12:09

milan.s

3
1
4

score 0 · Answer 4 · edited Dec 11 '13 at 16:40

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

Importing a CSV file using BULK INSERT command into SQL Server table

4 Answers4

Linked