how to import huge .csv into sql database?

Question

I want to import huge .csv file about 1 gig into database.

My application is coded in c# in visual studio 2010. It is running locally and does not need to use on network.

My attempt to import only 25mb using sql compact toolbox scripts leads to crash in Visual Studio.

My attempt to use stringbuilder leads to an out of memory exception (usage about 4 gig of memory !) and then fails.

My attempt to import these file into Excel or Access and then convert them to database fails as well.

Which of these databases can handle better to solve my problem?

• SQL Express
• SQL Compact
• local SQL Server database

Also, which method should I use to import it as fast as I can and to load it faster into a datagridview?

Thanks for any help.

Answer 1

If the CSV file does not have any strings containing commas, you can do a direct BULK INSERT from SQL (if it does, you will have to change the delimiter to something like a bar (|) character, first. This is the most direct means of getting data from a flat file into the database, and doesn't require any intermediate programs like SSIS or Excel

I use it often, and it is the fastest and most efficient way of getting data into SQL from outside. Your command will look something like like

BULK INSERT MyDatabase.dbo.MyTable 
      FROM MyFileName
           DATAFILETYPE='char',  
           FIELDTERMINATOR=',',
           BATCHSIZE=10000

The most common strategy is to load the data into a working table, do any clean-up / conversion necessary, and then insert it to the actual target table.

Answer 2

If you really want to achieve this using C#, what you'll need to do is read the CSV line-by-line and insert it before you move to the next one.

I have a similar situation where I have to read a 2GB "CSV" (tab seperated) and load into MSSQL. Here's how I have it setup.

using (FileStream fs = new FileStream(@"C:\file.csv", FileMode.Open, FileAccess.Read, FileShare.None))
using (StreamReader sr = new StreamReader(fs, Encoding.GetEncoding(1252)))
{
    if (sr.ReadLine() == null) //Take this out if you don't have a header
    {
        throw new Exception("Empty file?!");
    }

    while (sr.Peek() >= 0)
    {
          String s = sr.ReadLine();

          //SPLIT

          //INSERT SQL
    }
}