[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Opening and reading a csv file created in any editor

Posted on 2008-06-11
4
Medium Priority
?
907 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 2000 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

656 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