Solved

Opening and reading a csv file created in any editor

Posted on 2008-06-11
4
888 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now