csv file bulk insert sql server

Question

I have a problem when trying to insert a .csv file using bulk insert into SQL Server.

Csv file example rows at below:

16777216,16777471,AU,AUSTRALIA,APNIC DEBOGON PROJECT
16777472,16778239,CN,CHINA,CHINANET FUJIAN PROVINCE NETWORK
16778240,16779263,AU,AUSTRALIA,LEVEL 5 530 COLLINS STREET

This is my code:

CsvReader csv = new CsvReader(new StreamReader(fileName), false, ',', '\0', '\0', '\0', LumenWorks.Framework.IO.Csv.ValueTrimmingOptions.All);

csv.DefaultParseErrorAction = ParseErrorAction.AdvanceToNextLine;
csv.MissingFieldAction = MissingFieldAction.ReplaceByNull;

copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction);
copy.DestinationTableName = "Blabla";

var mapping1 = new SqlBulkCopyColumnMapping(0, "ipFrom");
copy.ColumnMappings.Add(mapping1);

var mapping2 = new SqlBulkCopyColumnMapping(1, "ipTo");
copy.ColumnMappings.Add(mapping2);

var mapping3 = new SqlBulkCopyColumnMapping(2, "countryCode");
copy.ColumnMappings.Add(mapping3);

var mapping4 = new SqlBulkCopyColumnMapping(3, "countryName");
copy.ColumnMappings.Add(mapping4);

var mapping5 = new SqlBulkCopyColumnMapping(4, "isp");
copy.ColumnMappings.Add(mapping5);


copy.WriteToServer(csv);
transaction.Commit();

Result table is:

"16777216 16777471 AU AUSTRALIA APNIC DEBOGON PROJECT" "16777472 16778239 CN CHINA CHINANET FUJIAN PROVINCE NETWORK" "16778240 16779263 AU AUSTRALIA LEVEL 5 530 COLLINS STREET"

I can't handle this file line by line because file has approximately 100 million lines

How can I discard at the end and start double quotes?

Answer 1

you can use this code :

  public bool CSVFileRead(string fullPathWithFileName, string fileNameModified, string tableName)
    {
        SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["dbConnectionString"]);
        string filepath = fullPathWithFileName;
        StreamReader sr = new StreamReader(filepath);
        string line = sr.ReadLine();
        string[] value = line.Split(',');
        DataTable dt = new DataTable();
        DataRow row;
        foreach (string dc in value)
        {
            dt.Columns.Add(new DataColumn(dc));
        }
        while (!sr.EndOfStream)
        {
            //string[] stud = sr.ReadLine().Split(',');
            //for (int i = 0; i < stud.Length; i++)
            //{
            //    stud[i] = stud[i].Replace("\"", "");
            //}
            //value = stud;
            value = sr.ReadLine().Split(',');
            if (value.Length == dt.Columns.Count)
            {
                row = dt.NewRow();
                row.ItemArray = value;
                dt.Rows.Add(row);
            }
        }
        SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
        bc.DestinationTableName = tableName;
        bc.BatchSize = dt.Rows.Count;
        con.Open();
        bc.WriteToServer(dt);
        bc.Close();
        con.Close();

        return true;
    }

Answer 2

you can use this code but MS office need for this:

  private void ImportCSV(string filePath = @"E:\nucc_taxonomy_140.csv", string tableName = "TempTaxonomyCodes")
    {
        string tempPath = System.IO.Path.GetDirectoryName(filePath);
        string strConn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + tempPath + @"\;Extensions=asc,csv,tab,txt";
        OdbcConnection conn = new OdbcConnection(strConn);
        OdbcDataAdapter da = new OdbcDataAdapter("Select * from " + System.IO.Path.GetFileName(filePath), conn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConfigurationSettings.AppSettings["dbConnectionString"]))
        {
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = 50;
            bulkCopy.WriteToServer(dt);
        }

    }