FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » Imported messages » comp.lang.php » Storing strings and numbers properly in CSV files
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Storing strings and numbers properly in CSV files [message #185620] Tue, 22 April 2014 14:43 Go to next message
Sonnich Jensen is currently offline  Sonnich Jensen
Messages: 4
Registered: February 2012
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
Hi

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
2. prices are e.g. 5.2 which Excel translates as a date.

Say:

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

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

I'd like to have 123 and 124 as strings, and eg 5.2 as a float.
Can I format it better than this?

WBR
Sonnich
Re: Storing strings and numbers properly in CSV files [message #185623 is a reply to message #185620] Tue, 22 April 2014 15:07 Go to previous messageGo to next message
Jerry Stuckle is currently offline  Jerry Stuckle
Messages: 2598
Registered: September 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On 4/22/2014 2:43 PM, sonnichjensen(at)gmail(dot)com wrote:
> Hi
>
> 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
> 2. prices are e.g. 5.2 which Excel translates as a date.
>
> Say:
>
> Item;Name;Price;Amount;Total
> 123;Test;5.2;1;5.2
> 124;Test2;1.2;2;2.4
> Total;;;;=sum(e2:e3)
>
> Just copy this into notepad and save with csv extension and you will see.
>
> I'd like to have 123 and 124 as strings, and eg 5.2 as a float.
> Can I format it better than this?
>
> WBR
> Sonnich
>

Unfortunately, while there are some guidelines on how .CSV files should
be written, there are few "hard and fast" rules. About the only ones
are that if a field contains the field and/or row separator character
(by default comma and newline, respectively), that field must be
enclosed in double quotes. Also, if a field contains double quotes,
("), the quotes must be doubled ("").

Because there are so few rules, the program that imports the file is
pretty much free to do what it wants with the file. You might try
enclosing the numbers you want as strings in quotes (i.e. "123"); I
don't know if this will work or not (I don't use Excel).

Other than that, you could try an Excel newsgroup or forum to see if
they have any suggestions on how to format your file so that Excel will
interpret it as you wish.

Wish I could be of more help.

--
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex(at)attglobal(dot)net
==================
Re: Storing strings and numbers properly in CSV files [message #185624 is a reply to message #185620] Tue, 22 April 2014 15:53 Go to previous messageGo to next message
Michael Vilain is currently offline  Michael Vilain
Messages: 88
Registered: September 2010
Karma: 0
Member
add to buddy list
ignore all messages by this user
In article <92e0366c-bb9b-48cc-b239-d90f14865c8a(at)googlegroups(dot)com>,
sonnichjensen(at)gmail(dot)com wrote:

> Hi
>
> 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
> 2. prices are e.g. 5.2 which Excel translates as a date.
>
> Say:
>
> Item;Name;Price;Amount;Total
> 123;Test;5.2;1;5.2
> 124;Test2;1.2;2;2.4
> Total;;;;=sum(e2:e3)
>
> Just copy this into notepad and save with csv extension and you will see.
>
> I'd like to have 123 and 124 as strings, and eg 5.2 as a float.
> Can I format it better than this?
>
> WBR
> Sonnich

The quoting rules with CSV must be somewhere on the web. Try googling
it. It should explain when you have to put entries in quotes. If all
else fails, just quote everything.

--
DeeDee, don't press that button! DeeDee! NO! Dee...
[I filter all Goggle Groups posts, so any reply may be automatically ignored]
Re: Storing strings and numbers properly in CSV files [message #185625 is a reply to message #185620] Tue, 22 April 2014 16:29 Go to previous messageGo to next message
Christoph Michael Bec is currently offline  Christoph Michael Bec
Messages: 207
Registered: June 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
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
> 2. prices are e.g. 5.2 which Excel translates as a date.
>
> Say:
>
> Item;Name;Price;Amount;Total
> 123;Test;5.2;1;5.2
> 124;Test2;1.2;2;2.4
> Total;;;;=sum(e2:e3)
>
> Just copy this into notepad and save with csv extension and you will see.
>
> I'd like to have 123 and 124 as strings, and eg 5.2 as a float.
> Can I format it better than this?

Most likely not. CSV files store all values as strings, and it's up to
the application that uses these data how to interpret them. I would
expect Excel to present a dialog where this can be configured, when you
import a CSV file (IIRC that was so with Excel 2000; haven't used it
since then). FWIW: regarding the CSV format there is the
*informational* RFC 4180[1].

If you're looking particularly for interchange with Excel, you may
consider to create .xls files instead of CSV files. There are libraries
helping with this task, e.g. PEAR's Spreadsheet_Excel_Writer[2].

[1] <http://tools.ietf.org/html/rfc4180>
[2]
< http://pear.php.net/manual/en/package.fileformats.spreadsheet-excel-writer. php>

--
Christoph M. Becker
Re: Storing strings and numbers properly in CSV files [message #185629 is a reply to message #185620] Tue, 22 April 2014 19:24 Go to previous messageGo to next message
Richard Yates is currently offline  Richard Yates
Messages: 86
Registered: September 2013
Karma: 0
Member
add to buddy list
ignore all messages by this user
On Tue, 22 Apr 2014 11:43:02 -0700 (PDT), sonnichjensen(at)gmail(dot)com
wrote:

> Hi
>
> 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
> 2. prices are e.g. 5.2 which Excel translates as a date.
>
> Say:
>
> Item;Name;Price;Amount;Total
> 123;Test;5.2;1;5.2
> 124;Test2;1.2;2;2.4
> Total;;;;=sum(e2:e3)
>
> Just copy this into notepad and save with csv extension and you will see.

Excel will handle this exactly as you want if, instead of opening it,
you go to the Data tab and 'Get External Data from Text.' It will let
you specify the column delimiters (in this case the semicolon) and
data types.

Once it is imported you can tell Excel to interpret columns any way
you want.


>
> I'd like to have 123 and 124 as strings, and eg 5.2 as a float.
> Can I format it better than this?
>
> WBR
> Sonnich
Re: Storing strings and numbers properly in CSV files [message #185630 is a reply to message #185625] Tue, 22 April 2014 19:27 Go to previous messageGo to next message
Thomas 'PointedEars'  is currently offline  Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
Christoph Michael Becker wrote:

> 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 2. prices are e.g. 5.2 which Excel translates as a date.
>>
>> Say:
>>
>> Item;Name;Price;Amount;Total
>> 123;Test;5.2;1;5.2
>> 124;Test2;1.2;2;2.4
>> Total;;;;=sum(e2:e3)
>>
>> Just copy this into notepad and save with csv extension and you will see.
>>
>> I'd like to have 123 and 124 as strings, and eg 5.2 as a float.
>> Can I format it better than this?
>
> Most likely not. CSV files store all values as strings, and it's up to
> the application that uses these data how to interpret them. I would
> expect Excel to present a dialog where this can be configured, when you
> import a CSV file (IIRC that was so with Excel 2000; haven't used it
> since then).

It depends on how you import the data. IIRC (it has been a year), opening
the CSV directly with MS Excel (from Explorer or the browser) gives you no
options, not even the choice of character encoding. Therefore, it is
imperative to quote all values where the type should not determined by
Excel. This is different if you use the Import Data dialog.

You would need to do this manually as fputcsv() can only either quote
nothing or everything. Or perhaps/probably ZF has a capability that could
be reused; I have not checked.

> FWIW: regarding the CSV format there is the *informational* RFC 4180[1].

Fascinating.

> If you're looking particularly for interchange with Excel, you may
> consider to create .xls files instead of CSV files.

That would be .xlsx by now, and it is really not necessary for plain data.
However, I would be surprised if CSVs could contain formulae for import.


PointedEars
--
Prototype.js was written by people who don't know javascript for people
who don't know javascript. People who don't know javascript are not
the best source of advice on designing systems that use javascript.
-- Richard Cornford, cljs, <f806at$ail$1$8300dec7(at)news(dot)demon(dot)co(dot)uk>
Re: Storing strings and numbers properly in CSV files [message #185652 is a reply to message #185620] Thu, 24 April 2014 15:21 Go to previous messageGo to next message
Jim Higgins is currently offline  Jim Higgins
Messages: 20
Registered: November 2010
Karma: 0
Junior Member
add to buddy list
ignore all messages by this user
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.
Re: Storing strings and numbers properly in CSV files [message #185698 is a reply to message #185620] Fri, 02 May 2014 20:48 Go to previous message
Mr Oldies is currently offline  Mr Oldies
Messages: 241
Registered: October 2013
Karma: 0
Senior Member
add to buddy list
ignore all messages by this user
On Tue, 22 Apr 2014 11:43:02 -0700 (PDT), sonnichjensen(at)gmail(dot)com wrote:

> Hi
>
> 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
> 2. prices are e.g. 5.2 which Excel translates as a date.
>
> Say:
>
> Item;Name;Price;Amount;Total
> 123;Test;5.2;1;5.2
> 124;Test2;1.2;2;2.4
> Total;;;;=sum(e2:e3)
>
> Just copy this into notepad and save with csv extension and you will see.
>
> I'd like to have 123 and 124 as strings, and eg 5.2 as a float.
> Can I format it better than this?
>
> WBR
> Sonnich


"1","2","3"
Which is a string and which is a constant?
The CSV file does not care.

The application will determine which is which.
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: Mock HTTP servers for unit tests.
Next Topic: scandir problem
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Oct 21 02:57:06 EDT 2017

Total time taken to generate the page: 0.00856 seconds