3 ms·
fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql I personally
by emeril 4y ago
fwiw, one can import the contents of a list of excel files into one big excel file using some relatively vanilla vba without having to involve sql
I personally usually only involve a database if there's some significant amount of data involved and/or if it's significantly more efficient than the above
I don't get the hate on excel in this thread compared to rdbms's; i.e., just use a few vlookups for some joins which are usually sufficiently performant especially if you don't have that much data in which case you shouldn't be using excel in the first place... (though, if you're "clever" and want higher performance vlookups, just use it's often maligned indexed lookup feature more carefully to allow for both exact and indexed lookups which can be like >100x faster...)
- webdoodle 4y agoThe import wasn't the hard part, as you say, pretty vanilla. It was the export back to Excel that was time consuming. It involved recursive cell references, some that referenced other worksheets and one that reference another Excel file altogether. When I tried a direct export, the system would interpret the cell references as values instead of parts of a formula. I had to parse each formula using CHR(). It also got tricky when I wanted to format the cell, say for currency. The final VBA script was barely 100 lines... I may have some of this wrong, as it's been almost 9 years now.