asked on

What is 3.64536E+17 in the CSV file that I've created and how can I fix it?

I've got a script that's making a CSV file and it sings, but there's one column of info that shows up like this:

3.64536E+17

It should be:

364536426895249000

When you double click on it once the file is open in Excel, it looks fine. But when you look at it as it appears in the column initially, you get that funky 3.64...nonsense.

What am I missing? How can I ensure that the data is presented accurately?

Here's the code that I'm using:

$don="SELECT * FROM verizon_data where posted_day='2013-08-06' limit 400000";
$don_query=mysqli_query($cxn, $don);
	if(!$don_query)
	{
	$nuts=mysqli_errno($cxn).': '.mysqli_error($cxn);
	die($nuts);
	}
$don_count=mysqli_num_rows($don_query);
echo $don_count; 

$fpw= fopen('file.csv', 'w');

while($row=mysqli_fetch_assoc($don_query))
{
fputcsv($fpw, $row);
}
fclose($fpw);

Open in new window

Beverley Portlock

Are you saying that the 3.64536E+17 is how it initially appears in Excel?

Bruce Gust

ASKER

yes

Ray Paseur

Try making the column wider, then click on the (correct) value in the input bar.

Professor J

right click on cells that shows these kind of wierd way
then click on format cells then select custom and then type 0 zero and click ok then it will show correctly.

SOLUTION

ChloesDad

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Julian Hansen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tel2

Hi ChloesDad,

Your solution:
"=""364536426895249000"""
looks interesting. And it works, too...and retains any leading zeros.

Are you able to explain to the class, exactly how/why it works? Or even just to me?

Thanks.
tel2

Julian Hansen

The reason is that Excel interprets that field as a formula with the = followed by the string.

There is another reason for not using CSV as an export format that is destined for Excel. Not an issue in the US but in Europe the default separator is set to a ';' which means a CSV using commas will import each line as a single value.

The solution to this - add the following to the top of the file

sep=;

Open in new window

Change to whatever separator you are using.

tel2

Hi julianH,

> The reason is that Excel interprets that field as a formula with the = followed by the string.

I know, but what's all the quotes? Especially the last 2 double quotes, which come after the closing quotes. There's nothing inside them.

Julian Hansen

Double quotes in Excel are used to include quotes in a string.

Excel expects strings to be in Double quotes

="This is a test"

Open in new window

But what if you need to include a double quote as part of the string? You add two double quotes which tells Excel this is a quote in a string and not a string terminator

="My name is ""John"""

Open in new window

Ouputs

My name is "John"

Open in new window

In this case the formula is to set the cell = to the string version of the number - to do that you have to enclose the number in double quotes to tell Excel to treat it like a string - which means you have to make these pairs of double quotes to tell Excel to include the quote in the string and not terminate the string.

tel2

Thanks for the good explanation, julianH. Well said!