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.
Without these steps, a number of frustrating problems can appear:
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!)
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).
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
From forum discussion http://forum.civicrm.org/index.php/topic,16442.0.html