Csv-files to Sql-database c#

Question

What is the best approach to store information gathered locally in .csv-files with a C#.net sql-database? My reasons for asking is 1: The data i am to handle is massive (millions of rows in each csv). 2: The data is extremely precise since it describes measurements on a nanoscopic scale, and is therefor delicate.

My first though was to store each row of the csv in a correspondant row in the database. I did this using The DataTable.cs-class. When done, i feelt that if something goes wrong when parsing the .csv-file, i would never notice.

My second though is to upload the .csvfiles to a database in it's .csv-format and later parse the file from the database to the local enviroment when the user asks for it. If even possible in c#.net with visual stuido 2013, how could this be done in a efficient and secure manner?

Why would something go wrong while parsing the file? You should develop the parse function to ensure it does correct validation and implement error logging to ensure you do not miss any problems — musefan
– musefan, Commented Jan 20, 2015 at 14:17
Yes, and i have. I check for all sort of errors that comes to mind, but what about the errors that doesn't? — user2858578
– user2858578, Commented Jan 20, 2015 at 14:21
I have had the same scenario not so long ago. I used a local database that I created in VS 2013 and then parsed the csv file into that database with a TextFieldParser. Check out msdn.microsoft.com/en-us/library/ms233763.aspx. — St0ffer
– St0ffer, Commented Jan 20, 2015 at 14:22
Thanks, but this is not really what i am wondering. I am familiar with both TextFieldParser and DataTable for parsing files. I then use SqlBulkCopy to upload the data to the database. What i am wondering is what i stated in my second though. — user2858578
– user2858578, Commented Jan 20, 2015 at 14:27
Should add though, that this approach will not be ideal, if you are importing massive amounts of information from the csv file, as it will be somewhat slow. If that is the case, you should probably take a look at bulk insert or bulk copy. I found a blog post with a method once that is pretty fast for importing large amounts of data: johnnycode.com/2013/08/19/… — St0ffer
– St0ffer, Commented Jan 20, 2015 at 14:31

Pidon · Accepted Answer · 2015-01-20 14:37:53Z

1

I used .Net DataStreams library from csv reader in my project. It uses the SqlBulkCopy class, though it is not free.

Example:

    using (CsvDataReader csvData = new CsvDataReader(path, ',', Encoding.UTF8))
    {
        // will read in first record as a header row and
        // name columns based on the values in the header row
        csvData.Settings.HasHeaders = true;

        csvData.Columns.Add("nvarchar");
        csvData.Columns.Add("float"); // etc.

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "DestinationTable";
            bulkCopy.BulkCopyTimeout = 3600;

            // Optionally, you can declare columnmappings using the bulkCopy.ColumnMappings property

            bulkCopy.WriteToServer(csvData);
        }
    }

answered Jan 20, 2015 at 14:37

Pidon

2751 silver badge6 bronze badges

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

Comments

pseudocoder · Accepted Answer · 2015-01-20 14:38:17Z

1

It sounds like you are simply asking whether you should store a copy of the source CSV in the database, so if there was an import error you can check to see what happened after the fact.

In my opinion, this is probably not a great idea. It immediately makes me ask, how would you know that an error had occurred? You certainly shouldn't rely on humans noticing the mistake so you must develop a way to programmatically check for errors. If you have an automated error checking method you should apply that method when the import occurs and avoid the error in the first place. Do you see the circular logic here?

Maybe I'm missing something but I don't see the benefit of storing the CSV.

answered Jan 20, 2015 at 14:38

pseudocoder

4,4022 gold badges28 silver badges41 bronze badges

1 Comment

St0ffer Over a year ago

^This, simple error logging or even just a try-catch around the method with a MessageBox to show the exception should catch if any errors happen (for example primary key violation).

dmigo · Accepted Answer · 2015-01-20 14:34:16Z

0

You should probably use Bulk Insert. With your csv-file as a source. But this will only work if the file is accessible from the PC that is running your SQL Server.

Here you can find a nice solution as well. To be short it looks like this:

StreamReader file = new StreamReader(bulk_data_filename);  
CsvReader csv = new CsvReader(file, true,',');  
SqlBulkCopy copy = new SqlBulkCopy(conn);  
copy.DestinationTableName = tablename;  
copy.WriteToServer(csv);

edited Jan 20, 2015 at 14:34

answered Jan 20, 2015 at 14:27

dmigo

3,0555 gold badges47 silver badges66 bronze badges

Collectives™ on Stack Overflow

Csv-files to Sql-database c#

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related