If you have a large amount of data to export, writing to comma-separated values (CSV) format using Data Transfer, and then reading the result into Excel is faster than writing directly to Excel format. However, there are some issues with date formatting and localization that you might encounter when working with CSV files in Excel. To avoid these issues, it is recommended to export the data to Excel directly, rather than to CSV format. Or, you could export the data to CSV format and then work with it in a text editor. If exporting to CSV and then working with the file in Excel is your preferred method, the following sections describe ways to avoid these date and localization issues.
If you export data from ARCHIBUS into a CSV file and then save the file in Excel, Excel saves dates in the CSV file using the format of your regional setting. When you export to CSV, a cell's definition is not saved with the CSV file. When Excel opens the file, it uses the "General" cell format to auto-detect the cell type, and uses the default format defined in Excel. When working with date fields, for example, if Excel's default format conflicts with the default core format of year-month-day (yyyy-mm-dd), you will get an error when trying to import back into ARCHIBUS a CSV file that you have worked on in Excel.
If you need to work with CSV files in Excel, the following is a workaround to preserve the date formatting.
To import a CSV file opened in Excel back into ARCHIBUS:
Excel displays a standard Open dialog box.
- Rename the file so that it has a .TXT extension.
- Use Excel to open the file.
- Excel starts up the Text Import Wizard.
When you export data to a CSV file, if you work with the file in Excel using some European operating systems, Excel saves the file with semicolons instead of comas separating the data. To import the file back into ARCHIBUS, you will need to use the following procedure to change the delimiter back to comma.
To change the delimiter to comma:
The Excel Text Import Wizard appears.
You are now able to import the file back into ARCHIBUS.