{"id":10422,"date":"2019-07-21T13:20:48","date_gmt":"2019-07-21T13:20:48","guid":{"rendered":"http:\/\/198.58.113.91\/blog\/?p=10422"},"modified":"2019-07-21T13:38:12","modified_gmt":"2019-07-21T13:38:12","slug":"stop-excel-convert-text-to-number-date-format-csv-file","status":"publish","type":"post","link":"https:\/\/www.winhelponline.com\/blog\/stop-excel-convert-text-to-number-date-format-csv-file\/","title":{"rendered":"Stop Excel from Converting Text to Number or Date format when Opening a CSV file"},"content":{"rendered":"

A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line in a .csv file is a data record consisting of one or more fields, with each field separated by a comma. A .csv file can be imported to and exported from programs that store data in tables, such as Microsoft Office Excel, LibreOffice, or OpenOffice. When you open a .csv file in a spreadsheet, the spreadsheet uses the comma as the field separator (delimiter).<\/p>\n

Here are the contents of a sample .csv file:<\/p>\n

\"name\",\"roll number\",\"dob\",\"address\"\r\n\"steve\",\"011\",\"10-Jan-2012\",\"Jungle Road, Leominster MA 1453\"\r\n\"jack\",\"012\",\"12-Mar-2012\",\"Massachusetts Ave, Lunenburg MA 1462\"\r\n\"rodriguez\",\"013\",\"11-May-2011\",\"780 Lynnway, Lynn MA 1905\"<\/pre>\n
When you open a .csv file in Microsoft Excel, it converts the text format to number or date format depending upon the data pattern in the .csv file. The automatic transformation of the data format may be undesirable in some cases. For instance, you may have a .csv file with the following contents:<\/p>\n
\"\"<\/p>\n
In the above example, the roll number field has a leading zero, and the date of birth is specified in dd-mmm-yyyy format. But, when you open the .csv file in Excel, it truncates the leading zero(es) and formats the date fields into dd-mmm-yy format automatically. Here is an example:<\/p>\n
\"\"<\/p>\n
With the automatic data type conversion, problems can happen when the .csv file contains the ZIP or postal codes, telephone numbers, or government-issued ID card numbers.<\/p>\n
You may want to preserve the leading zero(es) and the original date format, and wondering how to prevent Excel from automatically transforming the data type.<\/p>\n
Stop Excel from Converting Text to Number or Date format when Importing a CSV file<\/h2>\n
Option 1: Rename .csv to .txt and then open in Excel<\/h3>\n
To prevent Excel from automatically changing the data format to number\/date format, you can rename the .csv file to .txt. Then open the .txt file from the File menu in Microsoft Excel.<\/p>\n
Go through the following Text Import Wizard, select Text format for the required columns, and complete the process.<\/p>\n
In Step 1, select Delimited<\/strong> (with is the default selection, anyway). And, choose the My data has headers<\/strong> options if your .csv file’s 1st line represents the caption or header text.<\/p>\n
\"prevent<\/p>\n
In Step 2 of 3, Excel defaults to Tab as the delimiter, as we’re opening a file with extension .txt, not .csv.<\/p>\n
Deselect Tab<\/strong> and select Comma<\/strong>, and click Next.<\/p>\n
\"prevent<\/p>\n
Step 3 of 3 is an important step. You’ll see that General<\/b> option is chosen as the default in the Column data format<\/strong> section. What the General<\/strong> option does is that it converts numeric values to numbers, date values to dates, and all remaining values to text. This is exactly what we do not<\/strong> want.<\/p>\n
\"prevent<\/p>\n
Select the column — “roll number” — and select Text<\/strong> as the data format. Similarly, repeat the step for the Date<\/strong> column.<\/p>\n
\"prevent<\/p>\n
Click Finish<\/strong>. Excel should now import the values exactly as in the .csv file, without truncating the leading zeroes or changing the date\/time format.<\/p>\n
\"prevent<\/p>\n
Don’t prefer renaming the .csv file to .txt every time? Then try Option 2<\/strong>.<\/em><\/p>\n
\n
Option 2: Import .csv file using the Import Data Sources feature<\/h3>\n
To preserve the original data (format) when opening a .csv in Excel, use the “Import data” option in Excel’s Data tab.<\/p>\n
Select the Data<\/strong> tab in Excel, and click From Text\/CSV<\/strong><\/p>\n
\"\"<\/p>\n
Browse to locate the .csv file from which you want to import the contents.\u00a0You’ll see the preview window below where you can define the file origin, and change the delimiter if required. If the preview looks good, click on the Load<\/strong> button.<\/p>\n
But, in this example, the leading zero in the roll number field is truncated \u2014 also, the date field in different data type than what’s in the original .csv file. So, we want to transform it back to the original format.<\/p>\n
\"\"<\/p>\n
Click on the Transform Data<\/strong> button.\u00a0This opens the Power Query Editor which shows exactly what you saw in the preview dialog above.<\/p>\n
\"\"<\/p>\n
Important:<\/strong> If you are using Excel 2013 or earlier versions of Excel, you may need to <\/em>enable Power Query<\/a> in Excel. You can also download and install the most recent version of Power Query for Excel<\/a>, which automatically enables it.<\/em><\/p>\n
Select the columns which you want to transform (to Text format.)<\/p>\n
From the Transform tab, click Data Type:<\/strong> and select Text<\/strong> (instead of General<\/strong>) from the drop-down box.<\/p>\n
\"\"<\/p>\n
Click Replace current when you see the Change Column Type<\/strong> dialog.<\/p>\n
\"\"<\/p>\n
Selecting the Text<\/strong> option makes Excel import the contents of the .csv file “as is,” without intelligently converting the fields to number or date format.<\/p>\n
\"\"<\/p>\n
Verify the fields and if you’re satisfied with how Excel is displaying the contents, click on the Close & Load<\/strong> button in the Home<\/strong> tab. This closes the Power Query Editor and loads the data in your worksheet.<\/p>\n
\"\"<\/p>\n
That’s it! You’ve now imported a .csv file into Excel without changing the data formats.<\/p>\n
\n
Option 3: Formatting the CSV file so that Excel doesn’t convert the data format<\/h3>\n
You can format the .csv file in such a way that opening it normally in Excel (not importing via the Data tab) doesn’t transform the data format from text to number or date. For example, prefix the date and roll number fields with \"=\"<\/code> and suffix them with two double-quotes (\"\"<\/code>), like below:<\/p>\n
\"steve\",\"=\"\"011\"\"\",\"=\"\"10-Jan-2012\"\"\",\"Jungle Road, Leominster MA 1453\"<\/pre>\n
instead of:<\/p>\n
\"steve\",\"011\",\"10-Jan-2012\",\"Jungle Road, Leominster MA 1453\"<\/pre>\n
The “Equal To” symbol makes Excel import the field as a formula, and hence the field’s data format won’t be changed.<\/p>\n