Skip to main content

Using Drupal Feeds Module to Import Content from MS Access

Feeds 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.  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).

    Formula:
    UnixDate = (A2 - 25569) * 86400.

    Where:
    25569 = Difference in days between 1900(Unix) and 1970(Excel)
    86400 = Number of seconds in a day
    Source: webconsultingengineering.com
     
  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.
     
  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.

Comments

Brent said…
MS Access Conversions

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

Unix -> Access
DateAdd("s", [myUnixDate],
#1/1/1970#) As myAccessDate
Brent said…
Remember the times above are all seconds in GMT, so be sure your locale settings match the data if importing dates as text from another timezone before converting.

Popular posts from this blog

Simple HTTP Redirect with Querystring in IIS7

HTTP Redirect seems simple enough. Always was in IIS6 and in IIS7 there's even a button labeled HTTP Redirect that promises relative redirects.  It looks like it'll be as easy Apache finally.  That is until you try to redirect a querystring.  Then everything bombs.

Turns out it still is relatively easy, except you have to know that Microsoft changed $S$Q to $V$Q. Why? $Ss and $Gs I suspect.

And How.
In our example we'll redirect all pages under http://olddomain.com/content to http://mydomain.com/content.
Pick the virtual directory you want to redirect. e.g. http://olddomain.com/content Click HTTP Redirect under IIS in the IIS management console.In the HTTP Redirect Dialog:
Check Redirect requests to this destinationEnter your new path ending with $V$Q.  e.g. http://mydomain.com$V$QCounter-intuitively check Redirect all request to exact destination (instead of relative destination)Choose the appropriate Status Code (Permanent or Temporary)Apply Changes and Test

ArcGIS One-to-Many Labeling

ArcGIS is just plain lousy at dealing with any relationship that isn't one-to-one.

We all have a slew of hacks just to deal with this limitation. I for one regularly am creating temporary cross-tab queries so I can represent multiple sample results at a collection point, fish surveyed at a cross-section and a host of other relationships.

The classic example for mapping comes from the cadastral community and condominium lots. It's an odd situation where more than one person has title to the same piece of ground. How do you represent this?

I've got a new trick thanks to Mohammed Hoque's article in ArcUser Magazine.

We're going to do a database query inside a label expression, loop through the results and output the entire list to label.

For our example we'll use Outfitting Areas in Idaho and we'll label them with the Outfitters and Guide License Numbers and Outfitter Names.

1.) Open ArcGIS and add your spatial layer with the unique identifier shared with your d…

Serving up KML in IIS 6

To serve up KML in IIS 6, you have to add a few MIME Types. The easiest way to do this is to apply new MIME Type settings globally by changing the properties on your server's "Web Sites" folder in IIS.


Google Earth reads KML and KMZ files. The MIME type for KML files is

* application/vnd.google-earth.kml+xml

The MIME type for KMZ files is

* application/vnd.google-earth.kmz

Source: Google KML Tutorial


To add a MIME type to a Web site or directory

1. In IIS Manager, right-click the Web site or Web site directory for which you want to add a MIME type, and click Properties.

2. Click the HTTP Headers tab.

3. Click MIME Types.

4. Click New.

5. In the Extension box, type the file name extension.

6. In the MIME type box, type a valid MIME type. If you define a MIME type that has already been defined at a higher level, you are prompted to select the level where the MIME type should reside.

To create a MIME type for an undefined MIME type, type an asterisk (*) in the Extension box, an…