Importing csv files must have a csv extension?

[James]

Hi,

I'm importing some csv files with this code

/// start of code snippet
int iPos = strFileName.LastIndexOf(@"\");

string strPath = strFileName.Substring(0,iPos);
string strSelect = "Select * from [" + strFileName.Substring(iPos+1) + "]";

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

strConnection += strPath;
strConnection += ";Extended Properties='text;HDR=No;FMT=Delimited'";

System.Data.OleDb.OleDbConnection CSVConnection = new
System.Data.OleDb.OleDbConnection(strConnection);
CSVConnection.Open();

System.Data.OleDb.OleDbDataAdapter CSVAdapter = new
System.Data.OleDb.OleDbDataAdapter(strSelect, CSVConnection);

DataSet CSVDataset = new DataSet("CSVFile");

CSVAdapter.Fill(CSVDataset, "CSVFile");

CSVConnection.Close();

DataTable CSVDatatable;
CSVDatatable = CSVDataset.Tables["CSVFile"];

/// end of code snippet

I have found that only files with a .csv extension can be opened otherwise I
get an exception that read 'Cannot Update, Database or Object is read-only'.
Is that normal and is there a way around it?

Also, since I'm using the Microsoft.Jet.OLEDB.4.0 provider will the users
machine need to have Access or Office installed in order for this to work?

Finally, should I be using another provider, just wondering since I took
most of it from a sample code.

Thank you for your time.

 

[Nicholas Paldino [.NET/C# MVP]]

James,

I don't know of any limitation that prevents you from using a text file
with an extension other than csv, but I can't say without a doubt that this
isn't a limitation.

You might want to try the text driver with the classes in
System.Data.Odbc, it might provide a better experience for you.

I believe the connection string would be something like this:


Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=c:\somepath\;Extensions=asc,csv,tab,txt;Persist Security
Info=False;

Note you can set the extensions in the connection string.

You won't be required to have Access installed to run Jet, you just have
to have the support files for the Jet provider, should you choose to use it.
It's just that Access typically installs this.

Hope this helps.

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

Take a look at the opennetcf.org csv's provider. It does works great and you
have the source code too !

Cheers,

 

[James]

James,

I don't know of any limitation that prevents you from using a text file
with an extension other than csv, but I can't say without a doubt that this
isn't a limitation.

You might want to try the text driver with the classes in
System.Data.Odbc, it might provide a better experience for you.

I believe the connection string would be something like this:


Driver={Microsoft Text Driver (*.txt;
*.csv)};Dbq=c:\somepath\;Extensions=asc,csv,tab,txt;Persist Security
Info=False;

Note you can set the extensions in the connection string.

You won't be required to have Access installed to run Jet, you just have
to have the support files for the Jet provider, should you choose to use it.
It's just that Access typically installs this.

Hope this helps.

Thanks for your help.

I tried it but started getting the 'data source name not found' error which
was like a scary flashback to my DAO days. I tried a few variations but I
think I'll stick to the Jet for a while.