Feeds works brilliantly, but there is a couple weird gotchas when working from MS Access to Drupal that are worth documenting:
- 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 />")
- 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. The easiest way I know to fix this is to export your data to Excel and then use the Excel integer date value to convert to a Unix Timestamp.
We've all seen this right? That really long integer you see when you misformat a date?
Anyway, 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 excel is decimal days and unix is seconds (or something like that).
UnixDate = (A2 - 25569) * 86400.
25569 = Difference in days between 1900(Unix) and 1970(Excel)
86400 = Number of seconds in a day
- 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.
- 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.
- Apostrophes will cause you nightmares. They terminate strings early. Replace them with right apostrophes or quotes.
- Real HTML required. If you're like me and you've stored all your <HTML> as <HTML> in your ASP and .Net applications to get around Microsoft injection filters you'll have to undo all that silliness.