How2prevent excel from auto formating n auto aplying formula 2a cell when opening CSV

[MikeVince]

Hi,

I have this problem of mine that troubled me for days.

You see, i have a CSV file which has values like :

"3/1","4/2","10-20-2004"

when i open the CSV file using excel, excel will automatically conver
the values to be like:

3-1-2004 4-1-2004 10-20-2004

the 10-20-2004 is okay since it is really a date but the 3/1 and 4/
are not dates and are not any form of equation.

i just want excel to display them as exactly as seen in the CSV file.
so i tried to right click on the rows for both 3/1 and 4/2 and selecte
"Format Cells". Inside the "Format Cells", i selected the "text
category in the "Number" tab. But when i do that, the value for 3/
will become 38047.
I also tried Edit->Clear->All but still it didnt helped.

do you guys know how to make excel display the CSV value of 3/1 a
exactly 3/1 ?

please reply to my email if you know --> (e-mail address removed)

thanks for your time

 

[FDDavis]

When you import the data to excel from the csv file using the impor
wizard assuming you use a "," as the delimiter charracter selec
delimit then next then select the comma check box then next then o
this screen you can set all columns to text or selected columns as yo
wish also here you can adjust your columns devider. select finishe
then ok and that should get you there

Hope this help

 

[Bernie Deitrick]

Mike,

Rename your file to have a .txt or .prn extension. Excel will start its text
import wizard when you try to open it, and you can select and format columns
of 3/1 as text to prevent the conversion.

HTH,
Bernie
MS Excel MVP

 

[MikeVince]

Hi Bernie,

it worked! i never thought of this method.

But is there any way to keep the extension as CSV. which means, you can
just double click the file from windows explorer then excel will
automatically opens it. coz with .txt, i need to open excel first and
then open the .txt file from excel. having more steps for some users.
In short, when excel opens the .csv file and displays the cell as
3-1-2004 instead of 3/1. is there any way to tell excel to leave the
cell as exactly as it is in the original file without applying any
formula or formatting?


regards,

 

[MikeVince]

Hi Bernie,

it worked! i never thought of this method.

But is there any way to keep the extension as CSV. which means, you ca
just double click the file from windows explorer then excel wil
automatically opens it. coz with .txt, i need to open excel first an
then open the .txt file from excel. having more steps for some users.
In short, when excel opens the .csv file and displays the cell a
3-1-2004 instead of 3/1. is there any way to tell excel to leave th
cell as exactly as it is in the original file without applying an
formula or formatting?


regards,

Mike,

Rename your file to have a .txt or .prn extension. Excel will star
its text
import wizard when you try to open it, and you can select and forma
columns
of 3/1 as text to prevent the conversion.

HTH,
Bernie
MS Excel MV

 

[MikeVince]

Hi,

how to invoke the import wizard when opening .csv file?
when i open csv file, excel wont invoke the import wizard unlike when
open a .txt file.


regards,

 

[Dave Peterson]

Not that I know.

But if the file you're importing is always the same layout, you could rename the
file to *.txt and then record a macro when you import it.

Then clean up that macro code a bit, put a button on a worksheet, assign your
macro to the button and distribute workbook with the macro.

It might be even easier for the end user. You can build formatting, subtotals,
page setup and more into your macro.

Hi Bernie,

it worked! i never thought of this method.

But is there any way to keep the extension as CSV. which means, you can
just double click the file from windows explorer then excel will
automatically opens it. coz with .txt, i need to open excel first and
then open the .txt file from excel. having more steps for some users.
In short, when excel opens the .csv file and displays the cell as
3-1-2004 instead of 3/1. is there any way to tell excel to leave the
cell as exactly as it is in the original file without applying any
formula or formatting?

regards,

Mike,

Rename your file to have a .txt or .prn extension. Excel will start
its text
import wizard when you try to open it, and you can select and format
columns
of 3/1 as text to prevent the conversion.

HTH,
Bernie
MS Excel MVP

 

[FDDavis]

Sorry I took so long getting back to you but I hope this helps
Select cell you want to begin import in say cell A4 then from the men
Bar select Data, select Import External Data, select Import Data,
when the Select Data Source window pops up select the folder where th
file containing the data you want is located, then select the file an
select open. This should bring up the Text Import Wizzard. Selec
Delimit then next, select the box by Comma for using a "," as you
delimit charracter and you can see in the box how the columns will b
set. Now select next again and you can set each column value to tex
date general etc then select finish you then have a choice of wher
you want the data then select ok and it will be there
Hope this is helpfull
Sorry for taking so long to answe

 

[MikeVince]

Hi FD,

i worked but with some glitch and a long steps if someone has to d
this for lots of spreadsheets.
The glitch is that when i import the entire column, row 15 of th
imported column will be shifted 1 row down.
try the attached file.
also, i need to skip the rest of the columns first before clickin
finish which is a an extra step.

regards,

*Sorry I took so long getting back to you but I hope this helps
Select cell you want to begin import in say cell A4 then from th
menu Bar select Data, select Import External Data, select Impor
Data, when the Select Data Source window pops up select the folde
where the file containing the data you want is located, then selec
the file and select open. This should bring up the Text Impor
Wizzard. Select Delimit then next, select the box by Comma for usin
a "," as your delimit charracter and you can see in the box how th
columns will be set. Now select next again and you can set eac
column value to text date general etc then select finish you the
have a choice of where you want the data then select ok and it wil
be there
Hope this is helpfull
Sorry for taking so long to answer

Attachment filename: cat5505-051.zip
Download attachment: http://www.excelforum.com/attachment.php?postid=46999

 

[FDDavis]

I don't know what is causing it to skip down a row but it sounds lik
you probably need a macro to do this for you unfortunately I am of n
help there. Sorry Hopefully someone who is more knowledgable than
will answer
Thanks F

 

[MikeVince]

FD,

It will only have this problem if we import certain rows/column/cells.
There's no problem if i import the whole CSV file.

thanks for your help