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
<!--- | |
2024-07-02 convertExcelFile: ColdFusion UDF to convert an Excel file (XLS/XLSX) | |
to CSV/TSV/XML/JSON/SQL/HTML/MHT/DOC/PDF using TotalExcelConverter (command line) | |
For use with Windows; Requires https://www.coolutils.com/CommandLine/TotalExcelConverter | |
Blog: https://dev.to/gamesover/importing-data-from-a-pre-95-excel-2-worksheet-xls-file-56md | |
Tweet: https://x.com/gamesover/status/1829591479100342454 | |
---> | |
<cfscript> | |
public struct function convertExcelFile( | |
required string inputPath, | |
string outputPath="", | |
string convertTo="", | |
string delimiter="", | |
string table="myTable", | |
string additionalParams="", | |
numeric timeout=60 | |
) hint="I convert an Excel file (XLS/XLSX) to CSV/TSV/XML/JSON/SQL/HTML/MHT/DOC/PDF using TotalExcelConverter (command line)" { | |
local.timestart = gettickcount(); | |
local.exePath = "c:\TotalExcelConverter\ExcelConverter.exe"; | |
if (!fileExists(local.exePath)){ | |
throw(message="textConverter.convertExcel: TotalExcelConverter is not installed."); | |
} | |
if (!fileExists(arguments.inputPath)){ | |
throw(message="textConverter.convertExcel: Input file '#arguments.inputPath#' doesn't exist."); | |
} | |
if (!len(arguments.outputPath) && len(arguments.convertTo)){ | |
arguments.outputPath = arguments.inputPath.replaceAll("\.#listlast(arguments.inputPath,".")#$", ".#lcase(arguments.convertTo)#"); | |
} | |
if (!len(arguments.convertTo) && listlen(arguments.outputPath,".") gt 1){ | |
arguments.convertTo = lcase(listlast(arguments.outputPath,".")); | |
} | |
if (!listfind("csv,tsv,txt,xml,json,sql,html,mht,doc,pdf", lcase(arguments.convertTo))){ | |
throw(message="textConverter.convertExcel: Invalid ConvertTo'#arguments.convertTo#'."); | |
} | |
local.config = [ | |
"args": [ | |
"name": local.exePath, | |
"arguments": [ | |
"""#arguments.inputPath#""", | |
"""#arguments.outputPath#""" | |
], | |
"timeout": (val(arguments.timeout) gt 0) ? val(arguments.timeout) : 60 | |
], | |
"duration": 0 | |
]; | |
local.c = (arguments.convertTo eq "tsv") ? "csv" : arguments.convertTo; | |
arrayappend(local.config.args.arguments, "-C #ucase(local.c)#"); | |
if (arguments.convertTo eq "tsv"){ | |
arrayappend(local.config.args.arguments, "-td T"); | |
} else if (arguments.convertTo eq "csv"){ | |
if (listfindnocase(",|csv", arguments.delimiter, "|") || !len(arguments.delimiter)){ | |
arrayappend(local.config.args.arguments, "-td CSV"); | |
} else if (listfindnocase("#chr(9)#|T|TAB", arguments.delimiter, "|")){ | |
arrayappend(local.config.args.arguments, "-td T"); | |
} else if (listfindnocase(" |space", arguments.delimiter, "|")){ | |
arrayappend(local.config.args.arguments, "-td S"); | |
} else if (listfindnocase(";|semi|semicolon", arguments.delimiter, "|")){ | |
arrayappend(local.config.args.arguments, "-td Semi"); | |
} else if (len(arguments.delimiter)){ | |
arrayappend(local.config.args.arguments, "-td Other"); | |
arrayappend(local.config.args.arguments, "-tdo #asc(arguments.delimiter)#"); | |
} | |
} | |
if (arguments.convertTo eq "sql"){ | |
arrayappend(local.config.args.arguments, "-table #arguments.table#"); | |
} | |
if (len(trim(arguments.additionalParams))){ | |
arrayappend(local.config.args.arguments, trim(arguments.additionalParams)); | |
} | |
arrayappend(local.config.args.arguments, "-fo"); | |
local.args = duplicate(local.config.args); | |
local.args.arguments = arraytolist(local.args.arguments, " "); | |
cfexecute(attributecollection = local.args); | |
local.config.duration = javacast("long", gettickcount() - local.timestart); | |
return local.config; | |
} | |
</cfscript> |
Top comments (0)