CSV files and having numbers and strings in there...

Discussion in 'Excel Programming' started by sonnichjensen@gmail.com, Apr 22, 2014.

  1. Guest

    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
     
    , Apr 22, 2014
    #1
    1. Advertisements

  2. Claus Busch Guest

    Hi,

    Am Tue, 22 Apr 2014 11:41:58 -0700 (PDT) schrieb
    :

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


    change the CSV file to txt file and then open Excel and import from the
    txt file. In the import assistent you can format each column as you
    want.


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 22, 2014
    #2
    1. Advertisements

  3. joeu2004 Guest

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


    Look at your Regional and Language Options control panel. Is the "decimal
    separator" really a period (.), not a comma (,)?

    I assume your date separator is a period (.).

    If the decimal separator is a comma (,), Excel always interprets 5.2 as a
    date as long the component parts (5 and 2) are valid day and month numbers.

    The only work-around I know is to input the column as Text (see below), then
    enter formulas in a parallel column to interpret the text as numbers. Lots
    of work!

    If the decimal separator is a period (.), my version of Excel interprets 5.2
    as a number, despite the ambiguity with the date syntax.

    As for interpreting 123 and 124 as text, I think the best thing is not to
    open the CSV file directly in Excel, but instead to import it as a text
    file. In Excel 2007 and later, click on Data, Get External Data, From Text.

    Click on Next repeatedly, changing the separator as needed. When you get
    Step 3, select the first column, and click on Text for the column data
    format.

    PS: I am surprised that Excel interprets =sum(e2:e3) as a formula, not
    simple text. Caveat: the reference to e2:e3 works only if the entire data
    is imported into the correct range, starting with A1 in the upper-left in
    this case.
     
    joeu2004, Apr 22, 2014
    #3
  4. joeu2004 Guest

    "Claus Busch" <> wrote:
    > change the CSV file to txt file and then open Excel
    > and import from the txt file.


    FYI, it is not necessary to change the extension to ".txt".

    However, it might be prudent to do so because that really is not a CSV file,
    AFAIK.

    In __Comma__Separator_Values file, the separator is a comma (,).

    Excel does permit the separator to be the List Separator specified in the
    Regional and Language Options control panel.

    But in countries where semicolon (;) is the usual List Separator, is it
    common for the CSV file to use a semicolon instead of a comma as the
    separator?

    (Just for my edification.)
     
    joeu2004, Apr 22, 2014
    #4
  5. GS Guest

    Another way...

    Read the CSV into a string variable array using standard VB[A] I/O
    functions, then parse it into an array and 'dump' it into the
    worksheet. Since the data in the array is text, there should be no
    conflict of 'type format'.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 22, 2014
    #5
  6. Claus Busch Guest

    Hi Joe,

    Am Tue, 22 Apr 2014 12:23:37 -0700 schrieb joeu2004:

    > But in countries where semicolon (;) is the usual List Separator, is it
    > common for the CSV file to use a semicolon instead of a comma as the
    > separator?


    yes, in Germany e.g. the semicolon is the separator for CSV files


    Regards
    Claus B.
    --
    Vista Ultimate / Windows7
    Office 2007 Ultimate / 2010 Professional
     
    Claus Busch, Apr 22, 2014
    #6
  7. GS Guest

    Typo...

    > Another way...
    >

    Read the CSV into a string variable using standard VB[A] I/O

    > functions, then parse it into an array and 'dump' it into the
    > worksheet. Since the data in the array is text, there should be no
    > conflict of 'type format'.


    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 22, 2014
    #7
  8. Guest

    That seems to be it

    People say I should use , (COMMA separated....) - however the separator here is ; (semicolon), the comma is not a separator, so it will read , as a part of a field

    Therefore - you are right - using , as a decimal separator here will work as . seems to be for dates (unless the values are out of range then they are floats just to make it more fun)

    Now I only need to store data as strings - currently by adding ' in front of them




    On Tuesday, April 22, 2014 10:17:51 PM UTC+3, joeu2004 wrote:
    > <sonnichjensen> 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)

    >
    >
    >
    > Look at your Regional and Language Options control panel. Is the "decimal
    >
    > separator" really a period (.), not a comma (,)?
    >
    >
    >
    > I assume your date separator is a period (.).
    >
    >
    >
    > If the decimal separator is a comma (,), Excel always interprets 5.2 as a
    >
    > date as long the component parts (5 and 2) are valid day and month numbers.
    >
    >
    >
    > The only work-around I know is to input the column as Text (see below), then
    >
    > enter formulas in a parallel column to interpret the text as numbers. Lots
    >
    > of work!
    >
    >
    >
    > If the decimal separator is a period (.), my version of Excel interprets 5.2
    >
    > as a number, despite the ambiguity with the date syntax.
    >
    >
    >
    > As for interpreting 123 and 124 as text, I think the best thing is not to
    >
    > open the CSV file directly in Excel, but instead to import it as a text
    >
    > file. In Excel 2007 and later, click on Data, Get External Data, From Text.
    >
    >
    >
    > Click on Next repeatedly, changing the separator as needed. When you get
    >
    > Step 3, select the first column, and click on Text for the column data
    >
    > format.
    >
    >
    >
    > PS: I am surprised that Excel interprets =sum(e2:e3) as a formula, not
    >
    > simple text. Caveat: the reference to e2:e3 works only if the entire data
    >
    > is imported into the correct range, starting with A1 in the upper-left in
    >
    > this case.
     
    , Apr 23, 2014
    #8
  9. Guest

    Say, the customer wants a system that just opens - they will forget instructions after the next coffee break....


    On Tuesday, April 22, 2014 10:05:52 PM UTC+3, Claus Busch wrote:
    > Hi,
    >
    >
    >
    > Am Tue, 22 Apr 2014 11:41:58 -0700 (PDT) schrieb
    >
    > sonnichjensen:
    >
    >
    >
    > > 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.

    >
    >
    >
    > change the CSV file to txt file and then open Excel and import from the
    >
    > txt file. In the import assistent you can format each column as you
    >
    > want.
    >
    >
    >
    >
    >
    > Regards
    >
    > Claus B.
    >
    > --
    >
    > Vista Ultimate / Windows7
    >
    > Office 2007 Ultimate / 2010 Professional
     
    , Apr 23, 2014
    #9
  10. GS Guest

    <FWIW>
    I use the pipe character ("|") as a default delimiter so there's no
    conflict with content containing normal punctuation. Optional default
    delimiter is the tilde character ("~") where I use paired values...

    Const sMyVar$ = "prop1~val1|prop2~val2"

    ...that may contain paths. Otherwise...

    Const sMyVar$ = "prop1:val1|prop2:val2"

    ...just because it's easier to type.<g> There are times when I use all 3
    but this is rare...

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    Classic VB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, Apr 23, 2014
    #10
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.