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.

    Formulas:

    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.