CODEWITHSUNDEEP
c#sqlsql-servercsv
cpound
cpound

Reputation: 85

Bulk Data insert from CSV to SQL Server using Console Application

Need to insert a local .csv file, around 6MB, 130k rows. I need to open the file in a c# program, insert each row, each row having special considerations (quotes around each field).

Wondering the best way to go about this, as I've never written a c# app the inserts to a db. I have done the below directly on the DB, but now need to do this within a c# app.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

The sample data within the .csv file is below, and I'm a little lost on how to strip the quotes out when inserting to the DB from c#. Do I need to specify the column names in c#, or can I insert something similar to the T-SQL query?

Copyright (c) 2011 MaxMind Inc.  All Rights Reserved.
"beginIp","endIp","beginIpNum","endIpNum","countryCode","countryName"
"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia"
"1.0.1.0","1.0.3.255","16777472","16778239","CN","China"
"1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia"

And for those 1st 2 lines I know I need to do 2 reader.ReadLine();'s to ignore them. Any help for a c# newb is welcome! The fields are exactly the same throughout the .csv file, so that would not be a problem. Just don't know if theres an easy way to insert data from a large csv to SQL via c#. Thanks in advance!

EDIT:

What I'm now using, that beautifully gets .csv fields, even within quotes, given TGH's example link. Now just need to insert via SQL connection.

using (TextFieldParser parser = new TextFieldParser(@"C:\StreamReadTest.csv"))
        {
            parser.SetDelimiters(new string[] { "," });
            parser.HasFieldsEnclosedInQuotes = true;

            // Skip over header line.
            parser.ReadLine();
            // Skip field descriptions line.
            parser.ReadLine();

            while (!parser.EndOfData)
            {
                string[] fields = parser.ReadFields();

                foreach (var value in fields)
                {
                    Console.WriteLine(value);
                }

                Console.WriteLine("\n");
                Console.ReadKey();
            }
        }

Upvotes: 0

Views: 2237

Answers (1)

TGH
TGH

Reputation: 39248

You have several options:

You can create a SqlCommand and run the raw sql string you defined above. Alternatively you could wrap the sql in a stored proc and call it from .Net using Ado.net.

Another approach is to parse the file and save the individual rows through .Net (ado.Net or an ORM like Entity Framework) I would consider looking into the following library (.net built in csv parser) for parsing the file: http://coding.abel.nu/2012/06/built-in-net-csv-parser/

Upvotes: 2

Related Questions