3 ms·
I end up parsing a lot of excels at work (not my choice). I saw this and thought it looked interesting - export Execl to a CSV and run a query there. At the en
by collyw 13y ago
I end up parsing a lot of excels at work (not my choice). I saw this and thought it looked interesting - export Execl to a CSV and run a query there.
At the end of the day the problem is that non technical users don't fill in things properly. They have a space before a number so it gets treated as text. Or in the same column Excel spits out an integer as a float. Basically the things that a real database keeps in order for you, so unfortunately I don't see it solving my problems. Shame.
- iagooar 13y agoI was going to write something like this. From my experience parsing a lot of CSV files, what's really tough isn't parsing data out of a nicely formatted CSV file, but cleaning and correcting the junk that is written into it. Sometimes I'd have to pass 3 or 4 different "correctors" and checkers in order to get an acceptable CSV file.
- collyw 13y agoI assume this is a common problem to be solved. Are there any best practices, or libraries, or techniques that I could look at to help. At the moment I parse and update the parser every time a new error appears. Most of the time it seems to be excel presenting things to the user that appear correct when they are not. I have even been tempted to write my own "data import grid". It would look like excel, have very little of the functionality, but could allow a developer to set rules on what can / can't be entered in each column.
- lsiebert 13y agoI feel you. Cleaning data is always a bitch. That said, if you are dealing with excel, you can use Spreadsheet-ParseExcel and Spreadsheet-WriteExcel to have perl handle excel .xls files directly (though I'm not sure about xls files including embedded vba macros).