• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

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

0
brucegust
Asked:
brucegust
2 Solutions
 
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
Technology Partners: 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!

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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now