Preserved Topic: CSV file and MS Excel problem (Page 1 of 1)

Hi, please help me.
I have CSV file. There are 4 fields including data like:
4.501%, 0.98%, 1.23%, 3.45%

The problem is that it shows like:
4.50%, 0.98%, 1.23%, 3.45%

Because when I open CSV file with MS Excle, the cell format for % fileds is 2 decimal.
I have been thinking of how to solve this problem. If I set 3 decimal, I will see 0 after ones, which are not needed.

What I did was quated using double quatation mark. When I make CSV string, it was like:
strCSV = "4.501%", "0.98%", "1.23%", "3.45%"
But it does not seem to be working yet.

Many thanks and cheers in advance.

nope... yep nope... I definantly don't know Excel! Anyone else!

-----------------------------------------------------------------------------
Templar654's signature below... then again you already knew that!
-----------------------------------------------------------------------------
Still using IE? Please don't say yes! >>
Why Opera? Because it's the only one!

Online Portfolio | Journal | Community | Paintings | Cell #23041

select the column, right click, choose format, set format as "text".
that should do it if excel remember those settings the next time it's opened.
/Dan

{cell 260} {Blog}
-{ ?Computer games don?t affect kids; I mean if Pac-Man affected us as kids, we?d all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music.? (Kristian Wilson, Nintendo, Inc, 1989.) }-

Hi, guys. Thanks a lot.
Really appreciated that.
But I just wonder if there is kind of stylesheet such as CSS for HTML.
My user don't want to be bothered to do like you said, DmS.
I am using VB6. I wonder if there is a way to control to format the each cell uisng VB6.
Any info will be greatly appreciated.
Cheers, mates.

Take a look at the templating for excel to see what you can pre-format.

Sure you could do it with VBA, but then every user will get a macro warning unless they actually turned that off and with all the viruses in the wild... Simply put, that would only work in a completely controlled enviroment where you have control over the user-settings for security. Or, you'd have to sign your macros... $$$...

/Dan

{cell 260} {Blog}
-{ ?Computer games don?t affect kids; I mean if Pac-Man affected us as kids, we?d all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music.? (Kristian Wilson, Nintendo, Inc, 1989.) }-

Oh, wait a minute, are you generating the csv string outside of excel?
if so you should actually be able do it like this:

strCsv = '1.83%;'1.8%;'1.835%;'1.82%;'2.23%;

By starting every value with ' excel "should" treat it as a string, not a number.
/Dan

{cell 260} {Blog}
-{ ?Computer games don?t affect kids; I mean if Pac-Man affected us as kids, we?d all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music.? (Kristian Wilson, Nintendo, Inc, 1989.) }-

Hi, DmS. Glad to hear from you and thank you so much for your replies.
Well, I tried like below
strCSV = "4.501%", "0.98%", "1.23%", "3.45%"
But still does not go well.
I think when I open CSV file, if each data includes %, Excel seems to format them even though those data are string. So I wonder if there is a way to solve my problem.
I am working on stored procedure at the moment.
I will try exactly what you said above later.
Many thanks anyway.
Cheers.

No Prob

This: " strCsv = '1.83%;'1.8%;'1.835%;'1.82%;'2.23%;... " comes from the fact that excel treats the input in each cell as text if it starts with a '
Example, if you enter 1/10 you might get 1'st October in return since it might be interpreted as date, or you might get 0.10 since that's the result of the mathematical interpretation. preceed 1/10 with ' and it will be treated like text.

Try that.
/Dan

{cell 260} {Blog}
-{ ?Computer games don?t affect kids; I mean if Pac-Man affected us as kids, we?d all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music.? (Kristian Wilson, Nintendo, Inc, 1989.) }-

Hi, DmS.
Good morning.
Really thank you for that.
As you said, if I put single quataton mark on the left hand side of each figure, it was nearly fine.
One thing was:
What if I don't want to show single quatation mark when I open the file in Excle, what should I do???
cheers.

Well, it doesn't show in the cell for me
/Dan

{cell 260} {Blog}
-{ ?Computer games don?t affect kids; I mean if Pac-Man affected us as kids, we?d all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music.? (Kristian Wilson, Nintendo, Inc, 1989.) }-

Hi, DmS. Thanks a lot.
But it does show me.
In my csv file, it is like:

'1.23%, '12.5%, '12.3%

Hum....is anything wrong with it?

Can't tell from here
Try to lose the whitespace after the , to see if it helps.
/Dan

{cell 260} {Blog}
-{ ?Computer games don?t affect kids; I mean if Pac-Man affected us as kids, we?d all be running around in darkened rooms, munching magic pills and listening to repetitive electronic music.? (Kristian Wilson, Nintendo, Inc, 1989.) }-

Sorry to chime in so late, but if it's still relevant, the ' in the excel sheet should be fine.

Excel will usually denote text values like that. Normally, it shouldn't show up in the cell though, only in the formula box at the top.

Hi, Silence. Really thank you for your reply.
Well, you are right. But there is still funny thing.
Actually very very strange thing as well.

I made csv file using notepad.
'2.36,'0.362,'0.256
There is no space between each number.
I opened it by MS Excel.
It shows single quatation in front of each number.
Then Silence, your advice let me try one thing.
I clicked the formula box and hit Back Space key in front of the single quatation mark.
Then, here we go. From the cell, bloody single quatation mark are gone.
Boys, I am thinking of this to solve the pro.
How the hell this happens????
Any words?

Okay, that should work since Excel now knows to treat that data as text.

However, if you are then going to save it as a csv from Excel, make sure the cell properties for those items treat them as text.

Hi, Silence. Glad to hear from ya.
But what do you mean?

I generate CSV file by VB6.
I am making sure to have single quation mark in front of each data.
And one more thing: I am making sure I am deleting any space between data.
When VB finish generating CSV file, I opened it by MS Excel.
Each cell showing bloody ' single quatation mark.
I go to the formular box of each data. Then hit delete at the front of single quatation mark.
Then in the cell, all the problem are gone.

I say again.
In the fornular box, there is no space in front of data.
But why do I have to hit Back Space Key to get what I want to??????

Okay, after much messing around I found the solution to your problem.

When generating the csv file, generate it in the following format:

="1.234%",="12.5%",="12.3%"

Then, when excel opens the csv file, it should show it the way you want it to.

Oh, Silence.
Thank you so so much!!!
It is working!!!!
I am so happy~.
Again, thank you very very much.
Love ya.

No worries, mate.

And this thread has been faq'ed, but I'm not sure what'll happen when the thread gets archived.