[SOLVED] Converting a number to text

[johng]

I just have the code that mjdamato posted at the top of this page.  I need to bring in some numbers from a database, but before I do that, I want to make sure that I can make php see them as the data type I want (text, rather than a number). 

 

I have account numbers that are 16 digits long, and with php as well as excel, they cut off at least one of the last digits.  What I was trying to do with that code is to export it to excel, and keep all 16 digits.

 

What I was working on (and still not succeeding)  is passing the number (without the quotes) through another variable, so I could try manipulating a variable, instead of just manipulating the number. 

 

Example:

 

<?php
(string)$numbers = 1234567890123456;

$longNumberAsNumber = $numbers;
?>

and this:

<?php
$numbers = 1234567890123456;

$longNumberAsNumber = "'".$numbers;
?>

and this:

<?php
$numbers = 1234567890123456;

$longNumberAsNumber = "$numbers";
?>

 

And none of them have worked. 

[Barand]

And, of course ?

 

$numbers = '1234567890123456';

[johng]

Basically what I'm using the $numbers for is to simulate pulling it from a sql database.  That's why I have kept the $numbers just a number, not added anything to it.  I'm figuring that I can put it into another variable, and mess with that variable, but I won't be able to do anything to the original number (like put quotes or anything around it).  That's what's making this so difficult for me.

[Barand]

I think you are fighting against a "feature" in Excel

 

When I create a CSV with

 

123,"1234567890123456","abc", 42

 

so the 2nd and 3rd are clearly meant to be string data, Excel only treats the 'abc' as a string and insists that the rest must all be numbers.

 

If you put it in a .txt file and import it, you then get the option of specifying a type for individual columns and this time it works.

[johng]

When I use the code that was posted, I got a regular excel file (not csv) and it had four different values:

This is what it looks like when you just look at the cells:

1.23457E+15 

1.2345678901235E+15

1.23457E+15

1234567890123456

 

This is what is actually in the cells:

 

1234567890123500

1.2345678901235E+15

1234567890123460

1234567890123456

 

So what I am fighting is both excel (the second to bottom one is excel formatted) and also PHP (the first two are php formatted).

 

The first one is put in as a number, the second is put in as text (so php actually formatted it like that and placed it in the cell with the 'E')  The third one is a number, (which excel formatted, only holding 15 places), and the last one is text again, the only one that is exactly how it should be. 

 

The only problem with that one is that I can't get it to do that without putting quotes directly around the number itself, not a variable or anything else.  ("1234567890123456")

 

That's the trouble I'm having, because when I pull it in from a database, I can't manipulate the number directly like that.

[Psycho]

Ok, I have done some testing and can't seem to get PHP to convert a long number to a string and still retain all of the original numbers. But, the question is, do you need to do that. Have you tried to run the code above against the values you will be extracting from the database? You may want to format that field in the database as a varchar instead if an int field just to be safe.

[Psycho]

OK, I have another workaround that should complete this solution for you.

 

I still think you should try the code against the values from your database (and use a varchar field type) to see if this will even be an issue in that scenario.

 

But, if you are still having a problem with getting long numbers treated as text (without precision loss) there is a solution. You need to break the number down to smaller parts and then concatenate the parts. I tried several different approaches and this was the only one that worked with the code to produce excel files.

 

To see how this would work, add the following two lines directly after the line that defines the $longNumberAsNumber variable:

 

<?php

$longNumberAsNumber = 1234567890123456;
$first = intval($longNumberAsNumber / 100000000);
$last = $longNumberAsNumber - ($first*100000000);
$longNumberAsNumber = $first . $last; //Now a string variable w/o precision loss

?>

[sasa]

i try some testing and it works fine for me

<?php
mysql_connect('localhost','root');
mysql_selectdb('test');
$result = mysql_query('select * from `table`') or  die(mysql_error());
$file = fopen('e:\doc\test.csv','w');
$x = array('id','num as string','num as num');
fputcsv($file,$x,',');
$out = 'id;long number;'."\n";
while ($row = mysql_fetch_assoc($result)) {
// testing data from database
// num_t is varchar(16)
// num_i is bigint(16)
foreach ($row as $k => $v) {
	echo $k, ' -> ', $v , "\n";
}
echo '<hr />', "\n";
// prepear data to put in csv file
$x = array($row['id'], '="'.$row['num_t'].'"',$row['num_t']);
fputcsv($file,$x,',');
}
fclose($file);
?>

it output

id -> 1
num_t -> 1234567890123456
num_i -> 1234567890123456
<hr />
id -> 2
num_t -> 0012345678945678
num_i -> 12345678945678
<hr />
id -> 3
num_t -> 9876543210987654
num_i -> 9876543210987654
<hr />

 

if i open .csv file in excel it looks

 

 

[attachment deleted by admin]

[johng]

Mjdamato, you are truely amazing.  Thank you for a very creative (and very effective!) work around for my problem.  It worked like a charm (after figuring out that I needed to change my code back!).  So happy that it works now!  Now all I have to do is make sure it is all still there for when I pull info from my db.  Thanks again!!!

 

EDIT:  Also, thanks for everyone who gave input/ideas.  That's what these forums are all about!!!  Thanks all for sticking with me to help figure this out!

[Psycho]

@Sasa,

 

There must be some setting within PHP that handles to what precision numbers are handled. Or it could be a windows vs. Linux thing. Because I get very different results than you using the same numbers:

 

id    num as string         num as num
1     1.23456789012E+015    1.23E+15
2     342391                342391
3     9.87654321099E+015    9.88E+15

[johng]

Any idea on how to format the excel cells? (Like make them bold, or merge cells or anything like that?)

[Psycho]

It will complicate things, but here are a couple options:

 

http://www-128.ibm.com/developerworks/xml/library/os-phpexcel/

 

http://www.akbkhome.com/blog.php/View/78/Generating+excel,+again..html

[sasa]

@Sasa,

 

There must be some setting within PHP that handles to what precision numbers are handled. Or it could be a windows vs. Linux thing. Because I get very different results than you using the same numbers:

 

id    num as string         num as num
1     1.23456789012E+015    1.23E+15
2     342391                342391
3     9.87654321099E+015    9.88E+15

it works OK for me

look at http://sasa.thehostcity.com/index.php

[johng]

Well, that looks like it would work, but way too complicated for what I want.  I was hoping that there was just something simple, but it's not a big deal.  Thanks for finding that for me, maybe I will get super ambitious someday and try to figure it out... maybe...

 

Anyway, thanks again all for helping me out!