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
205 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 56

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 56

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 56

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

733 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