Adress-Import (Excel)

Aus WikiWICE
Version vom 13. September 2012, 15:36 Uhr von Sgatzke (Diskussion | Beiträge) (Excel file import Step 2)
Wechseln zu: Navigation, Suche

Address-Import (Excel)

For the best and most comfortable option to import large quantities of adresses and notes to WICE we recommend using the Excel-Import feature. You can access the import via the Administration of WICE->Database by clicking on Address import (XLS).

To identify your adresses by WICE during the import process you need to add the matching titles to your columns of your Excel sheet, according to their usage in the WICE database.

Please note that your Excel-File can not be password protected in order to use it with the Excel-Import!


The column title for the company name "" in your Excel-file is mandatory to proceed with your import!

You will have to add the matching column title in the first line of your Excel sheet. All of the supported column titles are listed here at the end of this chapter with a brief explantation or listed on the page of the Excel-Import - Step 1. Please also read carefully the remarks on the start screen of the import because these contain more useful hints as for example how to use the same colummn titles repeatedly to merge the contents of these columns in WICE or how contents of custom fields for addresses and contact persons need to imported into WICE.

Before you can start your import your need to prepare your Excel sheet with your addresses according to these prerequisites, so that you can import them into WICE.

Eng excelimport1.png
Screenshot: Example of an Excel sheet with the correct column titles to be imported into WICE

Here you see an example of an Excel sheet ready to be imported by the WICE Excel-import. Of course a sheet can contain more fields. It is most important that the column titels are in the correct format so that WICE can match them. We will send you an Excel-file as an example with all possible column titles, so that you are able to copy these into your sheet and avoid any typos. WICE will only be able to identify these titles if the spelling is identical - it will also list during the import process all the column tites found in your sheet.

If you have more than one contact person for a company address and you want to import them, you will have to add for every contact person another line in your sheet with the same fields for the company address including the personal information for your contact person, so that WICE can add this contact person to the already exisiting company address. It is not possible to import more than one contact person in the same line for the same company address. WICE identifies in the import process that a company address already exists and just adds the new contact person to this address.


While importing addresses these will be assigned to the owner group that the user importing these addresses has selected in it`s Profile at the field for the "Address input default groups"! If nothing has been selected here, all the addresses will be imported without an owner group, which means that these can be accessed by every other user. If you want to make certain addresses only avaiable for some users then you might have to split your addresses into multiple imports and change the owner groups into the Profile before each single import.

Excel file import Step 1

Eng excelimport2.png
Screenshot: Overview of the column titles useable in the import and the selection of the import file.

Please select now your file that you wish to import containing your address data. The file needs to be available on you local hard drive or network. Please klick on "Search" to select you file. Before you can continue you need to confirm the Checkbox "I agree with the hints printed below". Now you need to click on the button "Transfer Excel file" and the fiel will be uploaded to the WICE server. Depending on your file size this can take a few minutes.


If you want to import notes to be addes to an address, the date format in your import column "note.holdfile_date" of your Excel sheet needs to be in the format YYYY-MM-DD. The field "note.eomployee_assigned" needs to contain the database-ID (employee.rowid) of the employee to whom the holdfile needs to be assigned. So that Excel will accept this format it might be necassary that the column format needs to set to "Text".

Within the next step WICE will let you now how many sheets your file contains.

Excel file import Step 2

WICE now shows you, after having uploaded and analysed the file, how many different sheets it contains. If there are more then one sheet in your file you will have to select which sheet you want to import and it is also shown how many lines each sheet contains. Please select the sheet to be imported an click on "Proceed."

Eng excelimport3.png
Screenshot: View of the sheets contained in your import file

Excel file import Step 3

WICE now analyzes your Import-file and shows you the column titles that have been recognized:

Eng excelimport4.png
Screenshot: List of column titles found in your Import sheet

Multiple identical column titles in your import sheet can be merged and optionally specifying a divider. It is not necesary to include all the supported column titles in your import sheet. But it is mandatory(!) that the first company address category (address_company.address_catrgory1) is present and filled with content.


If you want to assign an address to multiple address categories, e.g. an address in address company category 1 needs to be "Customer" and "Partner" you will need to add separate columns in your import sheet for each category entry that you wish to assign.

WICE performs a duplicate check which each import. Hereby additional values can be added to an address or values can be overwritten with informationns from the import sheet.

Eng excelimport5.png
Screenshot: Select here the desired settings for the duplicate check and the number of lines to be imported.

You can select through the corresponding options wether the address fiels should be considered in the duplicate check if there values are empty or if existing data from duplicates should be overwritten. By selecting these option values of found duplicate addresses in your system will be overwritten by the values in your import file.

Next you can select the fields to be included in the duplicate check. Therefore you will have to click on the fields listed in "Company duplicate criteria" and in "Contact person duplicate criteria." The selections in these fields are linked as "and"-combinantions which means that each single criteria needs to match exactly to be defined as a duplicate.

Furthermore you can decide how many lines of your import sheet should be imported. For this purpose you need to fill in the number of lines in the field "Stop import after x lines." By using this option is it possible to limit the number of lines for validating and testing of your import sheet. If you want all lines to be imported just leave the field blank or insert a "0".

Now click on "Start import" to start the import.

Smile orange.png

Please test larger import files and your settings first by uploading just a few lines for your sheet by using the option "Stop import after x lines" to make sure that the data is consistent.

Excel file import Step 4

Now your import will be carried out. On the screen you see a list of every single line that is imported. If no more new lines are added, the import is finished or if the number of lines imported matches the total number of lines of your import sheet.


According to the importlog shown on the screen you can check for every single line how the import has handled these. If an address was detected as a duplicate you will see the remark "already exists" for this line. If now a new contact person was added the remark "inserting contact person..." is added. In case that all the addresses in your import file have been detected as duplicates of course no new adresses will be added to you system.

Smile orange.png

The information displayed in the importlog can`t be again accessed later on - therefore we recommend to save the contents of this screen via copy & paste e.g. in a text editor if necessary.


Please note that there is no message shown if the import is finished when the last line of your import sheet is shown in the importlog. So we suggest to check and remember in Step 2 of the Excel import of how many lines your import sheet consists. If the the import log reaches this number the import is finished and you can close this screen to check in the Addresse module if your import was done correctly.

Adressen-Import rückgängig machen

Um einen vorherigen Import rückgängig zu machen, rufen Sie in der Administration den Adressen-Import(XLS). Im unteren Bereich diese Maske sehen Sie die gewünschte Funktion:

Import reverse.png
Screenshot: Funktion um einen Excel-Adressimport rückgängig zu machen

Bitte beachten Sie die entsprechenden Hinweise: Sie können einen Import nur rückgängig machen, wenn die Option "Stammdaten von Dubletten aktualisieren" nicht ausgewählt wurde, und wenn der Import innerhalb der letzten 24 Stunden durchgeführt wurde!

Klicken Sie nun auf den Button "Vorigen Import rückgängig machen" und Sie gelangen in die folgende Ansicht, wenn nicht einer der obigen Punkte zutrifft:

Import reverse2.png
Screenshot: Maske zur Auswahl um einen gewählten Import rückgänig zu machen

Wählen Sie nun aus dem Drop-Down Menü den gewünschten Import aus den Sie rückgängig machen möchten und aktivieren Sie die Checkbox mit der Sicherheitsbestätigung und klicken Sie dann auf "Ausgewählten Import rückgängig machen". Es werden nun alle Daten aus dem Import unwiederbringlich gelöscht. Beachten Sie dazu auf jeden Fall auch den Hinweistext in der Maske! Wenn Sie einen weiteren Import rückgängig machen möchten, so führen Sie die Schritte erneut durch.

Erklärung der Felder für den Import

Im Prinzip sind die Felder für den Excel-Import relativ selbsterklärend. Wir haben hier für Sie alle Felder mit weiteren Erklärungen aufgeführt. Im Sinne einer besseren Übersicht haben wir dies unterteilt. In der Excel-Tabelle muss dies alles in einer Zeile stehen. Sie müssen aber nicht alle Felder verwenden.

Feldname address_company.address_category1 address_company.address_category2 address_company.address_category3 address_company.address_category4
Erklärung Firmenadresskategorie 1 Firmenadresskategorie 2 Firmenadresskategorie 3 Firmenadresskategorie 4
Feldname address_company.customer_number address_company.delivery_address address_company.fax
Erklärung Land Kundennummer (wird sonst automatisch vergeben) Lieferanschrift (nicht mehr unterstützt) Firmenfaxnummer
Feldname address_company.line_category1 address_company.line_category2 address_company.number_of_employee
Erklärung Branchen-Kategorie1 Branchen-Kategorie2 Firmenname Anzahl der Mitarbeiter
Feldname address_company.p_o_box address_company.remarks address_company.state
Erklärung Firma Postfach Firma Telefonnummer Firma Bemerkungen Firma Bundesland
Feldname address_company.state_pobox address_company.street address_company.street_number address_company.suburb
Erklärung Firma Bundesland Postfach Firma Straße Firma Hausnummer Firma Ortsteil
Feldname address_company.town_area address_company.town_pobox address_company.turnover
Erklärung Firma Ort Firma Ortsteil (engl. Adresse) Firma Ort Postfach Firma Umsatz
Feldname address_company.url address_company.zip_code address_company.zip_code_pobox
Erklärung Firma Webadresse Firma PLZ Firma PLZ Postfach

Die Felder für die Ansprechpartner lauten:

Feldname address_contactperson.address_category1 address_contactperson.address_category2 address_contactperson.address_category3 address_contactperson.address_category4
Erklärung Ansprechpartner Adresskategorie 1 Ansprechpartner Adresskategorie 2 Ansprechpartner Adresskategorie 3 Ansprechpartner Adresskategorie 4
Feldname address_contactperson.birthday address_contactperson.fax address_contactperson.firstname
Erklärung Ansprechpartner Geburtsdatum Ansprechpartner E-Mail Ansprechpartner Fax Ansprechpartner Vorname
Feldname address_contactperson.mobile_phone address_contactperson.phone2
Erklärung Ansprechpartner Handynummer Ansprechpartner Nachname Ansprechpartner Telefon Ansprechpartner Telefon 2
Feldname address_contactperson.phone3 address_contactperson.position address_contactperson.private_street address_contactperson.private_town
Erklärung Ansprechpartner Telefon 3 Ansprechpartner Visitenkarteninfo Ansprechpartner Privatanschrift Straße Ansprechpartner Privatanschrift Stadt
Feldname address_contactperson.private_zip_code address_contactperson.remarks address_contactperson.salutation address_contactperson.serial_salutation
Erklärung Ansprechpartner Privatanschrift PLZ Ansprechpartner Bemerkungen Ansprechpartner Anrede Ansprechpartner Serienbriefanrede
Feldname address_contactperson.title
Erklärung Ansprechpartner Titel

Eine Erklärung der Zusatzfelder und der Felder zum Import von Notizen finden Sie hier:

Feldname custom.* note.contactperson note.employee_assigned
Erklärung Zusatzfeld Notiz Ansprechpartner Notiz Mitarbeiter
Feldname note.holdfile_date note.holdfile_time note.medium note.priority
Erklärung Notiz Wiedervorlage Datum Notiz Zeit Notiz Aktionsmedium Notiz Priorität
Feldname note.task_done note.text note.time_exposure
Erklärung Notiz Wiedervorlage erledigt (0/1) Notiz Text Notiz Bearbeitungszeit