FUDforum: comp.lang.php » Storing strings and numbers properly in CSV files

Home » Imported messages » comp.lang.php » Storing strings and numbers properly in CSV files

Show: Today's Messages :: Polls :: Message Navigator

Re: Storing strings and numbers properly in CSV files [message #185652 is a reply to message #185620]

Thu, 24 April 2014 19:21

Jim Higgins
Messages: 20
Registered: November 2010

Karma:

Junior Member

On Tue, 22 Apr 2014 11:43:02 -0700 (PDT), in
<92e0366c-bb9b-48cc-b239-d90f14865c8a(at)googlegroups(dot)com>,
sonnichjensen(at)gmail(dot)com wrote:

> I am saving CSV files from a PHP app, but I face 2 problems:
> 1. stock numbers are sometimes just numbers, but I'd like to keep them as strings

Modify the PHP app to place a "=" in front of each number you want to
keep as a string. Simple example is ="123" Expanded example below.

> 2. prices are e.g. 5.2 which Excel translates as a date.

They don't appear as a date when tested here. Keep reading.

> Say:
>
> Item;Name;Price;Amount;Total
> 123;Test;5.2;1;5.2
> 124;Test2;1.2;2;2.4
> Total;;;;=sum(e2:e3)

The above is not proper CSV format. Modify your PHP program to
produce output that has each value within double quotes and each of
these quoted values separated by commas... like the following.

"Item","Name","Price","Amount","Total"
"123","Test","5.2","1","5.2"
"124","Test2","1.2","2","2.4"
"Total","","","","=sum(e2:e3)"

Read CSV spec if you have to deal with commas and/or quotes in your
actual data. And don't expect Excel to be fully cooperative. If
something doesn't work, you may need to Google for solutions specific
to Excel.

> Just copy this into notepad and save with csv extension and you will see.

Your unaltered sample file (with preceding > removed) resulted in a
4-line spreadsheet with all data for that line in column A of the
resulting Excel... as string data.

My revision (above) to your output results in items in separate
columns, but "Item" data that you want as a string is numeric.

The following is the output you want from your PHP program in order
for the "Item" data to appear as strings when imported into Excel. You
just stick an "=" in front of the quoted value to make Excel see it as
a string. If you wanted the price as a string then an example of what
should be between the commas would be ="5.2"

="Item","Name","Price","Amount","Total"
="123","Test","5.2","1","5.2"
="124","Test2","1.2","2","2.4"
"Total","","","","=sum(e2:e3)"

> I'd like to have 123 and 124 as strings, and eg 5.2 as a float.

My little rev (immediately above) to your output displays 5.2 as a
float, not a string.

> Can I format it better than this?

Try my examples and I think all your problems will go away.

Report message to a moderator

[Message index]

		Storing strings and numbers properly in CSV files By: Sonnich Jensen on Tue, 22 April 2014 18:43
		Re: Storing strings and numbers properly in CSV files By: Jerry Stuckle on Tue, 22 April 2014 19:07
		Re: Storing strings and numbers properly in CSV files By: Michael Vilain on Tue, 22 April 2014 19:53
		Re: Storing strings and numbers properly in CSV files By: Christoph Michael Bec on Tue, 22 April 2014 20:29
		Re: Storing strings and numbers properly in CSV files By: Thomas 'PointedEars' on Tue, 22 April 2014 23:27
		Re: Storing strings and numbers properly in CSV files By: Richard Yates on Tue, 22 April 2014 23:24
		Re: Storing strings and numbers properly in CSV files By: Jim Higgins on Thu, 24 April 2014 19:21
		Re: Storing strings and numbers properly in CSV files By: Mr Oldies on Sat, 03 May 2014 00:48

Previous Topic:	Mock HTTP servers for unit tests.
Next Topic:	scandir problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Nov 27 13:38:58 GMT 2024

Total time taken to generate the page: 0.03921 seconds