Error While converting CSV file to DataTable Adding .txt to the csv file name

Question

Below is my code to convert a csv file into DataTabel.

 public static DataTable ImportCSVtoDatatable(string filepath, string strQuery )
    {
        //strQuery = "Select * From [GJ20150417044150]";
        //string filepath= @"h$\\Data\\GJ20150417044150.csv";

        DataSet ds = new DataSet();
        var constring = string.Format(@"Provider=Microsoft.Jet.OleDb.4.0; Data Source={0};Extended Properties=""Text;HDR=YES;FMT=Delimited""", Path.GetDirectoryName(filepath));

        OleDbConnection con = new OleDbConnection(constring);

        OleDbDataAdapter da = new OleDbDataAdapter(strQuery, con);

        //Error Occuring on this step
        da.Fill(ds);
        DataTable dt = ds.Tables[0];
        return dt;
    }

my program got crashed and the error I am getting is as follows:

The Microsoft Jet database engine could not find the object 'GJ20150417044150.txt'. Make sure the object exists and that you spell its name and the path name correctly.

what I guess is ,it's trying to search for "GJ20150417044150.txt" file but could not get it as it is actually "GJ20150417044150.csv" file and there is no "GJ20150417044150.txt" in the given path.

please help me in:

1: How to get rid of this error and select the desired .csv file to convert into datatable.

2: Why this .txt is Added to the GJ20150417044150 in the process

`Data Source` in connection string requires exact path, just pass it the file name, why are you using `Path.GetDirectoryName(filepath)` ? — Habib, Apr 17 '15 at 12:45
Refer to this answer http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable — Abbas, Apr 17 '15 at 12:49
@Habib I have tried that by only provideing the file path. it is showing the error as "Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides" — Debasish, Apr 17 '15 at 13:02
Some stuff is commented out in your code, you may want to try to use this: `var fileName = Path.GetFileName(filepath);` `strQuery = @"SELECT * FROM [" + fileName + "]";` — Alex, Apr 17 '15 at 13:02
@Alex: the strQuery that i am passing to the function is as you mentioned only. — Debasish, Apr 17 '15 at 13:10
@Habib: I referred the link and modified the code but same error.that .txt is still getting added. — Debasish, Apr 17 '15 at 13:11
Can you include in your question what is the exact value of `strQuery` is when `new OleDbDataAdapter(strQuery, con)` is called. — Alex, Apr 17 '15 at 13:14
@Alex, con:Provider=Microsoft.Jet.OleDb.4.0; Data Source=H:\Data;Extended Properties="Text;HDR=YES;FMT=Delimited" — Debasish, Apr 17 '15 at 13:40
`GJ20150417044150` != `GJ20150417044150.csv` and != `Path.GetFileName(filepath);` — Alex, Apr 17 '15 at 13:52
@Alex strQuery = @"SELECT * FROM [" + fileName + "]" here we have to give the **Sheet Name** in place of ** fileName**.my sheet name is **GJ20150417044150 **. my csv name is ***GJ20150417044150.csv.** here we have to give sheet name which acts as a table. thats why it is GJ20150417044150 != GJ20150417044150.csv — Debasish, Apr 17 '15 at 14:10
Yes of course you do, and how is that working out for you so far? Have you already tried using `"SELECT * FROM GJ20150417044150.csv"` ? Over an hour ago you said "the strQuery that i am passing to the function is as you mentioned only". Turns out it was not. But do as you like. Have a good day. — Alex, Apr 17 '15 at 14:21
@Alex: actually I had used it and due to some other problem it was showing some error.then in process of editing from looking in many sources I had commented that one.NOW I CHANGED THE CODE AND ITS WORKING FINE. THANKS A LOT MAN. SORRY FOR THE INCONVENIENCE TO YOU. — Debasish, Apr 17 '15 at 14:40
@Debasish Please try to listen to what people are trying to say when they are trying to help you find a solution to your problem the next time. I summarized the solution to your problem in an answer. Please check [what to do when my question gets answers](http://stackoverflow.com/help/someone-answers). — Alex, Apr 17 '15 at 15:13

score 1 · Accepted Answer · answered Apr 17 '15 at 15:05

So the solution to this problem is, to change these lines:

//strQuery = "Select * From [GJ20150417044150]";
//string filepath= @"h$\\Data\\GJ20150417044150.csv";

to this

var fileName = Path.GetFileName(filepath); 
strQuery = @"SELECT * FROM " + fileName;

The issue being that the value for strQuery that was being used did not contain the full filename, but only the GJ20150417044150 part, to which the Jet engine tried to append the default text extension .txt, when it could not find a file with the name GJ20150417044150.

Error While converting CSV file to DataTable Adding .txt to the csv file name

1 Answers1