1

I have problem in connection while getting datas from excel. It is working very well in .xlsx file but it is not worked fot .xls file for format of Excel 8.0. How can I solve this problem? I tried to add "HDR:YES" or "IMEX:1" but not working. Here is my code;

                string connString = "";
                        if (sFileExtension == ".xls")

                            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(string.Format("Upload/Belgeler/Tmp/{0}_{1}/{2}", DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString(), sFileName)) + ";Extended Properties=Excel 8.0";
                        else if (sFileExtension == ".xlsx")
                            connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(string.Format("Upload/Belgeler/Tmp/{0}_{1}/{2}", DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString(), sFileName)) + ";Extended Properties=Excel 12.0";



                        // Create the connection object
                        OleDbConnection oledbConn = new OleDbConnection(connString);
                        try
                        {
                            // Open connection
                            oledbConn.Open();

                            DataTable dtSheetName = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            if (dtSheetName == null || dtSheetName.Rows.Count == 0)
                            {
                                if (oledbConn.State == ConnectionState.Open)
                                    oledbConn.Close();
                                throw new Exception("Excel belgesi içinde Sayfa[Sheet] bulunamadı!");
                            }
                            // Create OleDbCommand object and select data from worksheet Sheet1 
                            DataSet ds = new DataSet();
                            using (OleDbCommand cmdSheet = new OleDbCommand("SELECT * FROM [" + dtSheetName.Rows[0]["TABLE_NAME"].ToString() + "]", oledbConn))
                            {
                                OleDbDataAdapter oleda = new OleDbDataAdapter();
                                oleda.SelectCommand = cmdSheet;
                                oleda.Fill(ds, "ExcelFields");

                            }
                            oledbConn.Close();
Johnny Willemsen
  • 2,942
  • 1
  • 14
  • 16
KAYA
  • 49
  • 3
  • 11
  • Try using Excel 12.0 for both. In my experience the driver sorts it out just fine. If that does not work then it may be the case that your .xls file is broken in some way so try re-save it and see if you can open it. – Ciarán Jan 11 '18 at 13:08
  • It is not working. – KAYA Jan 11 '18 at 13:57

1 Answers1

0

This happens when the Excel file is corrupted and cannot be opened by Access Database Engine (OLE DB provider) even if you can open the file from Excel. In a similar case opening the file manually and saving it as a new file may do the trick Visit Getting "External table is not in the expected format." error while trying to import an Excel File in SSIS

Goda Kotb
  • 61
  • 2