here is a fix for csv files in excell !

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wesnoel
Forum Commoner
Posts: 58
Joined: Fri Sep 05, 2003 11:53 am

here is a fix for csv files in excell !

Post 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.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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
wesnoel
Forum Commoner
Posts: 58
Joined: Fri Sep 05, 2003 11:53 am

Post 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?
evilMind
Forum Contributor
Posts: 145
Joined: Fri Sep 19, 2003 10:09 am
Location: Earth

Post 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);
Post Reply