Parsing CSV Using Oledb Using C#

Parsing CSV using OleDb using C#

You should indicate only the directory name in your connection string. The file name will be used to query:

var filename = @"c:\work\test.csv";
var connString = string.Format(
    @"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""", 
    Path.GetDirectoryName(filename)
);
using (var conn = new OleDbConnection(connString))
{
    conn.Open();
    var query = "SELECT * FROM [" + Path.GetFileName(filename) + "]";
    using (var adapter = new OleDbDataAdapter(query, conn))
    {
        var ds = new DataSet("CSV File");
        adapter.Fill(ds);
    }
}

And instead of OleDB you could use a decent CSV parser (or another one).

Parsing CSV using OleDb using C# - Error column contain values with in double quotes those are not loaded in datatable

I have tried this delimiter option and solve my problem OleDbConnection cnnOleDB = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';'"

here full version of the code,

private DataTable ReadFileByDataTable (string sFileName) {
DataTable dtPOFileData = new DataTable();

    using (OleDbConnection cnnOleDB = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Test;Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
    {
        cnnOleDB.Open();

        using (OleDbDataAdapter adpPOFileData = new OleDbDataAdapter("SELECT * FROM ["test.csv"]", cnnOleDB))
        {
            adpPOFileData.Fill(dtPOFileData);
        }
    }

    return dtPOFileData;
}

Reading CSV into using OLEDB

"This error will also be generated when the syntax of the connection string is incorrect." - msdn

When i tried to copy your connectionstring, it seemed liked there were alot of spaces in there.

 ConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+openFileDialog1.FileName +";Extended Properties=text;HDR=Yes;FMT=Delimited");

if this does not seem to resolve your issue, i suggest you take a look at this

Importing Csv file using oledb - not reading 1.2.3.4 value

Well,first of all I will assume your csv file is tab delimited.
What you need is to specify the column types for OleDb to correctly parse it. The best way I think is using a schema.ini file, that must be in the same path that your csv file is in. This is a sample of how it should look:

[test.csv]
Format=TabDelimited
ColNameHeader=false
MaxScanRows=0
Col1=Name Text
Col2=Policy Text
Col3=VersionName Text
Col4=ReleaseDate Text
Col5=Description Text
Col6=DownloadType Text
Col7=Version Text
Col8=FileUrl Text

As you see, in the first line you must specify your csv file name. Then I set the delimiter to tabs. After that, I deactivate that the first line is the headers line (this would lead you to delete the first line in your csv). An after that, you must define all your columns with the intended type.

Hope this helps.

CSV parser to parse double quotes via OLEDB

Just incase anyone has a similar issue, i wanted to post the code i used. i did end up using Textparser to get the file and parse ot the columns, but i am using recrusion to get the rest done and substrings.

 /// <summary>
        /// Parses each string passed as a "row".
        /// This routine accounts for both double quotes
        /// as well as commas currently, but can be added to
        /// </summary>
        /// <param name="row"> string or row to be parsed</param>
        /// <returns></returns>
        private List<String> ParseRowToList(String row)
        {
            List<String> returnValue = new List<String>();

            if (row[0] == '\"')
            {// Quoted String
                if (row.IndexOf("\",") > -1)
                {// There are more columns
                    returnValue = ParseRowToList(row.Substring(row.IndexOf("\",") + 2));
                    returnValue.Insert(0, row.Substring(1, row.IndexOf("\",") - 1));
                }
                else
                {// This is the last column
                    returnValue.Add(row.Substring(1, row.Length - 2));
                }
            }
            else
            {// Unquoted String
                if (row.IndexOf(",") > -1)
                {// There are more columns
                    returnValue = ParseRowToList(row.Substring(row.IndexOf(",") + 1));
                    returnValue.Insert(0, row.Substring(0, row.IndexOf(",")));
                }
                else
                {// This is the last column
                    returnValue.Add(row.Substring(0, row.Length));
                }
            }

            return returnValue;

        }

Then the code for Textparser is:

 // string pathFile = @"C:\TestFTP\TestCatalog.txt";
            string pathFile = @"C:\TestFTP\SomeFile.csv";

            List<String> stringList = new List<String>();
            TextFieldParser fieldParser = null;
            DataTable dtable = new DataTable();

            /* Set up TextFieldParser
                *  use the correct delimiter provided
                *  and path */
            fieldParser = new TextFieldParser(pathFile);
            /* Set that there are quotes in the file for fields and or column names */
            fieldParser.HasFieldsEnclosedInQuotes = true;

            /* delimiter by default to be used first */
            fieldParser.SetDelimiters(new string[] { "," });

            // Build Full table to be imported
            dtable = BuildDataTable(fieldParser, dtable);

When reading a CSV file using a DataReader and the OLEDB Jet data provider, how can I control column data types?

There's a schema file you can create that would tell ADO.NET how to interpret the CSV - in effect giving it a structure.

Try this: http://www.aspdotnetcodes.com/Importing_CSV_Database_Schema.ini.aspx

Or the most recent MS Documentation

---

Related Topics