Please use this formula to change from Excel date to Unix date, then you can use “gmdate” to get the real date in PHP: UNIX_DATE = (EXCEL_DATE – 25569) * 86400 and to convert from Unix date to Excel date, use this formula: EXCEL_DATE = 25569 + (UNIX_DATE / 86400) After doing this formula into … Read more
Please use this formula to change from Excel date to Unix date, then you can use “gmdate” to get the real date in PHP: UNIX_DATE = (EXCEL_DATE – 25569) * 86400 and to convert from Unix date to Excel date, use this formula: EXCEL_DATE = 25569 + (UNIX_DATE / 86400) After putting this formula into … Read more
I think this should do what you’re asking. The other function is there just to deal with if you have shared strings, which I assume you do in your column headers. Not sure this is perfect, but I hope it helps. static void Main(string[] args) { DataTable dt = new DataTable(); using (SpreadsheetDocument spreadSheetDocument = … Read more
Unfortunately, you can’t set ImportMixedTypes or TypeGuessRows from the connection string since those settings are defined in the registry. For the ACE OleDb driver, they’re stored at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel in the registry. So, you can simplify your connection string to: conn.ConnectionString = @”Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Nick\Desktop\Pricing2.xlsx;Extended Properties=””Excel 12.0 Xml;HDR=Yes;IMEX=1;”””; Once you set TypeGuessRows to 0 and … Read more
There are a few different libraries doing parsing of Excel files (.xlsx). I will list two projects I find interesting and worth looking into. Node-xlsx Excel parser and builder. It’s kind of a wrapper for a popular project JS-XLSX, which is a pure javascript implementation from the Office Open XML spec. node-xlsx project page Example … Read more
I’m going to throw my hand in for flat csv files, if only because you’ve got the greatest control over the code. Just make sure that you read in the rows and process them one at a time (reading the document to the end and splitting will eat up all of your memory – same … Read more
As others have suggested, csv reading is faster. So if you are on windows and have Excel, you could call a vbscript to convert the Excel to csv and then read the csv. I tried the script below and it took about 30 seconds. # create a list with sheet numbers you want to process … Read more
You have 2 choices as far as I know: Spreadsheet_Excel_Reader, which knows the Office 2003 binary format PHPExcel, which knows both Office 2003 as well as Excel 2007 (XML). (Follow the link, and you’ll see they upgraded this library to PHPSpreadSheet) PHPExcel uses Spreadsheet_Excel_Reader for the Office 2003 format. Update: I once had to read … Read more
“External table is not in the expected format.” typically occurs when trying to use an Excel 2007 file with a connection string that uses: Microsoft.Jet.OLEDB.4.0 and Extended Properties=Excel 8.0 Using the following connection string seems to fix most problems. public static string path = @”C:\src\RedirectApplication\RedirectApplication\301s.xlsx”; public static string connStr = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + path + … Read more