Asset Name Showing Up as Dates In Excel

Objective:

To provide a solution to resolve asset names in the EXCEL spreadsheet after downloading the modify asset template .CSV file from DMU

Issue:

When downloading the modify asset template from the DMU the asset names from the .csv file appear fine in notepad, however in EXCEL any asset names with 2 digits before and after “-“ appear to be in a date format. This is from EXCEL auto-formatting the column and not an issue with the DMU.

Solution:

Example:

Correct in Notepad:

(example 05-01 shows up correctly here in notepad) 

Incorrect in EXCEL:

(Shows up as 1 May in EXCEL)

Solution and alternate Work around

Primary Solution:

1. Navigate to Control Panel/Clock and Region and Select “Change, date, time, and number formats”

The default for Windows is pictured here



2.  This needs to be changed to the highlighted, which will keep Microsoft from interpreting the “-“ character as a date

Secondary Solution(s):

1. Open EXCEL and create new blank spreadsheet
   
   
2. Select “Data” in the Ribbon
   
   
3. Select “From Text/CSV”

4. Choose your file
   
   
5. Once you verify that the information looks correct select “Load”

You should see your information populate like this:

Notes 

In order to save this as a .csv after you make your changes, you will need to make sure there is only one spreadsheet present. If you have two you will get an error like the one below:

To address this, close out the extra spreadsheet at the bottom:

Alternative Solution:

If you followed the steps previously and you are unable to make changes to the date format on your desktop because of administrative rights. Please perform the following once you get to this screen:

(The asset name from loading it from txt/.csv is not correct)

1. Click on “Transform Data”
   
   
2. Right click on “Asset Name” Column and change type to text

3. You will see a box to confirm the change. Click on “Replace Current”

4. Select “Close and Load” and it will load the information in your spreadsheet:

4. Before saving the file as the original file you downloaded from the DMU, make sure you close out any blank sheets.|
5. Upload the DMU and you should be able to see the changes you have made.