CODEWITHSUNDEEP
sql-serversql-server-2008csvbulkinsert
Henry
Henry

Reputation: 32905

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

I am trying to insert a .csv file into SQL Server 2008 R2.

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

Here're the top 5 lines, with 1st line = 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 Import and Export Data, and BULK INSERT, but haven't been able to import them correctly yet.

Shall I resort to use bcp? can it handle stripping the ""? how?

Thank you very much.

Upvotes: 3

Views: 13760

Answers (4)

Vishal Sen
Vishal Sen

Reputation: 1175

In C# you can use this code, working for me 

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

Upvotes: 0

Henry
Henry

Reputation: 32905

Got it, forgot to set Text Qualifier as ":

enter image description here

Upvotes: 5

el_stack
el_stack

Reputation: 538

Your data looks pretty inconsistent since NULL values don't also carry a quotation enclosure.

I believe you can create a format file to customize to your particular csv file and its particular terminators in SQL SERVER.

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

Upvotes: 1

Mayo
Mayo

Reputation: 10782

Is this a single import or are you wanting to schedule a recurring import? If this is a one-time task, you should be able to use the Import and Export Wizard. The text qualifier will be the quotation mark ("), be sure to select column names in the first data row, and you'll want to convey that the field delimiter is the semicolon (;).

I'm not certain the file is properly formatted - the last semicolon following each of the data rows might be a problem. If you hit any errors, simply add a new column header to the file.

EDIT: I just did a quick test, the semicolons at the end will be treated as part of the final value in that row. I would suggest adding a ;"tempheader" at the end of your header (first) row - that will cause SQL to treat the final semicolon as a delimiter and you can delete that extra column once the import is complete.

Upvotes: 0

Related Questions