Solved

Opening and reading a csv file created in any editor

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

770 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