Opening CSV file in Excel

[merentha]

I've a problem with opening a particular CSV (comma-delimited text) file in Excel. One of the data column consists of alphanumeric text. However, whenever a text such as "1234E5" appears, Excel automatically converts it to a number, assuming it to be 1234x10^5.

My current method of overcoming this problem is to save the file in .TXT format using notepad and open the new text file in Excel. Only then will the Excel text file wizard allow me to format that column as Text.

Is there a better alternative, such as some settings that I can disable within Excel to stop the autoconvert? Thanks.

 

[xenxabar]

On the last step of the import (wizard) select Text for the Column Data format. Then you can choose the specific formats of each column (or cell) as you like.

 

[merentha]

On the last step of the import (wizard) select Text for the Column Data format. Then you can choose the specific formats of each column (or cell) as you like.

I'm using this with the .TXT file. However, the wizard doesn't activate for the .CSV file. Excel automatically opens it in a worksheet format and converts that column to number format.

 

[CriticalThanking]

IIRC in 2003, the wizard does not pop up if you double-click the file. You have to File->Open the csv to get the import wizard.

CT

 

[merentha]

IIRC in 2003, the wizard does not pop up if you double-click the file. You have to File->Open the csv to get the import wizard.

My workplace is still using Excel 2002. Perhaps that is the problem. Guess I'll just have to bear with the problem until the IT dept decides to upgrade. Thanks for all the help.

 

[xenxabar]

IIRC in 2003, the wizard does not pop up if you double-click the file. You have to File->Open the csv to get the import wizard.

CT

In 2003, the wizard is under Data->Import Data. If you can get the wizard, you can determine the format of each column.

 

[GregC]

I've had the same problem. I deal a lot with parcel information and if CSV's are called in I always have formating problems. Parcel id's such as 3-15 come in as March 15, deed book and pages with low numbers also come in as dates and here in Maine almost all our zip codes start with 04 so the leading zero is dropped. I just convert the file to .txt to avoid any problems.

 

[kevin]

IIRC in 2003, the wizard does not pop up if you double-click the file. You have to File->Open the csv to get the import wizard.

CT

and that really annoys the crap out of me.

 

[kevin]

if you have control of the exporter making sure text fields are properly quoted can help. Don't think that fixes the date conversion issue though.

 

[RenaissanceBiker]

You don't have to use Notepad to convert it. A CSV file is already ASCII text. You can just change the file extention by renaming it in your explorer window.

 

[merentha]

You don't have to use Notepad to convert it. A CSV file is already ASCII text. You can just change the file extention by renaming it in your explorer window.

Do I just add ".txt" to the file during rename? I tried that but the file type is still a "Microsoft Excel Comma Separated Values File" instead of "Text Document", so Excel 2002 still treats it like a CSV file.

 

[kevin]

Do I just add ".txt" to the file during rename? I tried that but the file type is still a "Microsoft Excel Comma Separated Values File" instead of "Text Document", so Excel 2002 still treats it like a CSV file.

sounds like you have the display of file extensions turned off. (I'm on my Mac at the moment so this is from memory) in File Explorer window go to tools, folder options. click the view tab. make sure Hide extensions for known file types is turned OFF.

If this setting is turned on, when you turn it off you'll see the name of the file change to filename.txt.csv. Rename the file deleting the .csv at the end.

 

[GregC]

Do I just add ".txt" to the file during rename? I tried that but the file type is still a "Microsoft Excel Comma Separated Values File" instead of "Text Document", so Excel 2002 still treats it like a CSV file.

If the file name is abcdefg.csv Just replace the .csv with .txt so the file name will be abcdefg.txt

 

[xenxabar]

In 2003, the wizard is under Data->Import Data. If you can get the wizard, you can determine the format of each column.

Do I just add ".txt" to the file during rename? I tried that but the file type is still a "Microsoft Excel Comma Separated Values File" instead of "Text Document", so Excel 2002 still treats it like a CSV file.

I don't have 2002, but the same principle applies. Just go to the Data ->Get External Data and select the CSV file that you want (you don't need to rename anything). Then using the Text Import Wizard, choose the columns you want to include and the format you want them. Note in the sample screen shot below, I've set the first two columns to Text and excluded the last.

 

[merentha]

Thanks to everybody for the help, especially xenxabar. The elusive text import wizard now appears.