Click here to Skip to main content
14,775,690 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
I have a CSV file (900 MB) which contains countries information from all over world.

I want to import that csv file into sql server database. All fields are tab delimited text in utf8 encoding.

The problem is, city name field has city name in plain text but some names are in double quotes which are giving error while importing.

I have tried text qualifier as " but no use of it.

Any alternate approach would be helpful.

some of sample data ....

City_Name 
Shirehampton
Shirland
"Shillingford"
Shifnal
Posted
Comments
Mohd Imran Saifi 6-Mar-12 8:57am
   
just give ur code where u got problem
Herman<T>.Instance 6-Mar-12 10:19am
   
please show your query
vikram_shinde 6-Mar-12 10:53am
   
i am using SQL Server Import and Export wizard...........

Hi Vikram,

Why not use Find and Replace on your CSV to remove the double quotes on the CSV.

Since you're storing them in text column, I believe the values would be intact with or without the double quotes.
   
Comments
vikram_shinde 6-Mar-12 10:52am
   
it's 900MB CSV file.... even it's hard to open.....
Here is the solution I came up with......

1) I splited 1GB flat CSV file into around 10 parts using CSV splitter.
2) Then I opened first part in MS Excel , which starts with wizard for converting CSV file into excel workbook, then I saved this file as workbook.
3) Then I used SQL Server Import Export wizard for transferring data from excel workbook to database table.
4) I have repeated same process for remaining all parts.

I know it's bit non-technical but at end it solved the problem.
   
v2
System.Data.Odbc.OdbcConnection conn;
            DataTable dt = new DataTable();
            System.Data.Odbc.OdbcDataAdapter da;
            string connectionString;
            string importFolder;
            string fileName;

            string strsqlconn = "Data Source=(local);Initial Catalog=<database_name>;Integrated Security=True;";
            SqlConnection oSqlConn = new SqlConnection(strsqlconn);
            oSqlConn.Open();

            importFolder = @"C:\SQL Server Load\CSVLoadTest"; // path of the csv file

            fileName = "TestDataCSV.csv"; // CSV filename

            connectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + importFolder + ";";      // ODBC driver for txt, csv files
            conn = new System.Data.Odbc.OdbcConnection(connectionString);
            //conn.Open();

            da = new System.Data.Odbc.OdbcDataAdapter("select * from [" + fileName + "]", conn);
            da.Fill(dt); // fill the data to datatable

            SqlBulkCopy bulkCopy = new SqlBulkCopy(strsqlconn); // establish connection to bulk copy to SQL Server

            bulkCopy.DestinationTableName = "CSVData"; // set the destination table
            bulkCopy.BulkCopyTimeout = 600; // default 30 seconds
            bulkCopy.WriteToServer(dt);     // Write from the datatable to the destination.

            conn.Close();
      oSqlConn.Close();</database_name>
   
v2
Comments
Nelek 18-Apr-12 17:47pm
   
Added code tags
Hi,

Ok. In order to do the conversion first you need to eliminate all ".

1-Download EditPad lite free to use or EditPad Pro good for 30 days from www.cnet.com is an excellent editor and easy to use.
2-First, make a copy of your original file to another for example test.
3-Run EditPad and open the copy file.
4-Hit (Shift+Cntl+F) to search & replace
5-Enter " on the search
6-Hit the Search tab and select "Replace ALL" or (Ctrl+Alt+F3
7-Save the file, hit File tab using "Save as" to using another filename as test2.
Now, you have the file without any " in it.

Tips:
Search for " and see if there any left?
You can insert any type of text qualifier at the beggining & at the end of each line.

Good luck with your project!
   
Comments
vikram_shinde 6-Mar-12 11:01am
   
many thanks ... I am using same ....I tried find and replace but there are lots of records and still there are more ..... i think find and replace won't work in this case..... CSV file is 900 mb so you can imagine ......
Edejesusp 7-Mar-12 13:03pm
   
Hi Vikram,

In reference of your 900 mb no problem you can open and edit files of absolutely any size, including files larger than 4 GB, even on a 32-bit system with a modest amount of RAM. EditPad Pro intelligently swaps out parts of huge files to disk, allowing it to nearly instantly open very large files while using only a small amount of memory.

This help file is included with both EditPad Lite and EditPad Pro. All features that are available in EditPad Lite are also available in EditPad Pro. Some features are available in EditPad Pro but not in EditPad Lite. Those features are indicated as (available in EditPad Pro only) in this help file.

Tips:
1- Piece of cake, in less than 2 minutes you will eliminate all " from the file. Just follow the instructions I sent you.
2- You can also insert any type of character in each line you wish and save it with different file types.
3- Recommendation download the manaual from http://www.editpadpro.com/manual/EditPadPro.pdf
4- If you need help just call them or send an email, but first read the doc and play with the program.

Just give a try and have fun.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900