Export MS Access Memo field and convert Unicode

0
votes

I have an Access 2003 database. A table has a Memo field and I'm having issues with getting that data out.

  • Exporting that field to a txt or csv chops that field off (255 characters)
  • Exporting as Excel gives me strange characters for linebreaks
  • Appending to a mysql database via myODBC gives an error about "incorrect string"
  • Using VBA with Scripting.FileSystemObject doesn't work unless you tell it to create the file as Unicode, which is okay, but then I can't get the file converted to something I can import

There are Unicode characters in some of my data. It's not a multi-linual database, so the only ones I can find are slanted quotes, probably copied in from Word. Dropping them is fine; the information in the fields will still be understandable.

Can I convert these Unicode characters to their (ANSI? ASCII?) equivalents? I've not dealt with encodings very much.

I tried playing around with iconv, but without knowing anything about the encoding, it didn't really help.

Right now, I need help on: - converting the characters in my database so they export non-unicode-ish-ly - OR, converting the unicode characters after the file has been exporting.

3
ms-accessunicodeencodingmemo
What are you trying to import it into that doesn't support Unicode? – JohnFx
I was using ruby to load it to mysql. I'm exporting to YAML and using the Ruby on Rails fixture loaders. The issue was on reading the file into YAML because of an odd character at the beginning of the file. Maybe it's an issue with the YAML library. I dunno. – wesgarrison
How are you exporting? Have defined an export spec? In Access 2003, this gives you a dropdown list with all the usual encodings (code pages), including Unicode UTF8. This is also how you define your memo field as having a length greater than 255 characters. – David-W-Fenton
I probably should have made that an answer -- adding it as an answer. – David-W-Fenton

3 Answers

1
votes

The file created by Access/VBA is UTF-16. For some reason, there is a character at the beginning of the file that was causing the Ruby YAML library to parse wrongly.

iconv to the rescue!

iconv -f UTF-16 -t ASCII -c utf_file.yml > ascii_file.yml
1
votes

How are you exporting? Have defined an export spec? In Access 2003, this gives you a dropdown list with all the usual encodings (code pages), including Unicode UTF8. This is also how you define your memo field as having a length greater than 255 characters.

-2
votes

Export to .xml