C# import CSV file with schema.ini creates table but doesn't load data

Question

I need to import some CSV files into a temporary table inside an MDB database. The files come from a file upload on an ASP.NET Web application.

This is my CSV file:

"Operating Unit Organization Name";"Year";"Sales Rep Name";"Date";"Week";"Product Number";"Account Name";"Customer Number";"Corporate Brand";"Brand";"Ordered Quantity";"Amount"
"IT Operating Unit";2014;"Name-561004";2014-02-21;"2014 Week08";"123456+";"Buyer name";"456789";"Corp Brand";"Brand";4;147,52
"IT Operating Unit";2014;"Name-561004";2014-02-21;"2014 Week08";"123.012EXP";"Buyer name";"789123";"Corp Brand";"Brand";10;204,9
"IT Operating Unit";2014;"Name-561004";2014-02-17;"2014 Week08";"101S-3";"Buyer name";"234567";"Another Corp Brand";"Another Brand";30;237,5

This is the method that generates the schema.ini file. Since it has to contain the name of the CSV file, I generate a new schema file each time I upload a new CSV, because I need to save them all on a folder with specific name.

private void CreateCsvSchemaFile()
    {
        using (FileStream fs = new FileStream(Path.GetDirectoryName(FilePath) + "\\schema.ini", FileMode.Create, FileAccess.Write))
        {
            using (StreamWriter sw = new StreamWriter(fs))
            {
                sw.WriteLine("[" + Path.GetFileName(FilePath) + "]");
                sw.WriteLine("ColNameHeader=True");
                //sw.WriteLine("MaxScanRows=0");
                sw.WriteLine("Format=Delimited(;)");
                sw.WriteLine("DateTimeFormat=yyyy-MM-dd");
                sw.WriteLine("CharacterSet=ANSI");
                sw.WriteLine("DecimalSymbol=,");
                sw.WriteLine("Col1=\"Operating Unit Organization Name\" Text Width 255");
                sw.WriteLine("Col2=\"Year\" Long");
                sw.WriteLine("Col3=\"Sales Rep Name\" Text Width 255");
                sw.WriteLine("Col4=\"Date\" DateTime");
                sw.WriteLine("Col5=\"Week\" Text Width 255");
                sw.WriteLine("Col6=\"Product Number\" Text Width 255");
                sw.WriteLine("Col7=\"Account Name\" Text Width 255");
                sw.WriteLine("Col8=\"Customer Number\" Text Width 255");
                sw.WriteLine("Col9=\"Corporate Brand\" Text Width 255");
                sw.WriteLine("Col10=\"Brand\" Text Width 255");
                sw.WriteLine("Col11=\"Ordered Quantity\" Long");
                sw.WriteLine("Col12=\"Amount\" Currency");
                sw.Close();
                sw.Dispose();
            }

            fs.Close();
            fs.Dispose();
        }
    }

The schema.ini file is always generated correctly and in fact the table on the db is created with the correct field names and types.

This is the method that execute the CSV import.

private void ImportCsvIntoTemp()
    {
        try
        {
            CreateCsvSchemaFile();

            string query = @"SELECT * INTO TEMP_CSV 
                                FROM [Text;HDR=no;Database={0}].[{1}]";

            query = String.Format(query, Path.GetDirectoryName(FilePath), Path.GetFileName(FilePath));

            AccessDb.Query(AccessDbConnString, query);
        }
        catch (Exception ex)
        {
            string message = String.Format("CSV file import failed. Inner Exception: {0}", ex.Message);
            throw new ImportFailedException(message);
        }
    }

The table TEMP_CSV is created correctly but no data is loaded into it. Instead, if I open the MDB with Access I see 8 empty rows. I tried to change some parameters on the schema.ini file such as add/remove MaxScnaRows=0, CharacterSet, and so on... and on the query I also tried to change the FROM Properties, but I always get the same result.

-- EDIT Added AccessDb.Query() method and the database connection string --

I didn't include the Query() method because AccessDb it's just a "DB tier" class that contains methods that create connection to db and execute query/list query/scalar with/without parameters. I'm using this class in other pages and also in many other applications.

Anyway, this is the AccessDb.Query() method.

public static void Query(string connString, string query)
    {
        OleDbConnection conn = new OleDbConnection(connString);

        try
        {
            conn.Open();
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();
            cmd.Dispose();
        }
        catch (OleDbException odbEx)
        {
            throw odbEx;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            conn.Dispose();
            conn.Close();
        }
    }

This is my database connection string, configured inside Web.config file.

<connectionStrings>
    <add name="DefaultConnection" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Progetti\Personale\Progetti\Infomed\Database\Infomed.mdb;User Id=admin;Password=;" />
</connectionStrings>

The code you added is helpful, but we still don't know what your `connString` looks like. — Gord Thompson, Mar 05 '14 at 13:33

Gord Thompson · Answer 1 · 2014-03-05T13:51:39.197

The code that you posted is essentially correct. I copied and pasted it into a new C# project and tweaked it just enough to get it to run. When I did get it to run it worked fine, creating the new table and importing all three (3) rows into it.

The main difference is that I just used the following code to execute the SELECT * INTO ... query. Instead of...

AccessDb.Query(AccessDbConnString, query);

...I used...

using (OleDbConnection con = new OleDbConnection())
{
    con.ConnectionString =
            @"Provider=Microsoft.Jet.OLEDB.4.0;" +
            @"Data Source=C:\Users\Public\test\CsvImportTest\MyDb.mdb;";
    con.Open();
    using (OleDbCommand cmd = new OleDbCommand())
    {
        cmd.Connection = con;
        cmd.CommandText = query;
        cmd.ExecuteNonQuery();
    }
    con.Close();
}

The complete code is:

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace CsvImportTest
{
    class Program
    {
        static string FilePath = @"C:\Users\Public\test\CsvImportTest\TestData.csv";

        static void Main(string[] args)
        {
            ImportCsvIntoTemp();
            Console.WriteLine("Done.");
        }

        private static void ImportCsvIntoTemp()
        {
            try
            {
                CreateCsvSchemaFile();

                string query = @"SELECT * INTO TEMP_CSV 
                                FROM [Text;HDR=no;Database={0}].[{1}]";

                query = String.Format(query, Path.GetDirectoryName(FilePath), Path.GetFileName(FilePath));

                //AccessDb.Query(AccessDbConnString, query);
                using (OleDbConnection con = new OleDbConnection())
                {
                    con.ConnectionString =
                            @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                            @"Data Source=C:\Users\Public\test\CsvImportTest\MyDb.mdb;";
                    con.Open();
                    using (OleDbCommand cmd = new OleDbCommand())
                    {
                        cmd.Connection = con;
                        cmd.CommandText = query;
                        cmd.ExecuteNonQuery();
                    }
                    con.Close();
                }
            }
            catch (Exception ex)
            {
                string message = String.Format("CSV file import failed. Inner Exception: {0}", ex.Message);
                Console.WriteLine(message);
                //throw new ImportFailedException(message);
            }
        }

        private static void CreateCsvSchemaFile()
        {
            using (FileStream fs = new FileStream(Path.GetDirectoryName(FilePath) + "\\schema.ini", FileMode.Create, FileAccess.Write))
            {
                using (StreamWriter sw = new StreamWriter(fs))
                {
                    sw.WriteLine("[" + Path.GetFileName(FilePath) + "]");
                    sw.WriteLine("ColNameHeader=True");
                    //sw.WriteLine("MaxScanRows=0");
                    sw.WriteLine("Format=Delimited(;)");
                    sw.WriteLine("DateTimeFormat=yyyy-MM-dd");
                    sw.WriteLine("CharacterSet=ANSI");
                    sw.WriteLine("DecimalSymbol=,");
                    sw.WriteLine("Col1=\"Operating Unit Organization Name\" Text Width 255");
                    sw.WriteLine("Col2=\"Year\" Long");
                    sw.WriteLine("Col3=\"Sales Rep Name\" Text Width 255");
                    sw.WriteLine("Col4=\"Date\" DateTime");
                    sw.WriteLine("Col5=\"Week\" Text Width 255");
                    sw.WriteLine("Col6=\"Product Number\" Text Width 255");
                    sw.WriteLine("Col7=\"Account Name\" Text Width 255");
                    sw.WriteLine("Col8=\"Customer Number\" Text Width 255");
                    sw.WriteLine("Col9=\"Corporate Brand\" Text Width 255");
                    sw.WriteLine("Col10=\"Brand\" Text Width 255");
                    sw.WriteLine("Col11=\"Ordered Quantity\" Long");
                    sw.WriteLine("Col12=\"Amount\" Currency");
                    sw.Close();
                    sw.Dispose();
                }

                fs.Close();
                fs.Dispose();
            }
        }


    }
}

Check the code you are using to actually execute the SELECT * INTO ... query and see if you can find a significant difference between your code and mine.

Thanks for testing! At least I know it works... somehow! I added the `AccessDb.Query()` method definition to the original post. Take a look... — Cheshire Cat, Mar 05 '14 at 13:25
@RainbowCoder Well, one thing I notice is that your code is using an `OleDbCommand` object and mine uses an `OdbcCommand` object. The MSDN page "[Schema.ini File (Text File Driver)](http://msdn.microsoft.com/en-us/library/ms709353%28v=vs.85%29.aspx)" is definitely part of the ODBC section of the MSDN Library. — Gord Thompson, Mar 05 '14 at 13:31
OK... but, since OLEDB it's a Microsoft "wrapper" around ODBC, that is an older API, why would it not works? See also: [link](http://stackoverflow.com/questions/103167/what-is-the-difference-between-ole-db-and-odbc-data-sources) — Cheshire Cat, Mar 05 '14 at 13:44
OLEDB should be more efficent than ODBC. Anyway I can't refactor my AccessDb class since I also use it to access non relational databases, such as .XLS and .XML files. Could you make a try with the Oledb Driver and my connection string to the .MDB file? — Cheshire Cat, Mar 05 '14 at 13:52
@RainbowCoder I just switched my code to use OleDb and it works for me that way, too. (I have updated my answer.) — Gord Thompson, Mar 05 '14 at 13:53
Great... so what am I doing wrong? Could it be related to the connection string? Or some system setting...? — Cheshire Cat, Mar 05 '14 at 13:57
@RainbowCoder One thing that *might* make a difference is the Windows locale setting. Mine is "English (United States)". If yours is something different then you could try temporarily setting it to "English (United States)" in the Regional Settings control panel in Windows and see if that makes any difference. (It *shouldn't*, but....) — Gord Thompson, Mar 05 '14 at 14:03
Aaaah! Hell no! Sorry... :-P Yes... you were right. Setting my Windows locale to "English (United States)" makes it work fine! Just to let you know, I'm using "Italian (Italy)". So now I can try to import one column at a time and see where's the problem... Or do you have any other good suggestion? — Cheshire Cat, Mar 05 '14 at 14:49

score 2 · Accepted Answer · answered Mar 10 '14 at 09:53

Finally I found where the problem was. First you should pay attention to the Character set of the file you want to import. You've to be sure that is ANSI if you set the schema.ini file to ANSI. Then I also had some troubles with the "DecimalSymbol=," option. After removing it the import worked fine. Anyway, with my locale setting (Italia (Italiano)) it should be correct, since the decimal symbol is ','...

So this is the final method that creates the schema.ini file.

private void CreateCsvSchemaFile()
    {
        using (FileStream fs = new FileStream(Path.GetDirectoryName(FilePath) + "\\schema.ini", FileMode.Create, FileAccess.Write))
        {
            using (StreamWriter sw = new StreamWriter(fs))
            {
                sw.WriteLine("[" + Path.GetFileName(FilePath) + "]");
                sw.WriteLine("ColNameHeader=True");
                sw.WriteLine("Format=Delimited(;)");
                sw.WriteLine("DateTimeFormat=yyyy-MM-dd");
                sw.WriteLine("CharacterSet=ANSI");
                sw.WriteLine("Col1=\"Operating Unit Organization Name\" Text Width 255");
                sw.WriteLine("Col2=\"Year\" Long");
                sw.WriteLine("Col3=\"Sales Rep Name\" Text Width 255");
                sw.WriteLine("Col4=\"Date\" DateTime");
                sw.WriteLine("Col5=\"Week\" Text Width 255");
                sw.WriteLine("Col6=\"Product Number\" Text Width 255");
                sw.WriteLine("Col7=\"Account Name\" Text Width 255");
                sw.WriteLine("Col8=\"Customer Number\" Text Width 255");
                sw.WriteLine("Col9=\"Corporate Brand\" Text Width 255");
                sw.WriteLine("Col10=\"Brand\" Text Width 255");
                sw.WriteLine("Col11=\"Ordered Quantity\" Long");
                sw.WriteLine("Col12=\"Amount\" Currency");
                sw.Close();
                sw.Dispose();
            }

            fs.Close();
            fs.Dispose();
        }
    }

C# import CSV file with schema.ini creates table but doesn't load data

2 Answers2