Bulk Data insert from CSV to SQL Server using Console Application

Question

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();
            }
        }

@Darek I'd never heard of OSQL, but this page says "This feature will be removed in a future version of SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use the feature. Use sqlcmd instead. For more information, see sqlcmd Utility." — GlennFromIowa
– GlennFromIowa, Commented Nov 4, 2015 at 17:34

TGH · Accepted Answer · 2014-07-17 03:43:19Z

2

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/

answered Jul 17, 2014 at 3:43

TGH

39.4k13 gold badges106 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

cpound Over a year ago

Thanks for your help! That link knocked out two things I needed, how to get fields easily within quotes, and skip the two first lines of my .csv file. Any guidance on inserting this to a MSSQL DB? The table is already made, just containing the same fields: "beginIp","endIp","beginIpNum","endIpNum","countryCode","countryName" I have not connected and inserted to a c# db using parsed .csv data so any help is appreciated! Thanks.

TGH Over a year ago

There are a few different ways. One is Entity framework (asp.net/mvc/tutorials/getting-started-with-ef-using-mvc/…), but you can also consider Ado.Net.

Collectives™ on Stack Overflow

Bulk Data insert from CSV to SQL Server using Console Application

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related