Import Data From CSV

Question

Ok, this started out as a pretty simple task to import a customers data into our database. The script below loads all the data into memory and then allow manipulation after.

string ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" + FileLoc + "\"; Extended Properties=\"text;HDR=Yes;FMT=Delimited(,)\"";

using (OleDbConnection conn = new OleDbConnection(ConString))
{
    using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM DataInput.csv", conn))
    {
        conn.Open();

        using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
        {
            int RowNumber = 0;
            while (dr.Read())
            {


                if(RowNumber==0)
                {
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        TitleData.Add(new CSVTitleData() {ColumnID = i,ColumnName = dr.GetValue(i).ToString()});
                    }
                }
                else
                {
                    var DataToInsert = new List<CSVData>();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        if (i == 8)
                        {
                            var A = dr.GetValue(i);
                            Console.WriteLine(A.GetType().ToString() + " - " + i);
                        }
                        DataToInsert.Add(new CSVData(){ColumnID = i,Value = dr.GetValue(i).ToString()});
                    }

                    Content.Add(DataToInsert);
                }
                RowNumber++;
            }
        }
    }
}

This works fine until there is data like

4950
4951
4952
4998
2024
4176
7025-1
RVAC010
RVAC011
4945
4946
4947
4948

Excel tries to be clever and reads each of the fields as an Int. Resulting int the ones with a letters in being read as DBNull.

Now I've done some reading and there are lots of people with this question but no solid solution. One of the possible solutions is to create a Scheme.ini I wonder if someone can help me out with the exact format of that?

Or perhaps there is an easier was of reading in the CSV?

Answer 1

I would recommend ditching Excel, and use something along the lines of LINQ to CSV to handle your processing. You would eliminate the dependency on Excel, have direct control over typing, and its lighter weight to boot.

http://www.codeproject.com/KB/linq/LINQtoCSV.aspx

Answer 2

Hi

I've had the same experience with this.... I found fantastic library at codeproject for reading CSV files:

http://www.codeproject.com/KB/database/CsvReader.aspx

The library handles everything such as parsing CSVs correctly so you don't have to worry about problems like these. It is very easy to use and the data can be returned via a DataTable.

It has become an essential part of my code library I highly recommend you check it out.

Answer 3

My favorite method to read files is to use an external library http://www.filehelpers.com/

Description:
The FileHelpers are a free and easy to use .NET library to import/export data from fixed length or delimited records in files, strings or streams.

It has everything and the kitchen sink and still small..

Running excel is never a good idea in an asp.net app and is severally forbidden by microsoft.

You could always try to ODBC which has a way of specifying layout with a ini file for a microsoft only solution, though the days of ODBC are numbered