23

I know this topic is done to death but I am at wits end.

I need to parse a csv. It's a pretty average CSV and the parsing logic has been written using OleDB by another developer who swore that it work before he went on vacation :)

CSV sample:
Dispatch Date,Master Tape,Master Time Code,Material ID,Channel,Title,Version,Duration,Language,Producer,Edit Date,Packaging,1 st TX,Last TX,Usage,S&P Rating,Comments,Replace,Event TX Date,Alternate Title
,a,b,c,d,e,f,g,h,,i,,j,k,,l,m,,n,

The problem I have is that I get various errors depending on the connection string I try.

when I try the connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source="D:\TEST.csv\";Extended Properties="text;HDR=No;FMT=Delimited"

I get the error:

'D:\TEST.csv' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

When I try the connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TEST.csv;Extended Properties=Excel 12.0;

or the connection string

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.csv;Extended Properties=Excel 8.0;

I get the error:

External table is not in the expected format.

I am considering throwing away all the code and starting from scratch. Is there something obvious I am doing wrong?

Zulfi Tapia
  • 516
  • 1
  • 4
  • 14

6 Answers6

43

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).

Marcos Meli
  • 3,468
  • 24
  • 29
Darin Dimitrov
  • 1,023,142
  • 271
  • 3,287
  • 2,928
  • i cannot thank you enough! can you point me to some decent csv parsers? additionally where can i read up on the connection strings? its like voodoo to me as of now. – Zulfi Tapia Jul 25 '11 at 09:57
  • 1
    @Zulfi Tapia, I have pointed you to 2 *decent* CSV parsers at the end of my answer. – Darin Dimitrov Jul 25 '11 at 09:59
  • another alternate CSV parser was sugegsted by me and it is from Microsoft too. See my answer below which starts with 'Alternate solution...' – Vijay Gill Jul 26 '11 at 12:03
  • http://stackoverflow.com/questions/6455909/ierrorinfo-getdescription-failed-with-e-fail0x80004005-system-data-while-data-a – Arunkumar Chandrasekaran Jul 26 '11 at 12:59
  • Worked for me with a space in the file name. Thanks for simple, clean solution! – CraigP Jun 09 '17 at 03:38
  • I would love to use decent csv parsers but as is getting quite common these days unintelligent IT dept bods keep restricting and locking down pc's. I have to create something test it in different machine in vs, then can only use it in a SSIS script package. – Eddy Jawed May 16 '18 at 21:17
  • I used the same code but Code is not throwing exceptions but data is not getting copied into the table – santhosha Jun 16 '20 at 15:06
1

Alternate solution is to use TextFieldParser class (part of .Net framework itself.) https://learn.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.fileio.textfieldparser

This way you do not have to rely on other developer who has gone for holidays. I have used it so many times and have not hit any snag.

I have posted this from work (hence I cannot post an example snippet. I will do so when I go home this evening).

Vijay Gill
  • 1,508
  • 1
  • 14
  • 16
  • A short example would be a great addition to this answer. – Carol Mar 04 '19 at 17:51
  • @Carol - I will do so. I must have posted the answer while I was at work and hence could not post example. I will do so this evening when I go home. – Vijay Gill Mar 07 '19 at 09:07
  • @Carol There are quite a few around. [This](https://stackoverflow.com/a/48809517/395685) is one I posted here on SO a while ago. – Nyerguds Mar 07 '19 at 09:17
0

It seems your first row contains the column names, so you need to include the HDR=YES property, like this:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\TEST.csv;Extended Properties="Excel 12.0;HDR=YES";
Edwin de Koning
  • 14,209
  • 7
  • 56
  • 74
0

Try the connection string:

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\TEST.csv;Extended Properties=\"Excel 8.0;IMEX=1\""
davecoulter
  • 1,806
  • 13
  • 15
0
 var  s=@"D:\TEST.csv";
 string dir = Path.GetDirectoryName(s);
 string sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
                       + "Data Source=\"" + dir + "\\\";"
                       + "Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
Arunkumar Chandrasekaran
  • 1,211
  • 4
  • 21
  • 40
  • This takes me past the two errors I have mentioned above but since we are looking directly at a directory how do I specify what file to pick up? It crashes and tells me `The Microsoft Jet database engine could not find the object 'TEST$.txt'. Make sure the object exists and that you spell its name and the path name correctly.` – Zulfi Tapia Jul 25 '11 at 09:18
0

With this article I found that in MS Access you can also do

SELECT *
FROM [Text;Hdr=Yes;Database=X:\Full\Path\To\].ANSICodedCommaSeparatedText.csv;
Helge Telefonmann
  • 9
  • 2