How to handle the CRLF in bcp utility. using bcp to restore the csv file into Azure SQL database. File is comma delimited and content with ""

Question

I am facing issue while importing the data in azure sql database using bcp utility.

bcp dbo.Account in "D:ABC\Account.csv" -S xxxxx.database.windows.net -U jfsa -P xxxxxx -d dbname -a 4096 -b 50000 -k -E -h "tablock" -q -c -l 120 -t "," -r "0x0d0x0a" -o "D:\Logs\FileLog\account_output.txt" -e "D:\Logs\Error\account_err.txt" -m 100 -F 2

Contents in account csv

Not getting any error but below message after running the command

Starting copy...

0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1

Answer 1

Restore the csv file into Azure SQL database.

• You can use SQL server management studio to load the csv file to sql database. Follow the steps in this document How to import a CSV file into a database using SQL Server Management Studio-discountasp.net.

• You can also use Copy activity in azure data factory to copy csv data into sql database. Give csv file as source dataset and SQL database table as a sink dataset in copy activity.

As @Dai suggested, you can also use c# with the use of CsvHelper NuGet library to fill a System.Data.DataTable which is passed into SqlBulkCopy. I used that approach as you can see below.

Below are the steps I followed:

• Connection String is used to connect with Azure SQL Database. tableName and csvFilePath is used to store the Table name and .csv file path.

• StreamReader is created to read the CSV file and CsvHelper is used to parse the csv data.

• Each record in the CSV is an instance of DataClass. The ToList() method is used to read all records from the CSV.

• DataTable named dt is created to match the structure of the SQL table. It is configured with columns according to the structure of the data.

• SqlBulkCopy is used to insert the data from the DataTable into the Azure SQL Database.

Below Is the code I tried with:

static void Main(string[] args)
{
    string connectionString = "*****";
    string tableName = "SampleTable"; 
    string csvFilePath = @"C:\Users\****\Desktop\****\SampleDataFile.csv";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using (var reader = new StreamReader(csvFilePath))
        using (var csv = new CsvReader(reader, new CsvConfiguration(CultureInfo.InvariantCulture)))
        {
            var records = csv.GetRecords<DataClass>().ToList(); 
            DataTable dt = new DataTable();

            dt.Columns.Add("Id", typeof(int));
            dt.Columns.Add("FirstName", typeof(string));
            dt.Columns.Add("Age", typeof(int));

            foreach (var record in records)
            {
                dt.Rows.Add(record.Id, record.FirstName, record.Age);
            }

            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
            {
                bulkCopy.DestinationTableName = tableName;
                bulkCopy.WriteToServer(dt);
            }
            Console.WriteLine("Data imported successfully.");
        }
    }
}

Data Stored in CSV file:

Output: