Importing content with Node Import and Excel on the Mac

The Node Import module is an easy way to import content from a variety of sources like databases, excel files, or even RSS feeds.  It's important when using this files, in my opinion, to convert/export this data to an intermediary step of a CSV file.  I've even written PHP code to convert custom data into a CSV file.

Why use a CSV file?  Well, it's easy to then massage the data in Excel.  For example, if I want to create a new column that is a combination of both the cell in column A and the cell in column B (say, cells A1 and B1), then I would write an simple command in a new column to handle this for me:

=CONCATENATE(A1, " (", B1, ")" )

Which would look like:

A1 (B1)

So if I had Elephant in A1 and Mammal in B1, my new column would read: "Elephant (Mammal)"

A problem arises when you save this new file as a CSV and import it using Node Import.  Assuming you use commas (tab delimited is also a favorite), Node Import is going to look for new line characters to separate entries.  In Windows, this is no problem.  On the Mac, Node Import does not recognize the newline characters from Excel.  In fact, it is looking for the Windows specific newline characters /r/n and will not work with /r or /n by themselves.

So once you massage your data in Excel, open up your CSV file in your code editor of choice and change the new line.  Panic's Coda does this automatically by going to Text, Line Endings, Convert to Windows Line Endings.

With your massaged, Windows line ending formatted CSV, you're ready to use Node Import.

Also, worth checking out for a frequently importing content is the Node Import via Cron module.