Click here to Skip to main content
12,394,978 members (63,072 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
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 6-Mar-12 2:41am
Comments
Mohd Imran Saifi 6-Mar-12 8:57am
   
just give ur code where u got problem
digimanus 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...........
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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.
  Permalink  
Comments
vikram_shinde 6-Mar-12 10:52am
   
it's 900MB CSV file.... even it's hard to open.....
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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.
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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>
  Permalink  
v2
Comments
Nelek 18-Apr-12 17:47pm
   
Added code tags
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

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!
  Permalink  
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160721.1 | Last Updated 18 Apr 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100