0

I am currently working on a class project to produce a CSV reader in C#. I encountered errors when trying to read the data and read previous posts about trying to override the MaxScanRows task. I choose to incorporate a schema writing to the program that would write a schema at the point of reading the csv to a data grid view.

When trying to debug the code the console outputs this:

'TSP GUI.exe' (CLR v4.0.30319: TSP GUI.exe): Loaded 'C:\WINDOWS\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll'.

Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll

The code is:

public DataTable ReadCsv(string csvFileName)
        {
            DataTable dt = new DataTable();
            try
            {
                using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" +
                Path.GetDirectoryName(csvFileName) + "\";Extended Properties='text;HDR=Yes;IMEX=1;FMT=Delimited(,)';"))
                {
                    using (OleDbCommand cmd = new OleDbCommand(string.Format("select *from [{0}]", new FileInfo(csvFileName).Name), conn))
                    {
                        conn.Open();
                        using (OleDbDataAdapter obj_oledb_da = new OleDbDataAdapter(cmd))
                        {
                            DataTable dtSchema = new DataTable();
                            obj_oledb_da.FillSchema(dtSchema, SchemaType.Source);
                            if (dtSchema != null)
                            {
                                writeSchema(dtSchema);
                            }
                            obj_oledb_da.Fill(dt);
                        }
                    }
                }
            }
            catch
            {
                MessageBox.Show("Error occured");
            }
            return dt;
        }

The exception is thrown when conn.open(); runs.

Thanks for editing and passing direction to me,

Another option that will fix my problem is the extended properties for the connection string. I just dont know what to put to allow a CSV to be read with all rows as the csv file has mostly numerics in a row but rarely has a combination of Alphanumerics.

user12840244
  • 3
  • 2
  • Please don't add additional information in comments. Edit the question and add the information where it would have been if you'd included it initially. Don't use "edited" or "updated" type tags as we can tell what changed. Comments are for people to ask questions to get clarification or to make suggestions. If you use comments to answer or add new information you force us to read every comment to the question plus every answer to try to figure out what we need to know. – the Tin Man Feb 08 '20 at 00:03
  • Remove the `(,)` from here: `FMT=Delimited(,)`. This is not supported. Also, a comma is of course the default (it's the `C` in `CSV`). Possibly, use the `Microsoft.ACE.OLEDB.12.0` or `Microsoft.ACE.OLEDB.16.0` providers instead of `Jet.OLEDB.4`. If you need/have a different delimiter, you can use a `Schema.ini` file to specify a delimiter and more. See the notes and the sample code [here](https://stackoverflow.com/a/54352568/7444103). BTW, you don't need `conn.Open();`, the DataAdapter does that for you. You have to dispose of `dtSchema`. – Jimi Feb 08 '20 at 00:20
  • @Jimi Brilliant coding, used the link provided and changed to ACE.12 verison. Still having issues with missing cell values after a default 8 rows setting the column data type. IMEX=0/1 doesnt make any difference. Any recommendations? – user12840244 Feb 08 '20 at 01:26
  • I'm not really sure what *after a default 8 rows setting the column data type* means. If you mean you can parse correctly up to 8 rows and after that something goes wrong, then you have to post a sample of the data that makes your code fail. BTW, if you're seriously into CSV stuff, I suggest to make use of [CSVHelper](https://joshclose.github.io/CsvHelper/) or a similar, tested, library. – Jimi Feb 08 '20 at 01:38

0 Answers0