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):

  1. Import .mdb into OOo.
  2. Create a report, using the table you want, and all fields of that table.
  3. Select the entire table, and copy it to the clipboard.
  4. Create a new spreadsheet.
  5. Paste the clipboard contents to the spreadsheet.
  6. 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.

2 Comments.

  1. Thanks, me. that will come in useful for future work!

%d bloggers like this: