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
188 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
12 Comments
 
LVL 34

Expert Comment

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

Author Comment

by:brucegust
Comment Utility
yes
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Try making the column wider, then click on the (correct) value in the input bar.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 51

Accepted Solution

by:
Julian Hansen earned 250 total points
Comment Utility
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 11

Expert Comment

by:tel2
Comment Utility
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 51

Expert Comment

by:Julian Hansen
Comment Utility
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 11

Expert Comment

by:tel2
Comment Utility
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 51

Expert Comment

by:Julian Hansen
Comment Utility
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 11

Expert Comment

by:tel2
Comment Utility
Thanks for the good explanation, julianH.  Well said!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
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 how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now