Skip to end of metadata
Go to start of metadata

Microsoft Excel has a weakness in not 'playing nicely' when exporting to other applications, which can mess up your CiviCRM imports. Full instructions are given here on how to avoid this happening. The steps are simple: it essentially boils down to, 1) create a shape, 2) two clicks to create a macro, 3) copy and paste some code, 4) click on the shape.

Some ways Excel can make a mess of your import

Without these steps, a number of frustrating problems can appear:

  • Some non-English characters such as European accent marks will turn into question marks, and many others will lose their accent (e.g. "Tőkés" becomes "T?kes") (cause: no UTF8 support)
  • Names beginning with an accented character will lose their first character when imported into CiviCRM (e.g. a sheet from Excel would import "Ždanoka" as "danoka") (cause: no option to enclose fields in quotes)
  • Errors, strange results and data appearing in the wrong place can happen where there are blanks in the last row (cause: unconventional line ending)
  • Sheets might not import at all in some international versions of Excel, e.g. French (cause: using semicolons or tabs instead of commas as delimiters)

To stop this happening, these steps show you how to make an Excel macro that you can use instead of 'Save as' to save a more conventional CSV file of the kind that CiviCRM expects. 

(for the techies: it creates a UTF-8 CSV file with quotes escaped, fields enclosed in double quotes, commas forced as a delimiter regardless of locale and a blank column at the end - but you don't need to know that!)

Creating the macro for beginners (Excel 2007)

1) Create a shape for the button. Go to 'Insert' tab, 'Shape', pick any shape, put it anywhere where you can click on it.
2) Assign a macro. Right click on the shape, 'Assign Macro', then hit the 'New' button.
3) Paste in the code. It'll come up with a code screen. Copy and paste the code below into the place where it puts the cursor (between 'Sub ...' and 'End Sub'). 
4) That's it! Close the code screen completely (you don't need to save), click off the shape to deselect it. Then click on the shape, and it should just work - popping up a 'save as' screen.

You can copy and paste this macro button into any other excel sheet you need to export: just right-click on the shape to select it, then copy and paste as normal. If you need to move it around, first right-click on it to select it without triggering the macro, then you can drag it about as normal.

It might not work on older versions of Excel. If the code gives an error, make sure you copied everything and no other non-code text, and make sure you're pasting the code before 'End Sub' and after 'Sub SomeShapeX_Click()' (for example it might be Sub SmileyFace3_Click() or Sub Rectangle1_Click() depending on the shape you chose).

The macro code to copy and paste:

From forum discussion http://forum.civicrm.org/index.php/topic,16442.0.html

Labels