How to perform bulk copy from csv file to SQL table using C# when CSV having more than hundred columns?

Question

1.00/5 (1 vote)

See more:

Hi,
I am trying to insert thousands of records from csv file to sql table.My csv file having more than hundred columns. I found so many articles about sql bulk copy. but in the given examples there are 3 or 4 columns max. so mapping csv columns with database table columns is a big headache in my case.Plz anyone suggest me how to perform bulk copy when csv having multiple columns.

articles i found
SqlBulkCopy- Bulk Insert records and Update existing rows if record exists using C# and VB.Net[^]

What I have tried:

I am trying to insert thousands of records from csv file to sql table.My csv file having more than hundred columns. I found so many articles about sql bulk copy. but in the given examples there are 3 or 4 columns max. so mapping csv columns with database table columns is a big headache in my case.Plz anyone suggest me how to perform bulk copy when csv having multiple columns.

Posted 26-Apr-17 3:30am

Member 10714689

Updated 26-Apr-17 9:09am

PIEBALDconsult

v2

Add a Solution

Comments

CHill60 26-Apr-17 9:33am

The "What I have tried:" section is for the code you are using.
Mapping the columns to the CSV may be a headache but at least you only have to do it once.
It is possible to transpose the headings from the CSV using Excel and to generate code in that excel spreadsheet. I have successfully written thousands of lines of mapping code using that technique in a matter of moments.

PIEBALDconsult 26-Apr-17 9:54am

Look into SSIS or other ETL tools.
Or use ADO.net -- the System.Data.OleDb classes -- with the ACE engine to read CSV like a database.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PIEBALDconsult · Answer 1 · 2017-04-26T04:15:00

I have used the BCP utility, SqlBulkCopy, and ADO.net to do ETL work in the past, but I've spent the last five years doing ETL work with SSIS -- with more and more of it being from CSV files.

One main lesson I've learned in the last twenty years (and I recall other members of this site saying the same thing):
Don't try to convert data from text to other dataypes during the initial ETL process. Get the data into the database first (as text) and then do further processing on it.

A further benefit of doing that is that you can then write a utility that reads a CSV and produces a mapping file that BCP can use to load the data.

Maciej Los · Answer 2 · 2017-04-26T09:09:00

The way you use BulkCopy on few columns doesn't differ to BulkCopy on multiple columns, but...

You have to use ADO.NET[^] - OleDb[^].

How?

Create schema.ini file in which you have to define the number of columns
See:
Schema.ini File (Text File Driver) | Microsoft Docs[^]
How to: Add a Schema Definition to a Text File Data Source[^]
Much ADO About Text Files[^]
My past answer with sample code: Read Text File Specific Columns[^]
Create OleDbConnection[^] and OleDbCommand[^]
Use OleDbReader[^] to Load[^] data into DataTable
Now, connect your programme to Sql server database and copy the data using SqlBulkCopy class[^]

That's all!

For further details, please see:
ADO.NET Code Examples[^]

How to perform bulk copy from csv file to SQL table using C# when CSV having more than hundred columns?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0