DEV Community

Cover image for Importing Data from a Pre-95 Excel 2 Worksheet XLS file.
James Moberg
James Moberg

Posted on

Importing Data from a Pre-95 Excel 2 Worksheet XLS file.

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

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>
view raw convertExcelFile.cfm hosted with ā¤ by GitHub

Billboard image

Monitoring as code

With Checkly, you can use Playwright tests and Javascript to monitor end-to-end scenarios in your NextJS, Astro, Remix, or other application.

Get started now!

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

šŸ‘‹ Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay