Special Considerations when Working with CSV Files in Excel

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.

How Excel Handles Dates in CSV Files

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:

  1. Use the Text Import Wizard to import the file into Excel using one of the following methods:
    • First method:
      1. In Excel, select Data tab>Get External Data>From Text.

        Excel displays a standard Open dialog box.

      2. Use the controls in the dialog box to select the CSV file you would like to work with, and then click Open.
      3. Excel starts up the Text Import Wizard.
    • Alternative method:
    1. Rename the file so that it has a TXT extension.
    2. Use Excel to open the file.
    3. Excel starts up the Text Import Wizard.
  2. Go through the steps of the Text Import Wizard. In Step 3, you can specify how Excel should interpret the data that it imports from the CSV file. By indicating that the date fields are Text, you prevent Excel from saving the dates using the format associated with your locale, which would throw an error when you try to re-import the file. Specifying the dates as Text preserves the date in the default core format of yyyy-mm-dd.
  3. When you are done with the steps of the wizard, click Finish. Your data is imported in accordance with your specifications.

Working with CSV Files on Some European Operating Systems

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:

  1. Open Excel.
  2. Go to the Open menu and locate and select the CSV file.

    The Excel Text Import Wizard appears.

  3. On the Step 1 screen, under Choose the file type that best describes your data, make sure Delimiter is selected.
  4. On the Step 2 screen, under Delimiters, select Comma, click Next, and then click Finish.

    You are now able to import the file back into Archibus.