When you want to import external data from an incompatible third-party system into the Project level Budget tool (see Import a Budget CSV), its a common practice to use the Microsoft Excel program to edit the Import Budget CSV file. When performing this step, it is also common for users to report that the Microsoft Excel program has reformatted your numeric cost codes as dates. While this is unexpected behaviour, it can also be challenging to correct.
One of the most common uses for a CSV file is to transfer data from one project group to another. Because the data that needs to be transferred between these systems is incompatible, a CSV is the often the only and/or most efficient means for completing a data transfer. In the case of the Import Budget CSV file (as well with other Procore Import Templates), your data must be always be properly formatted before an import is successful.
Microsoft's Support website offers a number of online resources that may be of assistance with the specific issue you may be encountering:
Note: Some versions of Microsoft Excel 2016 (e.g., Office 365) have a different menu option that what is described in the Answer below. To learn more, see How do I restore the legacy Get External Data experience? on the Microsoft Office Support Site.
From an end user perspective, a CSV file appears to look and function like any other spreadsheet file (i.e., XLS, XLSX and so on) while you are viewing it in Microsoft Excel. However, the data stored in a CSV (i.e., the values in the headers, columns and rows) is actually encoded in a text format. To see what a CSV looks like in its raw form, simply open it with your computer's text editor (i.e., Notes, Text/Edit or Notepad). Below is an example.
Because Microsoft Excel is compatible with the CSV format, it is an accepted and common practice to input data from one system into an Excel spreadsheet in order to import it into another system. While using a spreadsheet program is the easiest method for end users to collect and compile data when preparing for an import, it is not without its drawbacks--particularly if you are less familiar with how Microsoft Excel works behind the scenes in order to display your data in its user-friendly format.
Typically, number-to-date reformatting occurs at the time you open a CSV file with Microsoft Excel. Because the project group must look at text data encoded in a wide variety of formats, Microsoft Excel is designed to automatically apply the 'General' cell format to the data in the spreadsheet (Note: If you want to see the variety of formats that can be applied to a cell in Microsoft Excel, choose Format > Cells. Then review the options in the Format Cells dialogue box). During auto-detection, the project group looks at the encoded cell values and then automatically decides whether the value in that cell should be formatted as date, fraction, currency or text and so on.
Below is an example of a spreadsheet where the first three cost codes have been unexpectedly formatted as a date, instead of a number.
Since you do NOT want to import these improperly formatted values into Procore (i.e., the import process will fail or the import will result in undesired values should you attempt to do so), you must carefully reformat your CSV file. This can take some time. To help you navigate this process, please start by following the steps in the Answer below.
To address the common issue of reformatting a CSV file, please open the CSV file using the recommended steps below. This helpful tip resolves many of the formatting CSV issues reported by end users. However, if you continue to experience an issue after using this method, please contact: support@procore.com.
IMPORTANT! In order to protect the integrity of your company’s data, Procore Employees are restricted from modifying the data that clients submit in all Procore Import Templates. This restriction applies to all data modifications, including correcting typographical errors. If Procore determines that errors are present in any Procore Import Template that you submit to Procore, it will be returned to you for correction. Please note that the import process may take up to 72 hours to process.