Stop Excel from automatically converting certain t

Question

Does anyone happen to know if there is a token I can add to my csv for a certain field so Excel doesn't try to convert it to a date?

I'm trying to write a .csv file from my application and one of the values happens to look enough like a date that Excel is automatically converting it from text to a date. I've tried putting all of my text fields (including the one that looks like a date) within double quotes, but that has no effect.

Answer 1

In Excel 2010 open a new sheet. On the Data ribbon click "Get External Data From Text". Select your CSV file then click "Open". Click "Next". Uncheck "Tab", place a check mark next to "Comma", then click "Next". Click anywhere on the first column. While holding the shift key drag the slider across until you can click in the last column, then release the shift key. Click the "text" radio button then click "Finish"

All columns will be imported as text, just as they were in the CSV file.

Answer 2

Prefixing space in double quotes resolved the issue!!

I had data like "7/8" in one of the .csv file columns and MS-Excel was converting it to date as "07-Aug". But with "LibreOffice Calc" there was no issue.

To resolve this, I just prefixed space character(added space before 7) like " 7/8" and it worked for me. This is tested for Excel-2007.

Answer 3

This is a only way I know how to accomplish this without messing inside the file itself. As usual with Excel, I learned this by beating my head on the desk for hours.

Change the .csv file extension to .txt; this will stop Excel from auto-converting the file when it's opened. Here's how I do it: open Excel to a blank worksheet, close the blank sheet, then File => Open and choose your file with the .txt extension. This forces Excel to open the "Text Import Wizard" where it'll ask you questions about how you want it to interpret the file. First you choose your delimiter (comma, tab, etc...), then (here's the important part) you choose a set columns of columns and select the formatting. If you want exactly what's in the file then choose "Text" and Excel will display just what's between the delimiters.

Answer 4

(EXCEL 2016 and later, actually I have not tried in older versions)

1. Open new blank page
2. Go to tab "Data"
3. Click "From Text/CSV" and choose your csv file
4. Check in preview whether your data is correct.
5. In сase when some column is converted to date click "edit" and then select type Text by clicking on calendar in head of column
6. Click "Close & Load"

Answer 5

I had a similar problem and this is the workaround that helped me without having to edit the csv file contents:

If you have the flexibility to name the file something other than ".csv", you can name it with a ".txt" extension, such as "Myfile.txt" or "Myfile.csv.txt". Then when you open it in Excel (not by drag and drop, but using File->Open or the Most Recently Used files list), Excel will provide you with a "Text Import Wizard".

In the first page of the wizard, choose "Delimited" for the file type.

In the second page of the wizard choose "," as the delimiter and also choose the text qualifier if you have surrounded your values by quotes

In the third page, select every column individually and assign each the type "Text" instead of "General" to stop Excel from messing with your data.

Hope this helps you or someone with a similar problem!

Answer 6

A workaround using Google Drive (or Numbers if you're on a Mac):

1. Open the data in Excel
2. Set the format of the column with incorrect data to Text (Format > Cells > Number > Text)
3. Load the .csv into Google Drive, and open it with Google Sheets
4. Copy the offending column
5. Paste column into Excel as Text (Edit > Paste Special > Text)

Alternatively if you're on a Mac for step 3 you can open the data in Numbers.