(Re-)Import of CSV files may not update or update incorrectly due to commas (,) in field values and/or ACCOUNTNO which are used by default as delimiters and separators in CSV files

Version 1

    Details

    (Re-)Import of CSV files may not update or update incorrectly due to commas (,) in field values and/or ACCOUNTNO which are used by default as delimiters and separators in CSV files.

    [SCENARIO]
    Users may have additional info they wish to have in GoldMine. Instead of  inputting manually the info directly into GoldMine, they export the records with the ACCOUNTNO into a .CSV  file, open the export file with Microsoft Excel, change/add the desired updates, save the changes in the Microsoft Excel file and then import back into  GoldMine using ACCOUNTNO as the match field.  

    [STEPS TO PREPARE]

    1.  Create 4 new contact records where  contact has a comma in first three characters, for example:  
    1,2 Test
    3,4 Test
    5,6 Test
    7 Test
    2.  Select File > Import and Export > Export Contact Records to open Export Wizard window
    3.  Select Export to a new file, CSV file
    4.  Select ALL Contact Records! and check Primary
    5.  From GoldMine Fields list Add Fields Accountno and Contact



    6.  Browse or enter a desired to desired path, and enter file name as export.csv.
    7.  No need to save as export profile > Finish
    8.  Open the file with Microsoft Excel, make the desired changes



    9. Save the file again
    10. select Yes to keep the current format. 

    [ADDITIONAL NOTE]
    During the step 9 the initial Field delimiters " are deleted so that only values with a , are now surrounded by ".

    [STEPS TO REPRODUCE]
    1. File > Import and Export > Import Contact Records
    2. Choose your import file the CSV file from steps to prepare
    3. Options Button

    3.     4. Remove “ (necessary due to Microsoft Excel removing the ")


    5.    5. OK > click Next (Bottom)

    6.    6. Choose correct mapping including label1 <=> ACCOUNTNO

    7. Profile Options

    7.   
      
    8. Choose reference field (AccountNo)

    8.      9. Tick the ignore first record in import file




    10. Overwrite the existing GoldMine record
    11. Finish the wizard
    12. Verify for one of the records with a comma if the record was updated

    [RESULT]

    - The records with the comma (1,2 test, 3,4 test and 5,6 test) in the ACCOUNTNO were not updated as GoldMine does not recognize  the matching account as the , (comma) cuts off the accountno for exampleB6070666447$N^#5 1,2 (real ACCOUNTNO)  <>  B6070666447$N^#5 1 (Import file interpretation)
    - Only the record with no comma in the ACCOUNTNO was updated correctly (7 Test)



     


    Resolution

    [CAUSE]
    - The  reason for this behavior is the , (comma) in the ACCOUNTNO column as this  is also set as the separator for the field values (by default from  Excel or in CSV files)
    - Currently GoldMine there is no way to prevent ACCOUNTNO to be generated without a comma
    - This is also logged as RM # 246393

    [WORKAROUND]
    - There might be other ways to  work around this for example in older versions of Microsoft Excel (up to  Microsoft Excel 2007) the file can be saved instead as a DBF file, this should be also possible with OpenOffice (please keep in mind that we cannot assist in any  conversion attempt). If the file will be available in DBF format  the import will work as expected as here no delimiters and separators  are necessary.

    - As newer Microsoft Excel versions do not have the  capability anymore to save as DBF files the current workaround require some preparation, but should be easy and might be  adapted quickly by a user.

    - Please keep in mind that it is strongly recommended to have a backup file of the import CSV file prior modification
    -  Please keep in mind that it is strongly recommended to have a full  running backup of the GoldMine database prior any import attempt

    1. Open the CSV file in Microsoft Excel (Important to do this in Excel as the comma separators / delimiters are not 'displayed')
    2. CTRL+F  (Find) and search for a CSV delimiter/separator accepted character -  see recommended list below >> It is important to identify a  character which is NOT available in the  whole sheet.

    - Recommended as usually not many times used, if no  character can be identified for the whole file it might be necessary in  some cases to split the Excel sheet into 2 or several files and use  different characters

    ALT+36 = $
    ALT+58 = :
    ALT+60 = <
    ALT+62 = >
    ALT+63 = ?
    ALT+91 = [
    ALT+93 = ]
    ALT+94 = ^
    ALT+126 = ~




    3.  CTRL+F (Find) and search for a unusual character you do not expect in  your data anywhere (this might be a little bit tricky but many times  things like (ALT+132 = ä or ALT+148 = ö or ALT+129 = ü should not appear  very often in English data). It is important to identify a  character which is NOT available in the whole sheet.



    4. CTRL+H (Replace) and enter in Find what , and Replace with this identified not yet existing character (here ä). Replace All



    5. Save the file again as CSV with the replaced comma values
    6.  Open the file with a notepad application - this will make the , (comma)  delimiters/separators visible and due to the replacement in step 4.  these are the only available , commas available.
    7. Replace all ,  (commas) with an accepted and available CSV delimiter as above in this example the ~ character



    8.  Replace all ä (the character which was used in step 4.) with a , comma  again (so now , are ONLY available in values and not anymore as  delimiters/separators)



    10. Save the file again as CSV file
    11. Try to re-import now with the matching option but select as separator the correct delimiter/separator - here ~



    [RESULT]
    - This time all records as expected were updated (this time the update field was mapped to EXT1