Solved

Opening and reading a csv file created in any editor

Posted on 2008-06-11
4
896 Views
Last Modified: 2012-08-14
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.OleDbConnection conn;
                  System.Data.OleDb.OleDbCommand command;


                  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
                  commandText = "select * from [sheet1$]";

                  conn = new System.Data.OleDb.OleDbConnection (connectionString);
                  command = new System.Data.OleDb.OleDbCommand (commandText, conn);

                  conn.Open ();

                  System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter (commandText, conn);
                  DataSet ds = new DataSet ();

                  da.Fill (ds, "FileData");

Error:

[OleDbException (0x80004005): External table is not in the expected format.]
   System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1059617
   System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
   System.Data.OleDb.OleDbConnection.Open() +37
0
Comment
Question by:LuckyLucks
  • 3
4 Comments
 
LVL 14

Accepted Solution

by:
Daniel Junges earned 500 total points
ID: 21761618
try the follow:
string conString =  
  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\csv" + 
  @";Extended Properties=""Text;HDR=No;FMT=Delimited\""";
 
OleDbConnection conn = new OleDbConnection(conString);
OleDbDataAdapter da = new OleDbDataAdapter(@"Select * from table1.csv", conn);
da.Fill(...);

Open in new window

0
 

Author Comment

by:LuckyLucks
ID: 21762724
I have changed my code as per your suggestion but I get the following error. I have gone to the relevant folder on the server and the file was correctly saved there. However it is causing an error as below:-

Code:
------------------------
string connectionString;
string commandText;
System.Data.OleDb.OleDbConnection conn;
System.Data.OleDb.OleDbCommand command;

 
connectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
@";Extended Properties=""Text;HDR=Yes;FMT=Delimited\""";

commandText = "Select * from " + brwUploadFile.FileName;
conn = new System.Data.OleDb.OleDbConnection (connectionString);
command = new System.Data.OleDb.OleDbCommand (commandText, conn);

conn.Open ();

System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter (commandText, conn);
DataSet ds = new DataSet ();

da.Fill (ds, "FileData");

Error:
-------------------------
'c:\inetpub\wwwroot\myApplication\Uploads\testfile0.csv' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.
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.OleDbException: 'c:\inetpub\wwwroot\myApplication\Uploads\testfile0.csv' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

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\myApplication\Uploads\testfile0.csv' is not a valid path.  Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]
   System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection) +1059617
   System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject) +53
   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
   System.Data.OleDb.OleDbConnection.Open() +37
   System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
   myApplication.page1.UploadFile(String fileName, String fileExtension, String filePath) in c:\Inetpub\wwwroot\myApplication\page1.aspx.cs:11773
   myApplication.page1.btnDBUpload_Click(Object sender, EventArgs e) in c:\Inetpub\wwwroot\myApplication\page1.aspx.cs:11650
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

 
0
 

Author Comment

by:LuckyLucks
ID: 21769673
The connection string value looks like :-
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\inetpub\wwwroot\myApplication\Uploads\input_text.csv;Extended Properties=Text;HDR=Yes;FMT=Delimited

And my syntax is :-
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=Text;HDR=Yes;FMT=Delimited";
0
 

Author Comment

by:LuckyLucks
ID: 21814112
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.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question