Required format of .csv (Comma Separated Values) file for use with ImportClientCSV:
1. File must be comma delimited format
2. Each field must be Quoted (surrounded by quotation characters) if the field contains a comma, a return, empty space or a quotation mark character
| • | It is recommended to quote every field to prevent errors |
3. Every line must contain 24 fields, any non used or empty fields must be present, i.e. "","",
4. The Name field which is the first field is the only required field
5. All the normal AyaNova rules for Client objects apply; duplicate clients will not be imported, records with no client name will not be imported, fields that exceed the length in AyaNova will be truncated.
6. The csv file must not contain field names in the first row or any header or footer, data only.
7. The csv file must contain the following 24 fields in following order:
Client Name, MailAddress, MailCity, MailStateProvince, MailCountry, MailPostal, DeliveryAddress, DeliveryCity, DeliveryStateProvince, DeliveryCountry, DeliveryPostal, Contact, Other Contacts, Business, Fax, Home, Mobile, Pager, Email, AccountNumber, WebSite, GeneralNotes, TechNotes, PopupNotes
Review these recommendations before importing:
| • | Use the export feature to export your current clients in AyaNova to a comma separate values file which you can then refer to and see the correct format even before creating your own for import. |
| • | Comma Separated Values files (.csv files) can be created and opened directly using Microsoft Access, this is handy if you wish to "massage" the data before importing or examine or work with an exported Clients.csv file |
| • | Backup your current AyaNova database before importing, there is no undo feature and it's easy to accidentally have a field in the wrong position as the import utility has no way of confirming if a phone number (for example) is in the email address column. |
| • | When you open the csv file before importing, carefully examine the grid of records displayed to ensure each field is in the correct column before importing. |
| • | For better performance when importing a large number of clients, do the import from the fastest connection to the database possible, i.e. don't import clients via a dataportal unless performance is not a concern. |
| • | Duplicate clients will not be imported, the client name is examined before each client is imported and if the client name is found to be in AyaNova already then that record is not imported. This means you can re-import the same data safely without getting duplicate records. |
| • | After importing open AyaNova and examine the imported records and ensure fields were imported to the correct area of the client record. |
| • | Ensure that the CSV file you will be importing is located in the same AyaNova program folder as the ImportClientCSV.exe file – otherwise you may receive errors relating to connecting to the database. |
| • | NOTE: This import utility uses the comma's in the CSV file to determine what is the next field. If a field itself has comma's in it (such as a Street field for example), there must be quotation marks around the entire field. |
| • | NOTE: In some cases, when you use MS Excel to edit or create a CSV file, the import utility may not understand Excel's use of quotes and comma's. If you get errors, it is suggested to to import it into a table in Microsoft Access. Right click on the table in MS Access, select Export…, select CSV from the drop down list and the CSV file will be created correctly with quotes and commas. If you do not have MS Access, send the existing CSV file zipped using WinZip to AyaNova Support, along with the issues you are experiencing, asking us to import into MS Access for you and create the formatted CSV file. |
Steps to import:
1. Create the csv file that contains the clients information that you wish to import into AyaNova
2. Create a backup of the AyaNova database file before continuing.
| • | Refer to the Backup & Recovery section in this Help file for information on backing up the database for your configuration. |
3. All other AyaNova related programs must be exited by everyone.
4. It is recommended that you perform the import on the same computer where the database resides, and where the csv file is.
5. From Start -> Programs -> AyaNova -> Utilities -> select the Client Import Export to open the program
6. Enter an AyaNova Administrator username and password
| • | (default is “manager” with password “letmein” if you have not yet changed it as recommended) |
7. Note the messages that occur
8. Select the menu option Open CSV File
<image>
9. Move to the location of the csv file, select the file, select Open
10. The ImportClientCSV utility will read the file and display a message identifying the number if records it finds, and a reminder to confirm the fields and check data before continuing
| • | Note – data can not be edited in the ImportClientCSV utility. It is for viewing only before importing. |
11. The data will now display with a header for each column so that you can confirm data is in the correct field
12. Once you have confirmed the data, select the menu option Import Data
13. Data will be imported into AyaNova
| • | The time it will take will depend on the number of records to import |
| • | Duplicate clients will not be imported based on the client name. |
14. When the import is finished, a message will display identifying the number of client records imported. Select OK to close.
15. Exit out of the ImportClientCSV utility by selecting the X in the upper right-hand corner
16. After exiting, log into AyaNova and confirm client records imported and data imported to correct fields