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

brucegustPHP DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Beverley PortlockCommented:
Are you saying that the 3.64536E+17 is how it initially appears in Excel?
0
brucegustPHP DeveloperAuthor Commented:
yes
0
Ray PaseurCommented:
Try making the column wider, then click on the (correct) value in the input bar.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

ProfessorJimJamCommented:
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.
0
ChloesDadCommented:
As others have said, you can change the format of the excel cell once the csv file has been opened, but this would then have to be done every time.

You can change your output to force excel to think that it is a string rather than a number by doing this

1,2,3,4,5,6,"=""364536426895249000"""

when you are creating the csv file. All of the speech marks are important.
0
Julian HansenCommented:
I don't think the problem is your script - Excel has an annoying habbit of treating long strings of digits and converts them to scientific notation.

The key thing is to first check the raw file in a text editor - you should find the data is as expected.

To view in Excel - change file to a .txt extension.
Open Excel and browse to the file
File import wizard comes up
Select delimited
Enter delimiting char
On the next screen scroll over to the column holding your numbers and set it to a Text field

That should import the number correctly.

Options going forward - personally I use the Excel XML format to export the data - you have more options for specifying cell types with the XML format and your data does not get mangled.

To find out how to create the XML file take a sample Excel file that you pre-create to match your data.
Save it to XML
Open the file - there is a header section which you can cut and paste into a header.php file
There is a footer section which you can cut and paste into a footer.php file
The middle bit is your row data - all you need to do is wrap that in a PHP loop and plug your data into the relevant bits.
include('header.php');
// LOOP THROUGH DATA OUTPUTING VALUES TO XML TEMPLATE
include('footer.php');

Open in new window


Simplistic example but it works.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tel2Commented:
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
0
Julian HansenCommented:
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.
0
tel2Commented:
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.
0
Julian HansenCommented:
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.
0
tel2Commented:
Thanks for the good explanation, julianH.  Well said!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.