Is is possible to change the default data type Excel uses when opening a .csv file? I would like Excel to default to TEXT rather than General for the Column Data Format when reading a .csv file.
I would like to be able to open a .csv without having leading 0's removed from my data. Currently I use the Import External Data wizard when reading a .csv file but I would prefer to be able to use File/Open or to just double click on the .csv file.
One option is to record a macro of the import process, that way you can define the TextFileColumnDataTypes to be Text.
When you record the macro you will see that the format is set with the line .TextFileColumnDataTypes = Array(2, 2, 2)
where 2 sets the Text format and the 3 elements in the array refer to 3 columns. You can set the array to contain more elements than the number of columns you expect to have in your text files as any extra are ignored.
You can press F8 to launch the Macro dialog which shortens the process such that it';s similar to opening from the file menu (although still not as convenient as being able to double click a file).
I found a useful example macro with some further explanations here
This goes into a bit more detail eexplaining what the relevant settings do, e.g. setting the correct delimiter in the macro etc.
If you have the option, you can save the data to an XML spreadsheet (I know, these files get large very fast) - to open it, just drag it to an open Excel window. This is the only way I know of to get the result you'd like. -- It is only useful for moderate to small data sets.