Precision, Performance, Confidence. ™

How to format an exported CSV file in Excel

This article came about when a customer found opening his CSV file in Excel would remove leading zeros in his asset numbers (see lines 17 and 18 below).

So I launched Excel.   file>open and changed the file type to "all files".   


That forced the wizard to launch.  The wizard is a great tool that will allow you to change the data type format for individual columns.  

I first checked that my data has a header row (if your CSV file does not - you can leave this unchecked):


Then click "Next"


I check that  is delimited by tab and click "next":


 I'll tell the wizard to format the data in selected columns (barcode, serial number and id) as "Text" by clicking on each tab, then clicking the "Text" radio button (repeating for each column to be formatted) and "Finish".

NOTE: MET/TEAM stores all dates with the time component, even if it is NOT displayed in MET/TEAM.  This may require additional formatting of the date column when it is initially viewed in Excel.  This can be accomplished by right clicking the date column's header and selecting "Format Cells...".  In the Format Cells prompt, click on "Date" and then the desired format in the controls to the right.  When satisfied, click "OK" and verify the results..



This is how it will now show up on my excel spreadsheet.  You can see on line 17 and 18 that the leading zeros are maintained.



Again to recap, launch excel first, change the file type to open to "all files" to launch the wizard.  Check the appropriate boxes and select the columns you want to format as text (or any other special format).  The finished product will show the data you need in the proper format.  

Have more questions? Submit a request

Comments

Please sign in to leave a comment.