LuckyLucks
asked on
Opening and reading a csv file created in any editor
Hi EEE:
I have a problem. I am loading a csv file to the database in .NET. By definition a csv file is a comma seperated file which can be created in a simple text editor like notepad or also in excel and saved as a csv. Now, when I read in the file (see code below) I will get the following error because the file is expected as an excel sheet. How can I make it generic to open a csv regardless of its file origin - text or excel.
Code:
// Read into dataset the first sheet of Excel
//
string connectionString;
string commandText;
System.Data.OleDb.OleDbCon nection conn;
System.Data.OleDb.OleDbCom mand command;
connectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
commandText = "select * from [sheet1$]";
conn = new System.Data.OleDb.OleDbCon nection (connectionString);
command = new System.Data.OleDb.OleDbCom mand (commandText, conn);
conn.Open ();
System.Data.OleDb.OleDbDat aAdapter da = new System.Data.OleDb.OleDbDat aAdapter (commandText, conn);
DataSet ds = new DataSet ();
da.Fill (ds, "FileData");
Error:
[OleDbException (0x80004005): External table is not in the expected format.]
System.Data.OleDb.OleDbCon nectionInt ernal..cto r(OleDbCon nectionStr ing constr, OleDbConnection connection) +1059617
System.Data.OleDb.OleDbCon nectionFac tory.Creat eConnectio n(DbConnec tionOption s options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
System.Data.ProviderBase.D bConnectio nFactory.C reateNonPo oledConnec tion(DbCon nection owningConnection, DbConnectionPoolGroup poolGroup) +27
System.Data.ProviderBase.D bConnectio nFactory.G etConnecti on(DbConne ction owningConnection) +47
System.Data.ProviderBase.D bConnectio nClosed.Op enConnecti on(DbConne ction outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.OleDb.OleDbCon nection.Op en() +37
I have a problem. I am loading a csv file to the database in .NET. By definition a csv file is a comma seperated file which can be created in a simple text editor like notepad or also in excel and saved as a csv. Now, when I read in the file (see code below) I will get the following error because the file is expected as an excel sheet. How can I make it generic to open a csv regardless of its file origin - text or excel.
Code:
// Read into dataset the first sheet of Excel
//
string connectionString;
string commandText;
System.Data.OleDb.OleDbCon
System.Data.OleDb.OleDbCom
connectionString = "Provider=Microsoft.Jet.OL
commandText = "select * from [sheet1$]";
conn = new System.Data.OleDb.OleDbCon
command = new System.Data.OleDb.OleDbCom
conn.Open ();
System.Data.OleDb.OleDbDat
DataSet ds = new DataSet ();
da.Fill (ds, "FileData");
Error:
[OleDbException (0x80004005): External table is not in the expected format.]
System.Data.OleDb.OleDbCon
System.Data.OleDb.OleDbCon
System.Data.ProviderBase.D
System.Data.ProviderBase.D
System.Data.ProviderBase.D
System.Data.OleDb.OleDbCon
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The connection string value looks like :-
Provider=Microsoft.Jet.OLE DB.4.0;Dat a Source=c:\inetpub\wwwroot\ myApplicat ion\Upload s\input_te xt.csv;Ext ended Properties=Text;HDR=Yes;FM T=Delimite d
And my syntax is :-
connectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=" + filePath + ";Extended Properties=Text;HDR=Yes;FM T=Delimite d";
Provider=Microsoft.Jet.OLE
And my syntax is :-
connectionString = "Provider=Microsoft.Jet.OL
ASKER
Apparently the problem was the filePath should not include the full physical path to the file.Instead it should include the full physical path only upto the folder in which the file resides.
ASKER
Code:
------------------------
string connectionString;
string commandText;
System.Data.OleDb.OleDbCon
System.Data.OleDb.OleDbCom
connectionString =
@"Provider=Microsoft.Jet.O
@";Extended Properties=""Text;HDR=Yes;
commandText = "Select * from " + brwUploadFile.FileName;
conn = new System.Data.OleDb.OleDbCon
command = new System.Data.OleDb.OleDbCom
conn.Open ();
System.Data.OleDb.OleDbDat
DataSet ds = new DataSet ();
da.Fill (ds, "FileData");
Error:
-------------------------
'c:\inetpub\wwwroot\myAppl
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbExc
Source Error:
Line 11771: DataSet ds = new DataSet ();
Line 11772:
Line 11773: da.Fill (ds, "FileData");
Line 11774:
Line 11775:
Stack Trace:
[OleDbException (0x80004005): 'c:\inetpub\wwwroot\myAppl
System.Data.OleDb.OleDbCon
System.Data.OleDb.OleDbCon
System.Data.ProviderBase.D
System.Data.ProviderBase.D
System.Data.ProviderBase.D
System.Data.OleDb.OleDbCon
System.Data.Common.DbDataA
System.Data.Common.DbDataA
System.Data.Common.DbDataA
myApplication.page1.Upload
myApplication.page1.btnDBU
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.WebControls.
System.Web.UI.Page.RaisePo
System.Web.UI.Page.RaisePo
System.Web.UI.Page.Process