How to bulk Insert csv with double quotes around all values?

Question

I am trying to insert a CSV file in SQL Server 2008 R2.

. CSV is 300 + MB from http://ipinfodb.com/ip_database.php Complete (City), 4.0M records.

Here are the first 5 rows, with 1st row = column headers:

"ip_start";"country_code";"country_name";"region_code";"region_name";"city";"zipcode";"latitude";"longitude";"metrocode"
"0";"RD";"Reserved";;;;;"0";"0";
"16777216";"AU";"Australia";;;;;"-27";"133";
"17367040";"MY";"Malaysia";;;;;"2.5";"112.5";
"17435136";"AU";"Australia";;;;;"-27";"133";

I tried to import and export data and BULK INSERT, but still could not import it correctly.

Resort to using bcp? can it handle the deletion ""? as?

Many thanks.

Answer 1

Got this, forgot to install Text Qualifier as ":

Answer 2

, NULL .

, csv SQL SERVER.

: http://lanestechblog.blogspot.com/2008/08/sql-server-bulk-insert-using-format.html

Answer 3

? , . ("), , , (;).

, - , , . - , .

EDIT: , . "tempheader" () - SQL , .

Answer 4

# ,

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