2

I have a CSV with data like this:

  "08540",1,"PRINCETON","NJ"

I know that it is possible to specify "Text" when using the Import Wizard and defining columns. But is there a way to tell Excel to treat quoted values as Text when Excel opens a CSV file?

Improve this question
2

1 Answer 1

Reset to default
1

There is no way to do it automatically. You need to convert the file to the following format first:

="08540",1,"PRINCETON","NJ"

If your files do have the format as you said (i.e. all you need is to add = in the beginning of every line) then the conversion is easy. Create a CONVERT.BAT file on your Desktop with the following code:

@echo off
mkdir "%USERPROFILE%\Desktop\Converted CSV files"
:next
if '%1'=='' goto done
set CSV="%USERPROFILE%\Desktop\Converted CSV files\%~nx1"
for /F "tokens=*" %%A in ('TYPE %1') do echo =%%A >>%CSV%
shift
goto next
:done

Now you can drag-n-drop your .CSV files on the CONVERT icon. The processed files will appear in the Converted CSV files folder.

Improve this answer
4
  • That is also the only way I know. Excel is pretty braindead when it comes to CSV handling. You can't even tell it to treat everything in the file as plain text (which should be the default imho if you doubleclick the CSV instead of importing it via the File-Open wizard).
    – Tonny
    Commented May 15, 2013 at 18:43
  • How do you do that when opening the file? Or I am missing something...
    – Jerry
    Commented May 15, 2013 at 18:49
  • @Jerry Edit the file with a plain text editor like Notepad before loading it into Excel. Yes... I know... You might just as well start Excel first and then open the CSV through File-Open and set the type via the wizard. That's actually less hassle. As I said. Excel is stupid about CSV handling.
    – Tonny
    Commented May 15, 2013 at 21:40
  • Ah, okay, that clears the process up :) And yes, even if I were to use a script to add the equal sign... the import wizard would be a much simpler and faster method.
    – Jerry
    Commented May 16, 2013 at 6:24

You must log in to answer this question.

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