CiviCRM Community Forums (archive)

Hi,

An old issue but an important one where it'd be great for us to document a definitive answer. Importing sheets from Excel where there are European names can be a nightmare, and it really shouldn't be a difficult task needing any technical expertise.

Here's a step-by-step that works:

Here's the simplest and best solution/workaround I can find for now, and it's pretty simple: You create a macro button, copy & paste in the code below, then click on it. Step-by-step instructions are below if you don't know macros.

Good tip: create a blank Excel sheet with a macro button like this, save it somewhere easy for everyone to get at (e.g. on a CiviCRM sidebar) and ask staff to just copy and paste any list they want to import into this file, then hit the button. This method works for all UTF 8 languages - I tried it successfully with characters from Russian Cyrillic, Japanese, Polish and Arabic.

These instructions are now also on the wiki here: http://wiki.civicrm.org/confluence/display/CRMDOC33/Making+Microsoft+Excel+give+you+clean+data+to+import

Creating the macro (Excel 2007)
---------------------------
Making a macro for beginners: (macro code is at the end)
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) Right click on the shape, 'Assign Macro', then hit the 'New' button.
3) It'll come up with a code screen. Copy and paste the code below into the place it puts the cursor (between 'Sub ...' and 'End Sub'). That's it!
4) Close the code screen completely (you don't need to save), click off the shape to deselect it. Then click on it and it should just work!

You can copy and paste this macro button into any other excel sheet you need to export - just right-click on it and 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()' (e.g. it might be Sub SmileyFace3_Click() or Sub Rectangle1_Click() ). This is where it puts you by default.

The macro code to copy and paste:



Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant

FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName <> False Then
ListSep = Chr(44)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If

Set fsT = CreateObject("ADODB.Stream")
fsT.Type = 2
fsT.Charset = "utf-8"
fsT.Open

For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
If CurrCell.Value <> Empty Then
EscapedQuotes = Replace(CurrCell.Value, Chr(34), Chr(34) & Chr(34))
CurrTextStr = CurrTextStr & """" & EscapedQuotes & """" & ListSep
Else
CurrTextStr = CurrTextStr & ListSep
End If
Next
fsT.writetext CurrTextStr, 1
If (fsT.Position < fsT.EOS) Then
   fsT.SetEOS = fsT.Position
End If
Next
End If

fsT.SaveToFile FName, 2



Technical notes: This macro does 3 things:

• It surrounds every non-blank cell in quotes, after "" escaping any quotes already there, to avoid CiviCRM/PHP's problem of ignoring non-Ascii accented characters at the start of a name, see http://issues.civicrm.org/jira/browse/CRM-345) (adapted from http://www.mrexcel.com/forum/showthread.php?t=320531)
• It forces the CSV file to use commas regardless of settings, avoiding any continental issues of semi-colons being used to delimit the CSV file, see Xavier's comments below
• It formats as a CSV file, and saves it (in a round-about way) as UTF8 using the "ADODB.Stream" object (adapted from http://www.josephtbradley.com/wp/2007/10/17/saving-as-utf-8-with-vba-in-excel-and-wrapping-text-with-no-orphans/ )

Cheers, so just clarifying does this mean Civicrm requires "," seperated CSV at all times including when localized to France? If that's the case it looks like it'd be easy to force this macro to use commas regardless, off the top of my head it looks like changing

ListSep = Application.International(xlListSeparator)
to

ListSep = ","
should do it.

I just found phpexcel. They seem to be able to read directly.

Have you try it by any chance ? Shouldn't be too hard to add as a replacement of csv

Quick update - I fixed this to work properly for all UTF-8 characters (I've updated the code and the original post). It now lets you save real UTF 8 csv files in Excel directly, and it correctly exports all UTF8 characters I tested to the csv, including Japanese, Arabic etc.

Quick note - oddly, some of the utf8 characters from this were displaying as boxes in my text edito, even though they worked fine in test Civi imports and even displayed properly in ol' Notepad. Probably means the text editor needs some character support add-on.

Looks good - it'd be great to avoid having to explain how to add UTF-8 byte order markers when people are puzzled that the sheet they exported from Civi and opened in Excel has made a mess of all their European names!

Exactly what I was looking for. Can anyone help with a "Run-time error 1004" error message? Method "GetSaveAsFilename of object_Application" failed. The following section in the code is highlighted when debugging: "FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")"

I am using Office 2011 for Mac.

Thank you for any help!