access to csv, the difficult way
This is kind of annoying. I do not have Microsoft Access installed on this machine, but was sent a database of stuff to import into my CMS.
I thought this would be a good time to write a CSV-importing tool for the CMS, so went to work converting the .mdb to .csv.
This was not as easy as it seems!
The only open-source program I could find to read the .mdb file was Open Office.org. I use that all the time, so that was fine by me.
Opening the file was simple enough, although the program asked me whether I wanted to register the .mdb database online (what the fuck?), and it also insisted on creating a new file to work from, instead of the .mdb. My guess is that OOo actually only reads its own format, but has a conversion tool for converting .mdb to .odt.
Once I had managed to open the file, I went into the Tables section and opened up the table I wanted as CSV. I expected there to be a simple “save as…” export function to save the table as either a .csv or even an OOo Calc document. But no…
I couldn’t even select multiple fields to copy into a spreadsheet or even a word-processor table!
In the end, after a few hours of absolute frustration, I found that this is the simplest way to convert a .mdb table to a .csv file (as far as I know):
- Import .mdb into OOo.
- Create a report, using the table you want, and all fields of that table.
- Select the entire table, and copy it to the clipboard.
- Create a new spreadsheet.
- Paste the clipboard contents to the spreadsheet.
- Save as CSV.
Now that, I think you will agree, is a very fucking roundabout way of doing this, but is the shortest way possible at the moment!
I was told that it was possible to shorten this, by replacing step 2 onwards with a simple SQL statement “
select * into text `file.csv` from `mytable`“, but of course, when I tried that, I got a bunch of errors.
I hope OOo becomes a little more interoperable with its other parts soon. It is a shame I can’t just select-all from the table view, and either paste directly into a spreadsheet, or save as a .csv.
Take a look at http://mdbtools.sourceforge.net/
Thanks, me. that will come in useful for future work!