i am using c# to parse some excel files to oracle.these files are
created by an automated 3rd party process and we have no control over
the naming convention and the version is excel 4.0 and the sheet names
are all very long (31 characters) when i use oledb to connect and get
the sheet name to select, it returns the filename with a $, and when
used in the select statement it fails because it is too long..i have
tried adox, this also returns the file name as the table name, the only
way i have been successful is in opening the excel file, renaming the
sheet and using the new name..but we don't want to use the excel
object.
I have also tried substr(sheetx,0,31) and adding in the $ but that also
did not work...so how does one use oledb to obtain the default sheet if
the name is too long or get the correct name instead of the file name?
here is the code:
strConnectionString="Provider=Microsoft.Jet.OLEDB. 4.0;Data
Source=c:\\temp\\EXPGRPSTEST_MIO_SBROSGLOBALXUSEMI _Sector_05242004_12M_GRPS.XLS;Extended
Properties=\"Excel 8.0;IMEX=1\"";
System.Data.DataTable dt = null;
OleDbConnection cnCSV =new OleDbConnection (strConnectionString);
cnCSV.Open();
dt = cnCSV.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
sheetx = dt.Rows[0]["TABLE_NAME"].ToString();
OleDbCommand cmdSelect = new OleDbCommand ("SELECT * FROM [" + sheetx
+"]", cnCSV);
OleDbDataAdapter daCSV = new OleDbDataAdapter();
daCSV.SelectCommand = cmdSelect;
DataTable dtCSV = new DataTable ();
daCSV.Fill(dtCSV);
dataGrid1.DataSource = dtCSV;
cnCSV.Close();
cnCSV.Dispose();
daCSV = null;
cnCSV.Close();
any help at all would be appreciated..
thanks
elena