Hi everyone, I'm currently doing some research on reading spreadsheet files (.csv and .xls) in ColdFusion and then creating or updating tables in a SQL database based on the data from the spreadsheet file. Does anyone here have any experience with this, advice, or just general direction for this task?

1, force everything into CSV format. XL files can contain macros and other data you do not want. 2, make sure you double-check and validate the input values of data before doing anything else. 3, Until you have the above down, place all data in a staging table and correct your code and or files accordingly. THat is of corse you want Table 1, Cell 1, to have CSV data object "1" in row 1, column 1, Otherwise if you are just storing XL or CSV files in SQL, you can store the hole objects as BLOBS

Thanks. I guess forcing users to give me .csv files isn't out of the question, as they can just save their .xls files as .csv in Excel.

We’ve done quite a bit of this sort of thing with Excel files but not CSV. Not sure how similar the contexts might be to yours. I can’t really share code but can share some general thoughts: • We’re in a position where we get to define the basic structure the sheet has to conform to (required sheet names, required column headings, data types, and rules about what constitutes “valid” data. As a result, we’ve developed a pretty extensive group of classes to simplify validating an arbitrary Excel file uploaded by a user to make sure we can use it and to provide feedback to the user about anything we don’t like. • We take the approach of turning the sheet into a in-memory query with a layer we’ve developed that sits on top of Apache POI to provide the flexibility and speed we need. We regularly deal with sheets with 30K+ rows and which can be as wide as 40 columns. • At some point in the process, once we’re sure what we have is going to be valid enough to go into the database, we move it into the database and do any really heavy lifting in terms of validation or processing there (e.g, validating rows of data against each other or against other data already present within the database), and it usually goes into a semi-temporary table there for holding and that sort of validation before actually going into the “real” tables. • There are a near infinite number of ways users can dork up an Excel template you provide for them to provide data for your system to user. Validate against them all and more. If you’re working with stuff at the POI level, it’s easy to reject stuff with formulas and links to external tables and macros and stuff that can cause problems and bring lots of baggage with them. • Working with CSVs has some significant advantages for bulk import that may make parts of this more performant but also less friendly to some types of users and may make some of the pre-database validation a little harder. You probably have to pick your poison in choosing between the two. Maybe that will spur some questions for you that we can riff on?

Thanks rstewart! What does your process look like for taking the query object and putting it into the database?

I never could get it to work as of CF2018

we gave up on cfspreadsheet early on and never looked at it again based. on our needs for validating incoming data, so i can’t answer any questions about it other than what I might learn from reading the docs. 🙂

it can do xlsx files, but there's a bug in cf2021 that it doesn't close them properly when it's done. So then you can't delete them.

We read the CSV files line by line, looking for the number of Data objects separated by commas, that need to match row 0. If any rows are missing the required data, We will fit with a "FIXME" flag, store the temporary data in a temp table and send it to someone to correct their import. It's not elegant, but it's far better than having to handhold users.

RE our process of going from in-memory query to getting it into the database: it’s not pretty and it’s not particularly fast. we chunk that in-memory query into groups of inserts that vary in size (number of rows) depending on width and size of data types. for our larger processes, we reserialize the in memory query to a storage location accessible to the database server as a CSV (which is very fast) and then do a bulk import from the CSV which is also very fast… but that’s only after we have done enough validation on content to have a very high degree of confidence that it will go in cleanly. that’s way faster than groups of inserts.

Gotcha. I was thinking I would have to do something similar. Right now, I send one really chunky update query that updates each individual row of the table in the database.

I wish we had standardized data, instead its always user input. Read the file line by line, write two files, errors and input, then csv the input and send back the errors. Its only drawback is We found we cant remotely mount storage when using this method, so a small box must have enough storage to handle the files, vs our standard puppet show. Using remote storage is ultra slow.

Thanks for the input thus far

Yes, was going to say.- don't use cfspreadsheet - use spreadsheet-cfml. We are switching out all use of cfspreadsheet to it. It uses less memory and because it's on github I've been able to make pull requests for our needs which are now part of the project.

spreadsheet-cfml recently added the ability to read large spreadsheets via POI's streaming API, but it currently only works in Lucee: https://blog.simplicityweb.co.uk/130/reading-large-spreadsheets-with-lucee

We have a process that makes use of spreadsheetRead, then other spreadsheet functions, to read the individual cells for processing. It took some guess work and endless trial & error to get the sequence of functions right but once there it works well. Our process reads an XLSX file with 4 separate tabs and parses data from all 4 to create a reformatted output XLSX. The sheets we process are a couple of hundred rows per tab and performance is good.