Solved

Opening and reading a csv file created in any editor

Posted on 2008-06-11
4
900 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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