How do I preserve cost code formatting in Excel?

Why does my data need to be saved in the CSV format?

One of the most common uses for a CSV file is to transfer data from one program 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. 

Where can I learn more about Microsoft Excel and number formatting?

Microsoft's Support website offers a number of online resources that may be of assistance with the specific issue you may be encountering:

• Stop Automatically Changing Numbers to Dates
• Keep Leading Zeros and Large Numbers
• How to Control and Understand the Settings in the Format Settings Dialog Box
• Excel Formatting and Features that are Not Transferred to Other File Formats

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.   

Why is Microsoft Excel reformatting my cost codes as dates?

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 program 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 dialog box). During auto-detection, the program 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. 

1. Download the Import Budget CSV file and then update it. For details, see Import a Budget CSV.
   Important! Do NOT open the file directly in Microsoft Excel. Instead, you will launch the application using the information described in the next step. Once the program is open, you will use the steps below to get the data from the CSV from within Microsoft Excel. 
2. Launch the Microsoft Excel program:
   1. If you are using a Windows computer, double-click the Microsoft Excel icon on your desktop or click Start > Programs > Microsoft Excel (Note: Depending on the OS version you are using and where Microsoft Excel is installed, the path you use may be different). 
      OR
   2. If you are using a Macintosh computer, click the in the Microsoft Excel icon in your computer's Dock or do a Spotlight search for Microsoft Excel. Then press ENTER (Note: The specific steps you use to start Excel on your computer may be different). 
3. In Microsoft Excel, do the following:
   1. Open a new blank workbook. Typically, you will choose File > New Blank Workbook. 
   2. Click the Data tab. Then click From Text.
      
      
      
      Note: On a Windows computer, this button is typically located in the Get External Data group as shown below.
      
      
      
      Note: If you are using Microsoft Excel 2016 for Office 365, this button on the Get External data group is NOT visible because of a new Get & Transform experience developed by Microsoft. Please follow the steps in this article to restore this legacy feature. See How do I restore the legacy Get External Data experience? on the Microsoft Office Support Site. 
      This opens a file navigation window.
   3. Navigate to the CSV that you want to work with. Then click Open or Get Data (Note: The label on the button will be different, depending upon the version and operating system your computer is running). 
      This opens the Text Import Wizard - Step 1 of 3 window. 
   4. Choose the Delimited option button.
   5. In the Start Import At Row box, select 1.
      
      
       
   6. Click Next.
      This opens the Text Import Wizard - Step 2 of 3 window.
   7. Mark the Tab and Comma checkboxes. 
   8. In the Data Preview area, make sure each column is present: Cost Code, Description, Cost Type, and Budget Amount. An example of the first row columns (a.k.a., the header) is below.
      Note: The actual values in the rows below the header should show your specific data. 
      
      
       
   9. Click Next.
      This opens the Text Import Wizard - Step 3 of 3 window.
4. At the bottom of with window under Preview of Selected Data, hold down SHIFT key. Then click the far right column on the spreadsheet. 
   All the columns should now be highlighted in BLACK. 
   Notes:
   • ALWAYS perform this step first.
   • Do NOT choose the General option button. If you do, click Cancel. Then quit Microsoft Excel without saving any changes and restart these steps. 
5. Under the Column Data Format, choose the Text option button. 
   The top row of the Preview of Selected Data area should now say "Text" as shown below. 
   
   
    
6. Click Finish.
   The Import Data window appears prompting you to choose where to import your data. 
7. Choose Existing Sheet.
8. Ensure the value in the cell is: =$A$1
9. Click OK.
   The system imports your data into the CSV spreadsheet. In most situations, this process will ensure your cost codes are imported into Excel using the expected formatting. An example is shown below.