none
Reading a CSV file with embedded periods in the file name using OleDbCommand RRS feed

  • Question

  • I have some existing code that reads data from simple CSV (not Excel) files.  Works fine, except that a user tried to specify a file name with embedded periods (my.text.file.csv).  This code shows the basic idea:

     

    OleDbConnection conn;
    OleDbCommand Command;

     

    // this filename works
    //string file = @"c:\mytextfile.csv";

     

    // this filename does not work
    string file = @"c:\my.text.file.csv";

     

    string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +

    Path.GetDirectoryName(file) +

    ";Extended Properties='text;HDR=Yes'";

     

    string sql = "select * from [" + Path.GetFileName(file) + "]";

    conn = new OleDbConnection(connStr);
    cmd = new OleDbCommand(sql, conn);
    conn.Open();
    cmd.ExecuteReader()

     

    ExecuteReader fails with this error:
    The Microsoft Jet database engine could not find the object 'my.text.file.csv'.  Make sure the object exists
    and that you spell its name and the path name correctly.

     

    I am escaping the filename in the select statement with brackets, but it doesn't help.  Anyone have any idea on how to properly escape the select statement?

    Friday, March 14, 2008 10:27 PM

Answers

All replies

  • Ran into similar problem - resolved by using the 8.3 shortname by calling kernel32 library GetShortPathName function.  I'm using VBA - looks like you're using VB.NET, which doesn't directly implement this function.  Here's an article that references using that function in .NET - http://msdn2.microsoft.com/en-us/magazine/cc163515.aspx - see last question.

     

    Friday, April 4, 2008 11:16 PM
  • I did contact Microsoft developer support and was told that this was a known problem and was listed as a limitation (that would not likely be fixed).  I was going to change my code to disallow filenames with embedded periods, but your solution is a great idea that I hadn't considered.  Thank you!

     

    Monday, April 7, 2008 3:05 PM
  • Did they refer to any KB number in particular?  I've encountered the same issue, and need to note this issue in production documentation. 

    Thanks
    Thursday, January 15, 2009 5:10 PM