How to specify the delimiter when importing CSV files via OLEDB in C#

Question

I need to perform a complex import in a Microsoft SQL Server 2000.

Since doing it in a DTS is too complicated, I'm trying to do it with a little C# program, but I'm having problems when I need to import a CSV files: this file is using semicolons as field delimiters instead of commas and I can't get the .NET's OLE DB provider to recognize it.

I already found various "solutions" on the net like using Extended Properties="Text; Format=Delimited" or ``Extended Properties="Text; Format=Delimited(;)"in the connection string or using aschema.ini` file to no avail.

This is the actual code I'm using:

DataTable Table = new DataTable();

using (OleDbConnection Connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=\"Text;HDR=Yes;Format=Delimited\""))
{
    Connection.Open();

    using (OleDbCommand Command = Connection.CreateCommand())
    {
        Command.CommandText = "select [Field 1], [Field 2] from [file.csv]";

        using (OleDbDataAdapter Adapter = new OleDbDataAdapter(Command))
        {
            Adapter.Fill(Table);
        }
    }
}

using (SqlConnection Connection = new SqlConnection("Data Source=server; Initial Catalog=database; User Id=user; Password=password;"))
{
    Connection.Open();

    using (SqlCommand Command = Connection.CreateCommand())
    {
        Command.CommandText = "insert into [table] ([field_1], [field_2], ...) values (@field_1, @field_2, ...)";

        Command.Parameters.Add("field_1", SqlDbType.Date, 0, "Field 1");
        Command.Parameters.Add("field_2", SqlDbType.VarChar, 100, "Field 2");
        ...

        using (SqlDataAdapter Adapter = new SqlDataAdapter())
        {
            Adapter.InsertCommand = Command;

            Adapter.Update(Table);
        }
    }
}

Any ideas on how to achieve using the semicolon as the field separator without relying on external libriaries?

Notes:

1. The "without relying on external libriaries" bit is because I need to import the file directly into the database and no library I found can do this (they return strings), and our PHB won't drop even a penny for a commercial solution.
2. I know I can import the file via a DTS, but I need to perform complex branching on the workflow and file alterations before and after the import, and that would result in jumping in and out of the DTS.
3. Doing everything inside the DTS would not be practical for me, as I'm not that skilled in ActiveX and VBScript coding.

Thanks in advance, Andrea.

Edit 1 - @andyb: Test program code for schema.ini approach:

String ConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=\"Text\"", Environment.CurrentDirectory);

DataTable Table = new DataTable();

using (OleDbConnection Connection = new OleDbConnection(ConnectionString))
{
    Connection.Open();

    using (OleDbCommand Command = Connection.CreateCommand())
    {
        Command.CommandText = "select * from [file.csv]";

        using (OleDbDataAdapter Adapter = new OleDbDataAdapter(Command))
        {
            Adapter.Fill(Table);
        }
    }
}

Barrel

Your code uses SQL provider syntax for the OleDbConnection string and OLE DB provider syntax inside the SqlConnection string. Could this be the problem? – Gobioid

@Panagiotis Kanavos: Cut & paste error, corrected. – Barrel

Answer 1

Commenter is right that you have your provider syntax the wrong way round.

However, this isn't the problem. Unfortunately, you cannot specify a custom delimiter in your oledb connection string. Instead, create a schema.ini file in the same directory as your source file containing the following:

[file.csv]
Format=Delimited(;)

Clumsy, but it does work.

Dishonesty

Hi, I've already tried the schema.ini approach, but it's not working, I still get the one column import. You can find the code I'm used in a new test program in the edited question's body. – Barrel

I've just copy-pasted your code and queried the DataTable and it's still working for me. Sorry to ask this, but you do have the correct filename in schema.ini? – Dishonesty

Yes, I double checked it and it's ok. Could it be that something is interfering with this? :\ – Barrel

I'm accepting this answer since other documents across the net are saying this method works, probably it's something on my side that's screwing it up. – Barrel

Thanks for that but this must be really frustrating. Have you tried the Microsoft Text Driver (also uses schema.ini) and System.Data.Odbc.OdbcConnection? e.g. using (OdbcConnection cn = new OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\\MyDir;")) { ... } You never know ... – Dishonesty

Does schema.ini have to be in your \bin folder (rather than the source code folder) perhaps? – Saba

It needs to be in the same folder as your data – Dishonesty

i found this approach works but it will trim the header even if i set "HDR=No;" – Defiant

Answer 2

The schema.ini file has to be saved in Unicode or ANSI, not as UTF-8.

Your data file must also be saved as Unicode not UTF-8.

Pires

Answer 3

You have to write your csv file name inside the schema.ini file (not [file.csv], e.g.: test.csv will have a schema.ini with [test.csv] text at line 0:

[test.csv]
Format=Delimited(;)

Wadai