Click here to Skip to main content
14,830,943 members
Please Sign up or sign in to vote.
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
v4

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();
   
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........
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());
                            }
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900