OleDb Connection string for tab-delimited files

1
votes

I need to read a variety of data file types, such as xlsx, csv, txt, and mdb, and I want to use an OleDB connection so that the process of reading the files is the same, just with a different connection string. However, OleDB is ignoring the delimiter in connection strings such as the following and only reads comma-delimited.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Text;HDR=Yes;Delimited(\t)';
Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Text;HDR=Yes;FMT=TabDelimited';

I would prefer to have the OleDB engine do the work rather than parse the tab-delimited files myself.

There are several StackOverflow questions concerning this, and the solution is usually to create an .ini file in the same directory, but sometimes my users do not have write access to the folder. Seeing as all of the StackOverflow questions similar to mine are at least a couple years old, does anybody have any updated information on this issue?

1
oledboledbconnection

1 Answers

0
votes

This is how I've used | delimiter to read |-delimited .csv or .txt files using OleDB, however, I was using ACE engine and constructing connection string from C#:

connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.GetDirectoryName(catalogFile) + ";Extended Properties='text;HDR=YES;FMT=Delimited(" + (char)124 + ")'";

(char)124 stands for the ASCII code of |. Knowing that ASCII code of TAB is 9 you may try using this in your connection string:

...;Extended Properties='text;HDR=YES;FMT=Delimited(" + (char)9 + ")'";

Try the above code snippet and also try your code using MS Access Database Engine driver. Since it's newer, maybe it has better delimiter config handling.