Page 1 of 1

here is a fix for csv files in excell !

Posted: Thu Oct 09, 2003 2:05 pm
by wesnoel
ok here is a cool fix for csv files in excel not being read right.

What I am doing is taking data from a submitted for and writting to a csv file. some of the form fields require long numbers that start with 000

ie. 0005621415.

when the files are opend into excel, excel ignores the beginning 0's.

ie. 5621415. or sometime changes to 562+1415

Now the fix....

If the number in the field is written like ="0005621415"
then excel will read the nuber correctly.

I would like my users to only enter the number and not the =" ".

How can I add this to the number variables before the csv file is written.

I also view the csv file in and html formatted table and would need to take out the =" " before it writes the array to html

Any hints or pointers would be appreciated. I know a lot of people have this same issue with csv files and excel so it will help all of us.

Posted: Thu Oct 09, 2003 2:22 pm
by JAM
I thought formating a cell in Excel makes numbers starting with zeros possible. Of course, it not a number anymore, but in this case it doesn't seem to matter (?).

Something like the below might give you more ideas...

Code: Select all

// just one example
// using single quotes around double quotes.
$value = "000123456"; // 000123456
$newvalue = '"' . $value . '"'; // "000123456"
$oldvalue = str_replace('"','',$newvalue); // 000123456

Posted: Thu Oct 09, 2003 4:00 pm
by wesnoel
actually the formula needs to be ="000321654" for it to work right

How to I get the equals sign in ther with out screwing it all up?

maybe use the ascii code or something?

Posted: Fri Oct 10, 2003 12:50 am
by evilMind
JAM wrote:... Snip Snip Snip...

Code: Select all

// just one example
// using single quotes around double quotes.
$value = "000123456"; // 000123456
$newvalue = '"' . $value . '"'; // "000123456"
$oldvalue = str_replace('"','',$newvalue); // 000123456
Chage $newvalue to:

Code: Select all

$newvalue = '="' . $value . '"'; // ="000123456"
// Just for good measure
$oldvalue = ereg_replace('(=)?"','',$newvalue);