How to Import .CSV sheetname using C#.net

Question

1.00/5 (1 vote)

See more:

Hi all,

Please help me to import .csv file sheet name.

C#

string full = Path.GetFullPath(excelFile);
string file = Path.GetFileName(full);
string dir = Path.GetDirectoryName(full);
connString = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + dir + \\";"
                             +  "Extended Properties=\"text;HDR=No;FMT=Delimited\"";
objConn = new OleDbConnection(connString); 
string query = "SELECT * FROM " + file; 
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connString);  
dAdapter.Fill(dTable);              
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

As I know for .csv extension files in connection string, we cant give file path for data source(as like as .xls or .xlsx file), instead we need to give the folder path in which uploaded .csv exists..

So same I have mentioned...
And I am able to get all uploaded .csv file values in dTable.
But while retrieving sheet names I am unable to pass Uploaded file name because...

To retrieve sheet name of an uploaded file we need to write this line

C#

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

But here I am passing objConn(Which consists datasource as dir )
So, dt retrieves all files present in that connectionstring dir folder ( in dt I need to retrieve sheet names present in uploaded .csv file ), and all those files are showing as a sheet names.

This is the Issue unable to clear and I need uploaded .csv file sheetnames..

How to do that ?
I Google from past 3 days, but I am unable to get the solution.

Please help.

Thanks in advance.

Posted 18-Mar-13 20:00pm

GaviGunda

Updated 18-Mar-13 20:26pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v4

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2013-03-19T00:23:00

Solution 1

The sheet names should be returned in the form <filename>#csv and you can use that name with the existing connection string to access the records of the file. Something like:

C#

string tableName = dt.Rows[0].["TABLE_NAME"].ToString();
OleDbCommand command = new OleDbCommand(string.Format("Select * From [{0}]", tableName), objConn );
OleDbDataReader reader = command.ExecuteReader();

Posted 19-Mar-13 0:23am

Richard MacCutchan

Comments

GaviGunda 19-Mar-13 7:21am

Hi Richard,

Thanks for your valuable replay,
in my folder path I have 30 uploaded files so while retrieving
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
with the help of objConn it displays all 30 files as TABLE_NAME I need last uploaded file so I am passing
tableName= dt.Rows[dt.Rows.Count-1]["TABLE_NAME"].ToString();

After this I need to retrieve csv sheetnames that can be done with
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); this code

But here again we are passing objConn here again it will take 30 names into dt....

So Unable to get solution... I need sheetnames..... Please help...

Richard MacCutchan 19-Mar-13 7:40am

Well I explained above, or thought I did, that the call to GetOleDbSchemaTable returns all the sheet (i.e. csv file) names. So you just need to iterate through those names to access the content of each file.

GaviGunda 19-Mar-13 7:58am

I tried in all the ways to retrieve sheetnames but I couldn't make through... ( I can get all csv filenames as TABLE_NAME values in dt but not SheetNames.. for .xls and .xlsx I am able to get sheetnames perfectly.. ) If you have any ideas/ suggestions please let me know thanks once again...

Richard MacCutchan 19-Mar-13 8:12am

csv files do not contain multiple sheets; each file is a unique sheet. You just need to iterate all the table records returned from GetOleDbSchemaTable and extract the names. Each sheetname then corresponds to a file, and you can extract its data with a SQL SELECT command.

GaviGunda 19-Mar-13 8:16am

oh k Thanks a lot...

GaviGunda 19-Mar-13 9:08am

Hi Richard,

I am getting 1 more problem... I extracted the file which I uploaded from 30 files but
I am getting filenames instead of sheetnames in the TABLE_NAME field of an dt
dt= objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

how to get sheetname from particular file(present inside csv)

In the output screen I am getting fileName instead of Sheet Name...

please help,,

Thanks,,,,

Richard MacCutchan 19-Mar-13 13:09pm

I really do not know what you mean. I have explained to you a number of times that .csv files do not contain multiple sheets, each file is the equivalent of a single sheet. The GetOleDbSchemaTable call returns a DataTable containing the names of all the .csv files found in the directory. You then select each (or any individual) table name from the list, each of which corresponds to a file. If the file name is mydata.csv then the table name will be returned as mytable#csv.

GaviGunda 19-Mar-13 13:25pm

thanks a lot Richard, Sorry for late replay I got the solution getting sheetname as mydata for a file mydata.csv...... but if the user changed sheet name to example for mydata.csv file means I am unable to get that example as a sheet name but I am getting mydata as a sheet name ...

Thanks a lot for your valuable help, I got to know something about .csv........

Member 11387722 · Answer 2 · 2015-02-24T18:58:00

C#

for (int i = 0; i < tablesNames.Rows.Count; i++)
                            {
                                string tableName = tablesNames.Rows[i]["TABLE_NAME"].ToString().Replace("$", "").Replace("#", "").Replace("csv", "").Replace("CSV", "");
                                tableName = tableName.Replace("'", "");
                                combomappingsheets.Items.Add(tableName.Trim());
                            }