Adress-Import (Excel): Unterschied zwischen den Versionen
(→Adressen-Import (Excel)) |
|||
Zeile 1: | Zeile 1: | ||
− | == | + | == 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. | 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 | + | 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. | 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. | ||
Zeile 25: | Zeile 25: | ||
− | === | + | === Data-Import Step 1 === |
{{Screenshot|Excelimport1.png|608|Overview of the column titles useable in the import and the selection of the import file.}} | {{Screenshot|Excelimport1.png|608|Overview of the column titles useable in the import and the selection of the import file.}} | ||
Zeile 31: | Zeile 31: | ||
{{Hinweis | {{Hinweis | ||
− | |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. | + | |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. | |
− | === | + | === Data-Import Step 2 === |
− | + | WICE now shows you, after having uploaded an 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." | |
− | |||
− | {{Screenshot|Excelimport2.png|545| | + | {{Screenshot|Excelimport2.png|545|View of the sheets contained in your import file}} |
− | === | + | === Data-Import Step 3 === |
− | WICE | + | WICE now analyzes your Import-file and shows you the column titles that have been recognized: |
− | {{Screenshot|Excelimport3oben.png|694| | + | {{Screenshot|Excelimport3oben.png|694|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. | |
{{Hinweis | {{Hinweis | ||
− | | | + | |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 | + | 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. |
− | {{Screenshot|Excelimport3unten.png|755| | + | {{Screenshot|Excelimport3unten.png|755|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. | ||
{{Tipp | {{Tipp | ||
− | | | + | |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.}} |
− | === | + | === Data-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. | |
[[Bild:Excelimport4.png]] | [[Bild:Excelimport4.png]] | ||
− | Anhand der Bildschirmausgabe können Sie für die einzelnen Zeilen prüfen wie der Import diese erfasst bzw. bearbeitet hat. Wenn es sich um eine Dublette handelt, so wird dies in der Zeile mit dem Vermerk "already exists" gekennzeichnet. Wenn nur ein weiterer Ansprechpartner erfasst wird, zu einer bereits vorhandenden Adresse, so wird dies auch durch den Vermerk "inserting contact person..." in der jeweiligen Zeile ausgegeben. | + | According to the importlog shown on the screen you can check for every single line how the import has handled these. Anhand der Bildschirmausgabe können Sie für die einzelnen Zeilen prüfen wie der Import diese erfasst bzw. bearbeitet hat. Wenn es sich um eine Dublette handelt, so wird dies in der Zeile mit dem Vermerk "already exists" gekennzeichnet. Wenn nur ein weiterer Ansprechpartner erfasst wird, zu einer bereits vorhandenden Adresse, so wird dies auch durch den Vermerk "inserting contact person..." in der jeweiligen Zeile ausgegeben. |
Sollten alle Ihre Zeilen als Dubletten erkannt worden sein, so werden natürlich auch keine neuen Einträge für diese Dubletten in der Adressdatenbank erzeugt! | Sollten alle Ihre Zeilen als Dubletten erkannt worden sein, so werden natürlich auch keine neuen Einträge für diese Dubletten in der Adressdatenbank erzeugt! | ||
{{Tipp|Der Importlog kann später nicht erneut aufgerufen werden - sollten Sie erneut auf diesen zugreifen wollen, dann markieren Sie die Auflistung und sichern Sie diese per Copy & Paste in einem Texteditor!}} | {{Tipp|Der Importlog kann später nicht erneut aufgerufen werden - sollten Sie erneut auf diesen zugreifen wollen, dann markieren Sie die Auflistung und sichern Sie diese per Copy & Paste in einem Texteditor!}} |
Version vom 13. September 2012, 15:42 Uhr
Inhaltsverzeichnis
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!
Wichtig: The column title for the company name "address_company.name" in your Excel-file is essential 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.
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.
Wichtig: 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. |
Data-Import Step 1
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 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.
Hinweis: 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.
Data-Import Step 2
WICE now shows you, after having uploaded an 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."
Data-Import Step 3
WICE now analyzes your Import-file and shows you the column titles that have been recognized:
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.
Hinweis: 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.
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.
Tipp: 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. |
Data-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. Anhand der Bildschirmausgabe können Sie für die einzelnen Zeilen prüfen wie der Import diese erfasst bzw. bearbeitet hat. Wenn es sich um eine Dublette handelt, so wird dies in der Zeile mit dem Vermerk "already exists" gekennzeichnet. Wenn nur ein weiterer Ansprechpartner erfasst wird, zu einer bereits vorhandenden Adresse, so wird dies auch durch den Vermerk "inserting contact person..." in der jeweiligen Zeile ausgegeben.
Sollten alle Ihre Zeilen als Dubletten erkannt worden sein, so werden natürlich auch keine neuen Einträge für diese Dubletten in der Adressdatenbank erzeugt!
Tipp: Der Importlog kann später nicht erneut aufgerufen werden - sollten Sie erneut auf diesen zugreifen wollen, dann markieren Sie die Auflistung und sichern Sie diese per Copy & Paste in einem Texteditor! |
Wichtig: Sie erhalten nach dem Abschluß des Imports, also nachdem Ihnen die Nummer der letzten Zeile der in Ihrer Importdatei enthaltenen Daten angezeigt wird, keine gesonderte Meldung von WICE. Daher prüfen Sie vorab im Step 2 bzw. in der von Ihnen verwendeten Excel-Datei die Gesamtanzahl der zu importierenden Zeilen. Wenn die Gesamtanzahl erreicht wird, können Sie die Importmaske verlassen bzw. beenden und im Modul "Adressen" prüfen ob die Daten korrekt importiert wurden. |
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:
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:
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.country | 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.name | address_company.number_of_employee |
Erklärung | Branchen-Kategorie1 | Branchen-Kategorie2 | Firmenname | Anzahl der Mitarbeiter |
Feldname | address_company.p_o_box | address_company.phone | 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 | 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.email | address_contactperson.fax | address_contactperson.firstname | |
Erklärung | Ansprechpartner Geburtsdatum | Ansprechpartner E-Mail | Ansprechpartner Fax | Ansprechpartner Vorname | |
Feldname | address_contactperson.mobile_phone | address_contactperson.name | address_contactperson.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 |