Q: How do you solve a problem you don't know how to solve?
A: Turn it into a problem that you do know how to solve.
How can I import data to a MSSQL Server from an "Pre-95" Excel 2 Worksheet XLS file?
Reading data from an Excel 97 XLS or XLSX file is supported. You can use the third-party Spreadsheet CFML library (personally preferred) or use package manger to install the built-in cfSpreadsheet.
If you encounter an older Excel file, these java-based solution aren't much help. Apache POI (used by cfSpreadheet & Spreadsheet CFML) can't read older Excel files. You'll need to first export the worksheet data to something generic (like CSV/TSV) and then bulk import the file using SQL BULK INSERT (personally preferred; fast) or use Spreadsheet CFML to stream the data into memory and manually INSERT each row (very slow for large datasets).
POSSIBLE MSSQL APPROACH: Use OPENROWSET or OPENDATSOURCE functions or configure the file as a "linked server".
Can I automate converting Excel to other formats using command line tools or libraries?
I'm aware of two (2) CLI programs for Windows that can do this; Coolutils Total Excel Converter ($49.90) and Excel Converter (free)
I've used TotalExcelConverter vai the command line to convert a Pre-95 Excel file to a modern XLSX file or CSV/TSV file. In fact, it can convert the data to multiple formats: HTML/XHTML/MHT, SQL, XML, JSON, etc.
I discovered two (2) possible java solutions, but haven't explored them as they're both dependent on Apache POI which doesn't support older XLS versions; dariober excelToCsv and informationsea excel2csv.
Further Documentation
- Convert Excel Files via Command Line with Total Excel Converter
- Excel Converter Command-Line Interface (CLI)
Source Code (demo)
https://gist.github.com/JamoCA/f60c4d0c4cefa76be6c4599f687ec0c6
Top comments (0)