Solved

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

Posted on 2014-10-08
12
213 Views
Last Modified: 2014-10-10
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

0
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 40368703
Are you saying that the 3.64536E+17 is how it initially appears in Excel?
0
 

Author Comment

by:brucegust
ID: 40368741
yes
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 40368744
Try making the column wider, then click on the (correct) value in the input bar.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 26

Expert Comment

by:ProfessorJimJam
ID: 40368773
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
 
LVL 15

Assisted Solution

by:ChloesDad
ChloesDad earned 250 total points
ID: 40369009
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
 
LVL 57

Accepted Solution

by:
Julian Hansen earned 250 total points
ID: 40369211
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
 
LVL 12

Expert Comment

by:tel2
ID: 40369698
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
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 40370039
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
 
LVL 12

Expert Comment

by:tel2
ID: 40371960
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
 
LVL 57

Expert Comment

by:Julian Hansen
ID: 40372570
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
 
LVL 12

Expert Comment

by:tel2
ID: 40374000
Thanks for the good explanation, julianH.  Well said!
0

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question