From your SQL Server Management Studio, you open Object Explorer, go to your database where you want to load the data into, right click, then pick Tasks > Import Data. This opens the Import Data Wizard, which typically works pretty well for importing from Excel. You can pick an Excel file, pick what worksheet to … Read more
I found a solution by adding IMEX=1 for the connection string, but there’s a special format for it which descriped in the following link. The IMEX parameter is for columns that use mixed numeric and alpha values. The Excel driver will typically scan the first several rows in order to determine what data type to … 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
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
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
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
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