CODEWITHSUNDEEP
c#visual-studio-2010csvoledb
daidai
daidai

Reputation: 541

Can't open csv file using OleDB

I am new in this so i cant find the solution for my problem.I want to read .csv file in DataTable using OleDB.Here is my code

string file = "D:\\MyFile.csv";
        string dir = Path.GetDirectoryName(file);

        String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";
        OleDbConnection objConn = new OleDbConnection(sConnectionString);

        objConn.Open();
        DataTable dt = new DataTable();
        OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file + "]", objConn);
        OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
        objAdapter1.SelectCommand = objCmdSelect;
        objAdapter1.Fill(dt);
        objConn.Close();

But I am getting an error: The Microsoft Jet database engine could not find the object 'D:\MyFile.csv'. Make sure the object exists and that you spell its name and the path name correctly.

The file is on the right place, so can you tell me what could be the problem?

Upvotes: 1

Views: 2258

Answers (3)

Deepu Madhusoodanan
Deepu Madhusoodanan

Reputation: 1505

Try with actual filename in select query.

SELECT * FROM [MyFile.csv]

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [MyFile.csv]", objConn);

Upvotes: 0

Thorsten Dittmar
Thorsten Dittmar

Reputation: 56697

You need to remove the path from the file name in this line:

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + file + "]", objConn);

It should read:

OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + Path.GetFileName(file) + "]", objConn);

In the connection string you're telling the engine the directory, in the command you're telling the engine the file to use (the path could be seen as the equivalent of a database, the file as the equivalent of a table).

You could improve this as follows:

string file = "D:\\MyFile.csv";
string dir = Path.GetDirectoryName(file);
string name = Path.GetFileName(file);

String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + ";Extended Properties='text;HDR=Yes;FMT=Delimited'";
...
OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [" + name + "]", objConn);
...

Upvotes: 1

Mohit
Mohit

Reputation: 2217

Place the file inside some folder in D:\ and then retry

GetDirectoryName functions usually returns '' when using such like

GetDirectoryName('C:\asd.txt') returns '' so use it like

GetDirectoryName('C:\myfol\asd.txt') it will returns 'C:\myfol'

and your code is concatinating directory name so chances are you are concatinating a blank string ''

in the line 1. String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dir + "; 2. sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0.; Data Source = ''; // as dir is ''

check http://msdn.microsoft.com/en-us/library/system.io.path.getdirectoryname(v=vs.110).aspx

for additional help.

Upvotes: 3

Related Questions