Click here to Skip to main content
15,905,508 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

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

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