Using Drupal Feeds Module to Import Content from MS Access

Drupal Feeds Module works brilliantly, but there is a couple weird gotchas when working from MS Access to Drupal that are worth documenting:

  1. Seems self-explanatory, but since you are importing as .txt or .csv make sure you remove all carriage returns.  In MS Access this involves writing an update query and doing a replace on all Chr(10) and Chr(13).  e.g. Replace(Replace([myfield],Chr(10),"<br />"),Chr(13),"<br />")
  2. Although CCK Date fields import perfectly as MS Access dates, published dates do not.  This is because the published field in Drupal is a Unix Timestamp which is a quite different than Access dates. 

    To fix you need to first account for the difference between the start dates of the two time systems and then account for the fact that is decimal days and unix is seconds (or something like that.


    Access -> Unix
    DateDiff("s", #1/1/1970#,[myAccessDate]) AS myUnixDate

    Unix -> Access
    DateAdd("s", [myUnixDate],
    #1/1/1970#) As myAccessDate

  3. Exporting from MS Access can also cause you problems with Memo fields.  Strangely, if you use Text Qualifiers your exported Memo fields will be truncated to 512 characters.  Don't do that.  There is a better way - export tables not queries and this won't happen.
  4. If Importing Taxonomy fields to Drupal, you'll need to edit your Taxonomies at least for now to Free Text.  You can set them back later, but at least for me, even if the Term existed it wouldn't insert unless I set it to Free Text.  Your mileage may vary.
  5. Apostrophes will cause you nightmares. They terminate strings early. Replace them with right apostrophes or quotes.
  6. Real HTML required. If you're like me and you've stored all your <HTML> as &lt;HTML&gt; in your ASP and .Net applications to get around Microsoft injection filters you'll have to undo all that silliness.

And I think that's it. If I remember any more from this evening I'll edit this post. Good times.

Finding and Replacing Special Characters in MS Access

Most special characters like Tabs and Line Break and Carriage Returns can be found and replaced in MS Access by using the character codes.  But you can't use the find and replace dialog.

Instead, author an update query and enter the following to remove tabs:

If removing line breaks you need to replace both the line break and carriage return character:

If editing for HTML you'd edit this to the following for HTML line breaks:
Replace(Replace([myfield],Chr(10),"<br />"),Chr(13),"<br />")

For other characters, look up the ascii character code. (use the numbers in the "Dec" column in the tables)

MS Access Truncating Exports of Memo Fields at 512 Characters

Recently I was surprised to find that exports of Memo fields to csv and txt truncate at 512 characters.

Thankfully, there is an easy, albeit poorly documented hack.  In the export wizard set "Text Qualifier" to "{none}"