how to import huge .csv into sql database?
c#, sql
Solution
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.
Problem
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.