6

I'm encountering a problem in Microsoft Excel 2010 regarding auto-formatting a String into a Date, and it seems to lose the cell's original value.

Steps to reproduce:

1 Create the following CSV file: 

 username, points
 phil, 1
 october51, 5

2 Open the file in Microsoft Excel 2010

Note: There is a row with the username october51. I would like Excel to retain this value instead of converting it to some other value.

Sample CSV file opened in a text editor

When I open this sample CSV file in a text editor (eg: gVim), I see exactly what I expect:

sample CSV file opened in gVim

Sample CSV file opened in Microsoft Excel 2010

However, when I open the CSV file in Excel, it formats october51 to Oct-51.

CSV file opened in Excel, october51 has been formatted as Oct-51

I understand why Excel is trying to format the field into a more readable format, but I'd rather keep the value as october51 since it's someone's username.

Trying different cell formats in Excel

Notice that when the file is first opened, the field format for the Oct-51 cell is Custom. So, I tried changing the format to Text

enter image description here

This is no better. The value of the cell is 18902, instead of october51 which is what I was expecting. It looks like Excel throws away the cell's original value immediately upon opening the file.

I've tried every possible format (eg: General, Text, Special, Custom), and none of them output the value october51.

The true issue is that this cell formatting is persisted when the file is saved. If I save this file in Excel and reopen it in a text-editor, the username october51 has actually been changed to Oct-51 or 18902, depending on which format was chosen for the cell.

How can I make Excel retain the cell's original string value?

Improve this question
3
  • 1
    possible duplicate of How can I stop Excel from eating my delicious CSV files and excreting useless data?
    – Excellll
    Nov 17, 2014 at 21:39
  • Thanks for that link, @Excellll! I tried searching for similar questions but didn't find any. That other question's title is pretty brutal :(
    – ampersandre
    Nov 17, 2014 at 21:41
  • 1
    The solution you seek is the second solution that describes using Excel's Import Text Wizard. Note that you can do this with a CSV file, despite what it says about needing to save it as text.
    – Excellll
    Nov 17, 2014 at 21:41

4 Answers 4

Reset to default
4

Try the below steps:

Rename the CSV file to have an extension of TXT (ie: sample.txt). Open a blank Excel sheet > go to 'File' > Open > In the Open search window, navigate the .csv file by choosing "Text Files (*.txt, *.csv)" from the drop down list > click on 'Open' but now. Refer the below step by step screenshots:

enter image description here

enter image description here

Un-tick Tab and tick Comma box:

enter image description here

Choose 'Text' button and click 'Finish' button.

enter image description here

Hope this helps.

Improve this answer
1
  • Thank you, I just tried this workaround and it does indeed work. I've marked this as the accepted answer because it provides good detail for solving the problem without needing extra resources, but for myself in the future I'm just going to use LibreOffice's Calc since it doesn't require a workaround like this (which can be time consuming if you need to work with multiple files). Thanks for your answer!
    – ampersandre
    Nov 17, 2014 at 23:10
3

Instead of opening the CSV file with Excel, create a new worksheet and start Text Import Wizard (on the Data tab, in the Get External Data group, click From Text). In the step 3 you will be able to click the column's header and change the column's datatype to Text. This will stop Excel from interpreting the data as date.

For more information about Text Import Wizard see this article.

Improve this answer
0
1

I ended up just using LibreOffice's Calc instead of Excel.

Opening the CSV file in LibreOffice Calc doesn't mangle the october51 data

LibreOffice Calc parses the data properly

Improve this answer
0

I was having the same problem when using VBA to import a CSV. I found this fixed the problem of csv import changing text that started with JANxxx, FEBxxx, MARxxx etc. into a date was simply to set .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1). Changing the first array value to 2 stopped the incorrect interpretation of the first column which was the one that contained the text that was being interfered with.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .