Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.c > #98476 > unrolled thread

Using C to convert excel spreadsheet into mysql

Back to article view | Back to comp.lang.c

Contents

  Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 02:50 -0800
    Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 11:13 +0000
      Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 03:50 -0800
        Re: Using C to convert excel spreadsheet into mysql Barry Schwarz <schwarzb@dqel.com> - 2016-12-09 08:43 -0800
          Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 09:09 -0800
      Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 07:06 -0800
        Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 15:50 +0000
          Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 09:07 -0800
            Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 17:23 +0000
              Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 09:29 -0800
              Re: Using C to convert excel spreadsheet into mysql Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2016-12-09 12:43 -0500
                Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 10:00 -0800
                  Re: Using C to convert excel spreadsheet into mysql Manfred <noname@invalid.add> - 2016-12-09 19:49 +0100
                Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 18:15 +0000
              Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 09:55 -0800
                Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 18:22 +0000
                  Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 00:23 -0800
                    Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-11 01:33 -0800
                      Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 03:38 -0800
                        Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-11 12:35 -0800
                          Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-12 01:08 -0800
                            Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-12 10:14 +0000
                              Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-12 02:42 -0800
                                Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-12 09:02 -0800
                  Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 00:23 -0800
                Re: Using C to convert excel spreadsheet into mysql "James R. Kuyper" <jameskuyper@verizon.net> - 2016-12-09 13:44 -0500
                  Re: Using C to convert excel spreadsheet into mysql Lew Pitcher <lew.pitcher@digitalfreehold.ca> - 2016-12-09 13:57 -0500
                    Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 02:57 -0800
                    Re: Using C to convert excel spreadsheet into mysql BartC <bc@freeuk.com> - 2016-12-11 11:43 +0000
                    Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-11 15:51 +0000
                  Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 02:42 -0800
            Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 18:09 +0000
              Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 10:15 -0800
              Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-09 12:16 -0800
                Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-11 15:56 +0000
                  Re: Using C to convert excel spreadsheet into mysql luser droog <luser.droog@gmail.com> - 2016-12-11 08:27 -0800
                    Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-11 16:37 +0000
                  Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-11 12:40 -0800
                    Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-12 01:13 -0800
        Re: Using C to convert excel spreadsheet into mysql Barry Schwarz <schwarzb@dqel.com> - 2016-12-09 08:53 -0800
          Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 09:20 -0800
      Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-09 11:05 -0800
        Re: Using C to convert excel spreadsheet into mysql Manfred <noname@invalid.add> - 2016-12-09 20:27 +0100
        Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 03:19 -0800
          Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-11 15:48 +0000
            Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-11 12:32 -0800
            Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-12 01:16 -0800
              Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-12 10:31 +0000
                Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-12 02:46 -0800
                  Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-12 10:51 +0000
                  Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-12 09:09 -0800
        Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 03:31 -0800
        Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-11 14:48 +0000
    Using C to convert excel spreadsheet into mysql "Rick C. Hodgin" <rick.c.hodgin@gmail.com> - 2016-12-09 03:16 -0800
      Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 03:50 -0800
        Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 12:00 +0000
          Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 06:47 -0800
          Re: Using C to convert excel spreadsheet into mysql "Rick C. Hodgin" <rick.c.hodgin@gmail.com> - 2016-12-09 07:48 -0800
            Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 16:17 +0000
            Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 08:37 -0800
            Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-09 08:45 -0800
              Re: Using C to convert excel spreadsheet into mysql "Rick C. Hodgin" <rick.c.hodgin@gmail.com> - 2016-12-09 08:50 -0800
                Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 16:55 +0000
                  Re: Using C to convert excel spreadsheet into mysql "James R. Kuyper" <jameskuyper@verizon.net> - 2016-12-09 12:28 -0500
                    Re: Using C to convert excel spreadsheet into mysql Richard Heathfield <rjh@cpax.org.uk> - 2016-12-09 18:10 +0000
    Re: Using C to convert excel spreadsheet into mysql "Mr. Man-wai Chang" <toylet.toylet@gmail.com> - 2016-12-09 19:48 +0800
    Re: Using C to convert excel spreadsheet into mysql Keith Thompson <kst-u@mib.org> - 2016-12-09 11:00 -0800
      Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 03:03 -0800
    Re: Using C to convert excel spreadsheet into mysql andre <ahr@blabla.be> - 2016-12-10 10:43 +0100
      Re: Using C to convert excel spreadsheet into mysql Alla _ <modelling.data@gmail.com> - 2016-12-11 03:33 -0800

Page 2 of 4 — ← Prev page 1 [2] 3 4  Next page →

#99015

On Sunday, December 11, 2016 at 11:35:34 PM UTC+3, Keith Thompson wrote:
> Alla _ <modelling.data@gmail.com> writes:
> > <snip>
> >> 
> >> > Income Statement,2011,2012,2013,2014,2015,
> >> > Sales,1062,1252,1587,1934,2519,
> >> > Cost of Goods Sold,654,814,1009,1190,1499,
> >> >   Gross Profit,408,438,578,744,1020,
> >> > SG&A,254,271,364,454,576,
> >> >   Operating Income before Depr,154,167,214,290,444,
> >> > Depreciation and Amortization,25,31,38,52,70,
> >> >   Operating Profit,129,136,176,238,374,
> >> > Interest Expense,4,3,3,1,4,
> >> > Other Gains and Losses,0,7,10,0,-1,
> >> >   Pretax Income,125,126,163,237,371,
> >> > Income Tax Expense,55,52,65,92,141,
> >> >   Net Income,70,74,98,145,230,
> >> > ,,,,,,
> >> 
> >> You have a CSV file with 14 rows, each containing 7 comma-separated
> >> fields.  In the first 13 rows, the first field is a string and the
> >> remaining fields are numbers.  In the final row, all 7 fields are empty.
> >> 
> >> If you don't want that final row of empty fields, you should be able to
> >> just delete it in Excel and re-save it.
> >> 
> > That's the first idea I got when I saw those commas, and I have deleted
> > rows up to 520, re-saved the file, then saves as an .csv, and still got 
> > that empty row. I have done that a few times, each time getting the 
> > same result. Strange. 
> 
> It might be some kind of glitch in Excel.  I don't run Windows, so I
> can't investigate further.  (LibreOffice Calc, a replacement for MS
> Excel, doesn't seem to have that issue.)
> 
> BTW, I see you've started snipping more quoted text, which is great --
> but please don't snip the attribution lines.  There's no indication in
> your followup that I wrote the quoted text.
> 
I am trying to follow advices ) Do I understand correctly that attribution
lines are those at the beginning of the post and/or a signature? 
> -- 
> Keith Thompson (The_Other_Keith) kst-u@mib.org  <http://www.ghoti.net/~kst>
> Working, but not speaking, for JetHead Development, Inc.
> "We must do something.  This is something.  Therefore, we must do this."
>     -- Antony Jay and Jonathan Lynn, "Yes Minister"

[toc] | [prev] | [next] | [standalone]

#99023

On 12/12/16 09:08, Alla _ wrote:
> On Sunday, December 11, 2016 at 11:35:34 PM UTC+3, Keith Thompson wrote:

<snip>

>> BTW, I see you've started snipping more quoted text, which is great --
>> but please don't snip the attribution lines.  There's no indication in
>> your followup that I wrote the quoted text.
>>
> I am trying to follow advices ) Do I understand correctly that attribution
> lines are those at the beginning of the post and/or a signature?

Attribution lines tell us who said what. Above, you will see that I have 
snipped all the material except the stuff that lends context to my 
reply. I have *ATTRIBUTED* that material to the people who contributed 
it, by citing their names (or rather, by not deleting those 
attributions, which are inserted automatically by newsreaders). In this 
case, the attributions are:

* "On 12/12/16 09:08, Alla _ wrote"
...and...
* "On Sunday, December 11, 2016 at 11:35:34 PM UTC+3, Keith Thompson wrote".

The chevrons indicate the level of quoting. So, in the following:

*************************************
* Fred said:                        *
* > Joe said:                       *
* >> Tom said:                      *
* >>> I think you're wrong.         *
* >> No, he's right, because X.     *
* > Are you sure? What about Y?     *
* I'm sure, because of Z as well.   *
* --                                *
* Joe, master of wossname           *
*************************************

"Fred said", "Joe said", and "Tom said" are ATTRIBUTIONS. The number of 
chevrons indicates who said what. Fred's contributions are marked off 
with one ">", Joe's with two, and Tom's with three. Newsreaders 
automatically increase the chevron count with each new reply. An 
attribution should only be removed if nothing remains from the person 
being attributed.

By leaving attributions in place, we (a) acknowledge the contribution 
made by a particular subscriber, and (b) make it clear who said what. 
That's why we don't delete attributions unless there's nothing left in 
our reply that stems from the contributor in question.

In the above example, "Joe, master of wossname", which comes after the 
dash-dash-space line, is a "signature". Signatures should NOT be quoted 
in replies. A good newsreader will automatically remove them.

The fact that your newsreader does not automatically remove signatures 
is one good sign that it isn't a good newsreader. Consider using 
something else.

-- 
Richard Heathfield
Email: rjh at cpax dot org dot uk
"Usenet is a strange place" - dmr 29 July 1999
Sig line 4 vacant - apply within

[toc] | [prev] | [next] | [standalone]

#99026

On Monday, December 12, 2016 at 1:15:00 PM UTC+3, Richard Heathfield wrote:
> On 12/12/16 09:08, Alla _ wrote:
> > On Sunday, December 11, 2016 at 11:35:34 PM UTC+3, Keith Thompson wrote:
> 
> <snip>
> 
> >> BTW, I see you've started snipping more quoted text, which is great --
> >> but please don't snip the attribution lines.  There's no indication in
> >> your followup that I wrote the quoted text.
> >>
> > I am trying to follow advices ) Do I understand correctly that attribution
> > lines are those at the beginning of the post and/or a signature?
> 
> Attribution lines tell us who said what. Above, you will see that I have 
> snipped all the material except the stuff that lends context to my 
> reply. I have *ATTRIBUTED* that material to the people who contributed 
> it, by citing their names (or rather, by not deleting those 
> attributions, which are inserted automatically by newsreaders). In this 
> case, the attributions are:
> 
> * "On 12/12/16 09:08, Alla _ wrote"
> ...and...
> * "On Sunday, December 11, 2016 at 11:35:34 PM UTC+3, Keith Thompson wrote".
> 
I thought so. Thank you for confirming ) 

> The chevrons indicate the level of quoting. So, in the following:
> 
> *************************************
> * Fred said:                        *
> * > Joe said:                       *
> * >> Tom said:                      *
> * >>> I think you're wrong.         *
> * >> No, he's right, because X.     *
> * > Are you sure? What about Y?     *
> * I'm sure, because of Z as well.   *
> * --                                *
> * Joe, master of wossname           *
> *************************************
> 
> "Fred said", "Joe said", and "Tom said" are ATTRIBUTIONS. The number of 
> chevrons indicates who said what. Fred's contributions are marked off 
> with one ">", Joe's with two, and Tom's with three. Newsreaders 
> automatically increase the chevron count with each new reply. An 
> attribution should only be removed if nothing remains from the person 
> being attributed.
> 
> By leaving attributions in place, we (a) acknowledge the contribution 
> made by a particular subscriber, and (b) make it clear who said what. 
> That's why we don't delete attributions unless there's nothing left in 
> our reply that stems from the contributor in question.
> 
> In the above example, "Joe, master of wossname", which comes after the 
> dash-dash-space line, is a "signature". Signatures should NOT be quoted 
> in replies. A good newsreader will automatically remove them.
> 
> The fact that your newsreader does not automatically remove signatures 
> is one good sign that it isn't a good newsreader. Consider using 
> something else.

> -- 
> Richard Heathfield
> Email: rjh at cpax dot org dot uk
> "Usenet is a strange place" - dmr 29 July 1999
> Sig line 4 vacant - apply within

[toc] | [prev] | [next] | [standalone]

#99081

Alla _ <modelling.data@gmail.com> writes:
> On Monday, December 12, 2016 at 1:15:00 PM UTC+3, Richard Heathfield wrote:
[SNIP]
>> -- 
>> Richard Heathfield
>> Email: rjh at cpax dot org dot uk
>> "Usenet is a strange place" - dmr 29 July 1999
>> Sig line 4 vacant - apply within

The above 5 lines, starting with "-- ", are a signature.  You should
delete it when you post a followup (manually if your newsreader doesn't
do it for you).

-- 
Keith Thompson (The_Other_Keith) kst-u@mib.org  <http://www.ghoti.net/~kst>
Working, but not speaking, for JetHead Development, Inc.
"We must do something.  This is something.  Therefore, we must do this."
    -- Antony Jay and Jonathan Lynn, "Yes Minister"

[toc] | [prev] | [next] | [standalone]

#98762

<snip>
> 
> Again, getting rid of "save cell contents as shown" will fix this.
> 
I have not attached the contents:
Income Statement,2011,2012,2013,2014,2015,
Sales,1062,1252,1587,1934,2519,
Cost of Goods Sold,654,814,1009,1190,1499,
  Gross Profit,408,438,578,744,1020,
SG&A,254,271,364,454,576,
  Operating Income before Depr,154,167,214,290,444,
Depreciation and Amortization,25,31,38,52,70,
  Operating Profit,129,136,176,238,374,
Interest Expense,4,3,3,1,4,
Other Gains and Losses,0,7,10,0,-1,
  Pretax Income,125,126,163,237,371,
Income Tax Expense,55,52,65,92,141,
  Net Income,70,74,98,145,230,
,,,,,,

[toc] | [prev] | [next] | [standalone]

#98592

On 12/09/2016 12:55 PM, Alla _ wrote:
...
> I have found the problem (part of it) - Region settings on my Mac;
> changed those for US (although English has been the default language),
> relaunched Excel, saved the file in .csv and got a better result (see below).
>
> But I still don't understand why some commas are surrounded by quotes,

You don't have commas surrounded by quotes - you have commas surrounded 
by quoted strings. Example: " $1,062 "," $1,252 ". Note, in particular, 
that these quoted strings contain commas, which must be distinguished 
from the commas that separate the fields. And that distinction is 
something you're going to have to achieve with your own code - no 
built-in facility of C will handle that for you.

> and how C will interpret dollar signs, ...

C won't do anything special with dollar signs - which means your program 
is going to have to detect dollar signs  and do whatever special 
handling is required. That's equally true of the double quote marks.

> ... as well as some white space I see
> in this result; there should be no white spaces in csv file, if I have
> understood it correctly. ...

As a general rule, spaces in text strings must be preserved in the csv 
file. It's also not uncommon to refer to something as "comma separated" 
when it's actually separated by the combination of a comma followed by a 
space character. This file seems to contain some comma delimiters that 
are accompanied by a space character, and others that aren't, so your 
code is going to have to be able to deal with both possibilities.

> ... Also, it seems I have to delete all minuses
> in the original file because as the 4th row from the bottom shows
> csv reads it as it is usually formatted in Excel (1).

A better approach is to write your code so that it recognizes the 
convention that (1) means -1.

Some of your financial data is apparently stored in a cell with a format 
string that specifies use of ',' as a delimiter. When you save it in CSV 
format, it's stored in quotes, to prevent it from being misinterpreted 
as a delimiting comma.

I don't currently have access to Excel, but I do have access to 
LibreOffice Calc, which knows how to read and write files in Excel 
format and CSV, and generally works similarly to Excel. When I created 
two cells, one with a currency value formatted as $1,234.56, and the 
other containing the text "$1,234.56", both cells were saved the same 
way in the CSV file, which boggles my mind.

> Income Statement,2011,2012,2013,2014,2015
> Sales," $1,062 "," $1,252 "," $1,587 "," $1,934 "," $2,519 "
> Cost of Goods Sold,654 ,814 ,"1,009 ","1,190 ","1,499 "
>   Gross Profit,408 ,438 ,578 ,744 ,"1,020 "
> "Selling, General, and Admin Exp",254 ,271 ,364 ,454 ,576
>   Operating Income before Depr,154 ,167 ,214 ,290 ,444
> Depreciation and Amortization,25 ,31 ,38 ,52 ,70
>   Operating Profit,129 ,136 ,176 ,238 ,374
> Interest Expense,4 ,3 ,3 ,1 ,4
> Other Gains and Losses,0 ,7 ,10 ,0 ,(1)
>   Pretax Income,125 ,126 ,163 ,237 ,371
> Income Tax Expense,55 ,52 ,65 ,92 ,141
>   Net Income, $70 , $74 , $98 , $145 , $230
> ,,,,,

[toc] | [prev] | [next] | [standalone]

#98595

On Friday December 9 2016 13:44, in comp.lang.c, "James R. Kuyper"
<jameskuyper@verizon.net> wrote:

> On 12/09/2016 12:55 PM, Alla _ wrote:
> ...
>> I have found the problem (part of it) - Region settings on my Mac;
>> changed those for US (although English has been the default language),
>> relaunched Excel, saved the file in .csv and got a better result (see
>> below).
>>
>> But I still don't understand why some commas are surrounded by quotes,
> 
> You don't have commas surrounded by quotes - you have commas surrounded
> by quoted strings. Example: " $1,062 "," $1,252 ". Note, in particular,
> that these quoted strings contain commas, which must be distinguished
> from the commas that separate the fields. And that distinction is
> something you're going to have to achieve with your own code - no
> built-in facility of C will handle that for you.
> 
>> and how C will interpret dollar signs, ...
> 
> C won't do anything special with dollar signs - which means your program
> is going to have to detect dollar signs  and do whatever special
> handling is required. That's equally true of the double quote marks.
[snip]

... AND ...

we haven't even touched on the effects of "internationalization".

For instance, in North America, one way to write "one thousand two hundered
fifty five and seven tenths" numerically would be
  1,255.7
but, in Europe, that same number might be written numerically as
  1.255,7

This is important, because /some/ CSV writers save numeric values according to
the locale of the writer, and thus you might have to READ a CSV that is
formatted that way. And, because both of those numeric formats contain
commas, the CSV file either needs to wrap those /numeric/ fields in quotes,
or it has to otherwise "escape" the embedded commas.

Similarly, dollar signs are not the only currency symbol that could appear in
your data; think of currency amounts that use the Euro sign, or the Pound
sign.

As I said, reading a CSV is hard because there are so many variations and
exceptions.

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

[toc] | [prev] | [next] | [standalone]

#98775

On Friday, December 9, 2016 at 9:58:07 PM UTC+3, Lew Pitcher wrote:
> On Friday December 9 2016 13:44, in comp.lang.c, "James R. Kuyper"
> <jameskuyper@verizon.net> wrote:
> 
> > On 12/09/2016 12:55 PM, Alla _ wrote:
> > ...
> >> I have found the problem (part of it) - Region settings on my Mac;
> >> changed those for US (although English has been the default language),
> >> relaunched Excel, saved the file in .csv and got a better result (see
> >> below).
> >>
> >> But I still don't understand why some commas are surrounded by quotes,
> > 
> > You don't have commas surrounded by quotes - you have commas surrounded
> > by quoted strings. Example: " $1,062 "," $1,252 ". Note, in particular,
> > that these quoted strings contain commas, which must be distinguished
> > from the commas that separate the fields. And that distinction is
> > something you're going to have to achieve with your own code - no
> > built-in facility of C will handle that for you.
> > 
> >> and how C will interpret dollar signs, ...
> > 
> > C won't do anything special with dollar signs - which means your program
> > is going to have to detect dollar signs  and do whatever special
> > handling is required. That's equally true of the double quote marks.
> [snip]
> 
> ... AND ...
> 
> we haven't even touched on the effects of "internationalization".
> 
> For instance, in North America, one way to write "one thousand two hundered
> fifty five and seven tenths" numerically would be
>   1,255.7
> but, in Europe, that same number might be written numerically as
>   1.255,7
> 
> This is important, because /some/ CSV writers save numeric values according to
> the locale of the writer, and thus you might have to READ a CSV that is
> formatted that way. And, because both of those numeric formats contain
> commas, the CSV file either needs to wrap those /numeric/ fields in quotes,
> or it has to otherwise "escape" the embedded commas.
> 
> Similarly, dollar signs are not the only currency symbol that could appear in
> your data; think of currency amounts that use the Euro sign, or the Pound
> sign.
> 
> As I said, reading a CSV is hard because there are so many variations and
> exceptions.
> 
Yes, that is why one of the most prominent software companies Salesforce, which 
provides business solutions, explicitly states what type of csv files their systems require )
https://help.salesforce.com/HTViewSolution?id=000221341
I intend to follow their rule, and not to overload my student's code with features
detecting numerous aspects of csv file formatting, as well as different possible
inputs ) 
> -- 
> Lew Pitcher
> "In Skills, We Trust"
> PGP public key available upon request

[toc] | [prev] | [next] | [standalone]

#98782

On 09/12/2016 18:57, Lew Pitcher wrote:

> we haven't even touched on the effects of "internationalization".
>
> For instance, in North America, one way to write "one thousand two hundered
> fifty five and seven tenths" numerically would be
>   1,255.7
> but, in Europe, that same number might be written numerically as
>   1.255,7

Which you rarely see (and not only because I'm in the UK!). CSV files 
still separate with commas not periods.

> This is important, because /some/ CSV writers save numeric values according to
> the locale of the writer, and thus you might have to READ a CSV that is
> formatted that way. And, because both of those numeric formats contain
> commas, the CSV file either needs to wrap those /numeric/ fields in quotes,
> or it has to otherwise "escape" the embedded commas.
>
> Similarly, dollar signs are not the only currency symbol that could appear in
> your data; think of currency amounts that use the Euro sign, or the Pound
> sign.
>
> As I said, reading a CSV is hard because there are so many variations and
> exceptions.

If a field contain troublesome characters such as commas or spaces then 
the whole thing would be wrapped in quotes.

Then reading each field /as a string/ should be little problem (but 
something like scanf might have difficulty).

Turning the string into a numerical value is a separate matter, and it 
might not be possible without extra information about a particular column.

What numerical value should even "$100" have anyway? What about 
$1000000000000000.99? What if one field is 1000 (representing US$) and 
another is also 1000 (but representing TT$)? How about if another is 
also 1000 (but is an index into a separate table of names)?

This goes beyond the scope of just reading CSV data from a file, and 
into interpretation.

-- 
Bartc

[toc] | [prev] | [next] | [standalone]

#98815

On 09/12/16 18:57, Lew Pitcher wrote:
> On Friday December 9 2016 13:44, in comp.lang.c, "James R. Kuyper"
> <jameskuyper@verizon.net> wrote:
>
>> On 12/09/2016 12:55 PM, Alla _ wrote:
>> ...
>>> I have found the problem (part of it) - Region settings on my Mac;
>>> changed those for US (although English has been the default language),
>>> relaunched Excel, saved the file in .csv and got a better result (see
>>> below).
>>>
>>> But I still don't understand why some commas are surrounded by quotes,
>>
>> You don't have commas surrounded by quotes - you have commas surrounded
>> by quoted strings. Example: " $1,062 "," $1,252 ". Note, in particular,
>> that these quoted strings contain commas, which must be distinguished
>> from the commas that separate the fields. And that distinction is
>> something you're going to have to achieve with your own code - no
>> built-in facility of C will handle that for you.
>>
>>> and how C will interpret dollar signs, ...
>>
>> C won't do anything special with dollar signs - which means your program
>> is going to have to detect dollar signs  and do whatever special
>> handling is required. That's equally true of the double quote marks.
> [snip]
>
> ... AND ...
>
> we haven't even touched on the effects of "internationalization".

For pity's sake, Lew! :-)

Did you deal with internationalisation issues when you were first trying 
to learn how to use fgets and strchr?

Give the kid a break!

-- 
Richard Heathfield
Email: rjh at cpax dot org dot uk
"Usenet is a strange place" - dmr 29 July 1999
Sig line 4 vacant - apply within

[toc] | [prev] | [next] | [standalone]

#98773

<snip>
> You don't have commas surrounded by quotes - you have commas surrounded 
> by quoted strings. Example: " $1,062 "," $1,252 ". Note, in particular, 
> that these quoted strings contain commas, which must be distinguished 
> from the commas that separate the fields. And that distinction is 
> something you're going to have to achieve with your own code - no 
> built-in facility of C will handle that for you.
> 
> > and how C will interpret dollar signs, ...
> 
> C won't do anything special with dollar signs - which means your program 
> is going to have to detect dollar signs  and do whatever special 
> handling is required. That's equally true of the double quote marks.
> 
> > ... as well as some white space I see
> > in this result; there should be no white spaces in csv file, if I have
> > understood it correctly. ...
> 
> As a general rule, spaces in text strings must be preserved in the csv 
> file. It's also not uncommon to refer to something as "comma separated" 
> when it's actually separated by the combination of a comma followed by a 
> space character. This file seems to contain some comma delimiters that 
> are accompanied by a space character, and others that aren't, so your 
> code is going to have to be able to deal with both possibilities.
> 
> > ... Also, it seems I have to delete all minuses
> > in the original file because as the 4th row from the bottom shows
> > csv reads it as it is usually formatted in Excel (1).
> 
> A better approach is to write your code so that it recognizes the 
> convention that (1) means -1.
> 
> Some of your financial data is apparently stored in a cell with a format 
> string that specifies use of ',' as a delimiter. When you save it in CSV 
> format, it's stored in quotes, to prevent it from being misinterpreted 
> as a delimiting comma.
> 
Thank you very much! I feel ashamed for forgetting basic C concepts, or
rather not forgetting them but somehow assuming that the magic .csv 
extension should have solved many issues; this whole csv thing completely
distracted me :)  After numerous discussions and explanations, I have received
here, including your very helpful one, I see my mistakes; most likely more to 
come, but I am learning. 
I will proceed with pseudocode on paper before writing a program itself.

> I don't currently have access to Excel, but I do have access to 
> LibreOffice Calc, which knows how to read and write files in Excel 
> format and CSV, and generally works similarly to Excel. When I created 
> two cells, one with a currency value formatted as $1,234.56, and the 
> other containing the text "$1,234.56", both cells were saved the same 
> way in the CSV file, which boggles my mind.
> 
<snip>

[toc] | [prev] | [next] | [standalone]

#98584

On 09/12/16 17:07, Alla _ wrote:

<snip>

> Income Statement;2011;2012;2013;2014;2015
> Sales; $1†062 ; $1†252 ; $1†587 ; $1†934 ; $2†519
> Cost of Goods Sold;654    ;814    ;1†009    ;1†190    ;1†499
>   Gross Profit;408    ;438    ;578    ;744    ;1†020
> Selling, General, and Admin Exp;254    ;271    ;364    ;454    ;576
>   Operating Income before Depr;154    ;167    ;214    ;290    ;444
> Depreciation and Amortization;25    ;31    ;38    ;52    ;70
>   Operating Profit;129    ;136    ;176    ;238    ;374
> Interest Expense;4    ;3    ;3    ;1    ;4
> Other Gains and Losses;0    ;7    ;10    ;0    ;-1
>   Pretax Income;125    ;126    ;163    ;237    ;371
> Income Tax Expense;55    ;52    ;65    ;92    ;141
>   Net Income; $70 ; $74 ; $98 ; $145 ; $230
> ;;;;;

Well!

Alla has very kindly sent me the original Excel file, and I cannot begin 
to imagine how Excel has so badly screwed this up. (In fact, I have to 
assume that Alla has made some kind of mistake --- sorry, Alla, but it's 
the most likely explanation.)

I have replied to Alla by email, attaching the CSV file that LibreOffice 
produced. (It took two goes, because first time I forgot to clear a 
check box saying "save cell contents as shown".) Here it is:

Income Statement,2011,2012,2013,2014,2015
Sales,1062,1252,1587,1934,2519
Cost of Goods Sold,654,814,1009,1190,1499
   Gross Profit,408,438,578,744,1020
"Selling, General, and Admin Exp",254,271,364,454,576
   Operating Income before Depr,154,167,214,290,444
Depreciation and Amortization,25,31,38,52,70
   Operating Profit,129,136,176,238,374
Interest Expense,4,3,3,1,4
Other Gains and Losses,0,7,10,0,-1
   Pretax Income,125,126,163,237,371
Income Tax Expense,55,52,65,92,141
   Net Income,70,74,98,145,230

Alla:

As you can see, each row of the spreadsheet is represented by one row of 
the text file.

Each row is broken into fields separated by commas. Where the data 
includes a comma, the field is protected by quotation marks. The only 
example of this in your data is "Selling, General, and Admin Exp".

(If data includes quotation marks too, it gets a little scarier!)

The leading spaces in some of the leftmost fields reflect the leading 
spaces in your data.

In C terms, this is how you would parse the data (and I'm going to gloss 
over the quoted quotation mark rule for now, to keep this a bit simpler):

1) read a line using fgets
2) read each field in turn until you run out of fields, storing as you go.

To read a field:

1) check the first character. Is it a quotation mark? If so, keep 
reading and storing until you come to the next quotation mark. (This is 
the rule that I have over-simplified, but it will work with the data you 
supplied.) When you get to that second quotation mark, you have 
retrieved the whole of that field.
2) If it wasn't a quotation mark, read and store until you come to a 
comma. Don't store the comma.

If the field is supposed to be numeric, use a conversion function such 
as strtol or strtod to convert it.

And that's it. It isn't very difficult, although it gets a lot more 
difficult if you try to do it with scanf.

-- 
Richard Heathfield
Email: rjh at cpax dot org dot uk
"Usenet is a strange place" - dmr 29 July 1999
Sig line 4 vacant - apply within

[toc] | [prev] | [next] | [standalone]

#98587

On Friday, December 9, 2016 at 9:09:16 PM UTC+3, Richard Heathfield wrote:
> On 09/12/16 17:07, Alla _ wrote:
> 
> <snip>
> 
> > Income Statement;2011;2012;2013;2014;2015
> > Sales; $1†062 ; $1†252 ; $1†587 ; $1†934 ; $2†519
> > Cost of Goods Sold;654    ;814    ;1†009    ;1†190    ;1†499
> >   Gross Profit;408    ;438    ;578    ;744    ;1†020
> > Selling, General, and Admin Exp;254    ;271    ;364    ;454    ;576
> >   Operating Income before Depr;154    ;167    ;214    ;290    ;444
> > Depreciation and Amortization;25    ;31    ;38    ;52    ;70
> >   Operating Profit;129    ;136    ;176    ;238    ;374
> > Interest Expense;4    ;3    ;3    ;1    ;4
> > Other Gains and Losses;0    ;7    ;10    ;0    ;-1
> >   Pretax Income;125    ;126    ;163    ;237    ;371
> > Income Tax Expense;55    ;52    ;65    ;92    ;141
> >   Net Income; $70 ; $74 ; $98 ; $145 ; $230
> > ;;;;;
> 
> Well!
> 
> Alla has very kindly sent me the original Excel file, and I cannot begin 
> to imagine how Excel has so badly screwed this up. (In fact, I have to 
> assume that Alla has made some kind of mistake --- sorry, Alla, but it's 
> the most likely explanation.)
> 
> I have replied to Alla by email, attaching the CSV file that LibreOffice 
> produced. (It took two goes, because first time I forgot to clear a 
> check box saying "save cell contents as shown".) Here it is:
> 
> Income Statement,2011,2012,2013,2014,2015
> Sales,1062,1252,1587,1934,2519
> Cost of Goods Sold,654,814,1009,1190,1499
>    Gross Profit,408,438,578,744,1020
> "Selling, General, and Admin Exp",254,271,364,454,576
>    Operating Income before Depr,154,167,214,290,444
> Depreciation and Amortization,25,31,38,52,70
>    Operating Profit,129,136,176,238,374
> Interest Expense,4,3,3,1,4
> Other Gains and Losses,0,7,10,0,-1
>    Pretax Income,125,126,163,237,371
> Income Tax Expense,55,52,65,92,141
>    Net Income,70,74,98,145,230
> 
> Alla:
> 
> As you can see, each row of the spreadsheet is represented by one row of 
> the text file.
> 
> Each row is broken into fields separated by commas. Where the data 
> includes a comma, the field is protected by quotation marks. The only 
> example of this in your data is "Selling, General, and Admin Exp".
> 
> (If data includes quotation marks too, it gets a little scarier!)
> 
> The leading spaces in some of the leftmost fields reflect the leading 
> spaces in your data.
> 
> In C terms, this is how you would parse the data (and I'm going to gloss 
> over the quoted quotation mark rule for now, to keep this a bit simpler):
> 
> 1) read a line using fgets
> 2) read each field in turn until you run out of fields, storing as you go.
> 
> To read a field:
> 
> 1) check the first character. Is it a quotation mark? If so, keep 
> reading and storing until you come to the next quotation mark. (This is 
> the rule that I have over-simplified, but it will work with the data you 
> supplied.) When you get to that second quotation mark, you have 
> retrieved the whole of that field.
> 2) If it wasn't a quotation mark, read and store until you come to a 
> comma. Don't store the comma.
> 
> If the field is supposed to be numeric, use a conversion function such 
> as strtol or strtod to convert it.
> 
> And that's it. It isn't very difficult, although it gets a lot more 
> difficult if you try to do it with scanf.
Thank you very much! I will be back with questions and results. 
And I still have to see my mistakes in converting those files, learn 
the processes, otherwise I will not be able to proceed.

[toc] | [prev] | [next] | [standalone]

#98608

Richard Heathfield <rjh@cpax.org.uk> writes:
[...]
> In C terms, this is how you would parse the data (and I'm going to gloss 
> over the quoted quotation mark rule for now, to keep this a bit simpler):
>
> 1) read a line using fgets
> 2) read each field in turn until you run out of fields, storing as you go.
>
> To read a field:
>
> 1) check the first character. Is it a quotation mark? If so, keep 
> reading and storing until you come to the next quotation mark. (This is 
> the rule that I have over-simplified, but it will work with the data you 
> supplied.) When you get to that second quotation mark, you have 
> retrieved the whole of that field.
> 2) If it wasn't a quotation mark, read and store until you come to a 
> comma. Don't store the comma.

Is that accurate?  For example, given:

zero,"one,two",three

your method would store the 9 characters "one,two", including the
quotation marks, for the second field.  I think it should be the 7
characters one,two without the quotation marks.  Otherwise there would
be no way to represent a field that contains a comma and no quotation
marks.

[...]

-- 
Keith Thompson (The_Other_Keith) kst-u@mib.org  <http://www.ghoti.net/~kst>
Working, but not speaking, for JetHead Development, Inc.
"We must do something.  This is something.  Therefore, we must do this."
    -- Antony Jay and Jonathan Lynn, "Yes Minister"

[toc] | [prev] | [next] | [standalone]

#98816

On 09/12/16 20:16, Keith Thompson wrote:
> Richard Heathfield <rjh@cpax.org.uk> writes:
> [...]
>> In C terms, this is how you would parse the data (and I'm going to gloss
>> over the quoted quotation mark rule for now, to keep this a bit simpler):
>>
>> 1) read a line using fgets
>> 2) read each field in turn until you run out of fields, storing as you go.
>>
>> To read a field:
>>
>> 1) check the first character. Is it a quotation mark? If so, keep
>> reading and storing until you come to the next quotation mark. (This is
>> the rule that I have over-simplified, but it will work with the data you
>> supplied.) When you get to that second quotation mark, you have
>> retrieved the whole of that field.
>> 2) If it wasn't a quotation mark, read and store until you come to a
>> comma. Don't store the comma.
>
> Is that accurate?

Probably not. :-)

> For example, given:
>
> zero,"one,two",three
>
> your method would store the 9 characters "one,two", including the
> quotation marks, for the second field.  I think it should be the 7
> characters one,two without the quotation marks.

Right. So you adjust your start and end accordingly.

> Otherwise there would
> be no way to represent a field that contains a comma and no quotation
> marks.

CSV generally puts quotation marks around a field that contains a comma. 
The question is only whether we intend to strip those off (which 
actually of course we ought to).

The harder task of figuring out a field that, /before/ being written to 
CSV, actually does contain not only commas but quotation marks as well, 
is one that I wouldn't trouble Alla with at this stage.

Lots of people have been telling Alla to take it slowly, but these are 
very often the same people who are also raising all the complications 
that a fully robust commercial-quality product must deal with. It would 
be a kindness to the student if we could allow her to proceed at the 
slower pace that we keep urging her to adopt.

-- 
Richard Heathfield
Email: rjh at cpax dot org dot uk
"Usenet is a strange place" - dmr 29 July 1999
Sig line 4 vacant - apply within

[toc] | [prev] | [next] | [standalone]

#98829

On Sunday, December 11, 2016 at 9:56:36 AM UTC-6, Richard Heathfield wrote:

> CSV generally puts quotation marks around a field that contains a comma. 
> The question is only whether we intend to strip those off (which 
> actually of course we ought to).

I've been hesitating to add an additional distraction
but the whole CSV format is widely overestimated. 
For many smaller tasks (including this one, I truly hope)
a more flexible notion of Delimiter-Separated Values
is simpler to get working.

The idea is "if your data has 'commas', delimit with
something else", FVVO 'commas'. If you delimit fields
with the pipe character | then nothing needs to be
escaped with quotes and the rats nest of quoted quotes
doesn't arise....

... until you want the pipe character in your data, 
but that may be never if you can help it. If you can't
help it, then at least you've deferred this headache 
until then.

I believe there is (or at least once was) an option
in Excel to File->Export with more choices than the
File->SaveAs option. In the dialog box there you could
select a general DSV format and choose the delimiter
you want to use.

> The harder task of figuring out a field that, /before/ being written to 
> CSV, actually does contain not only commas but quotation marks as well, 
> is one that I wouldn't trouble Alla with at this stage.

Agreed.

> Lots of people have been telling Alla to take it slowly, but these are 
> very often the same people who are also raising all the complications 
> that a fully robust commercial-quality product must deal with. It would 
> be a kindness to the student if we could allow her to proceed at the 
> slower pace that we keep urging her to adopt.

Again agreed.

[toc] | [prev] | [next] | [standalone]

#98830

On 11/12/16 16:27, luser droog wrote:
> On Sunday, December 11, 2016 at 9:56:36 AM UTC-6, Richard Heathfield wrote:
>
>> CSV generally puts quotation marks around a field that contains a comma.
>> The question is only whether we intend to strip those off (which
>> actually of course we ought to).
>
> I've been hesitating to add an additional distraction
> but the whole CSV format is widely overestimated.
> For many smaller tasks (including this one, I truly hope)
> a more flexible notion of Delimiter-Separated Values
> is simpler to get working.

One word: Specification.

-- 
Richard Heathfield
Email: rjh at cpax dot org dot uk
"Usenet is a strange place" - dmr 29 July 1999
Sig line 4 vacant - apply within

[toc] | [prev] | [next] | [standalone]

#98886

Richard Heathfield <rjh@cpax.org.uk> writes:
[...]
> Lots of people have been telling Alla to take it slowly, but these are 
> very often the same people who are also raising all the complications 
> that a fully robust commercial-quality product must deal with. It would 
> be a kindness to the student if we could allow her to proceed at the 
> slower pace that we keep urging her to adopt.

I agree, and I've been guilty of that myself.  I've at least tried to
indicate which considerations that can be skipped for now, and I'll try
even harder to do that if I fail to resist the temptation to go off on
yet another tangent.

-- 
Keith Thompson (The_Other_Keith) kst-u@mib.org  <http://www.ghoti.net/~kst>
Working, but not speaking, for JetHead Development, Inc.
"We must do something.  This is something.  Therefore, we must do this."
    -- Antony Jay and Jonathan Lynn, "Yes Minister"

[toc] | [prev] | [next] | [standalone]

#99016

On Sunday, December 11, 2016 at 11:40:17 PM UTC+3, Keith Thompson wrote:
> Richard Heathfield <rjh@cpax.org.uk> writes:
> [...]
> > Lots of people have been telling Alla to take it slowly, but these are 
> > very often the same people who are also raising all the complications 
> > that a fully robust commercial-quality product must deal with. It would 
> > be a kindness to the student if we could allow her to proceed at the 
> > slower pace that we keep urging her to adopt.
> 
> I agree, and I've been guilty of that myself.  I've at least tried to
> indicate which considerations that can be skipped for now, and I'll try
> even harder to do that if I fail to resist the temptation to go off on
> yet another tangent.
> 
That is why I have chosen an relatively simple task for a project, but 
the one that will allow me to work through concepts I have already 
learned, deepen my knowledge on some of those, acquire additional
skills, and mainly proceed learning how to think programmatically,
i.e. correctly (as I have said many times it is my weakest point).
But, please, answer my questions on mallocing I have asked above.
No, I won't attempt creating any sophisticated program, it's out of 
question for now, but this memory issues sound reasonable for my 
task, even given limitations of the scope of my tiny program. 

[toc] | [prev] | [next] | [standalone]

#98566

On Fri, 9 Dec 2016 07:06:13 -0800 (PST), Alla _
<modelling.data@gmail.com> wrote:

Why are you chopping off attributions?  

><snip>
>> Step 1: save the Excel spreadsheet data in .csv format.
>> Step 2: LOOK at the saved file. Learn what .csv format is.
>
>Could you, please, do me a huge favour and believe that there is not need to 
>continuously add notes like "look at the saved file" - it is an over exaggeration, 
>which might useful and suitable for people just learning to think by themselves 
>at the age, say, 2 year old )))

Look at Richard's step 2 again

>To make sure I am on the right track (although this is yet not C): I have saved my 
>excel test file in csv format simply by Save as in Excel. When I opened it, I didn't see
>any thing different visually - I was expecting something like data, data, data etc, i.e.
>comma separated entries, where commas should have been used instead of excel
>columns. 

If you had done what he suggested, you would realize that your
expectations as expressed here are incomplete.

>And another question. I have data in the following format:
>years|  year1      |      year2 |       year2
>item1|  numbers| numbers |  numbers

What would your expectation be if item1 contained the text "totals,
first six months only" without the quotation marks?

>item2|  numbers| numbers |  numbers
>etc

-- 
Remove del for email

[toc] | [prev] | [next] | [standalone]

Page 2 of 4 — ← Prev page 1 [2] 3 4  Next page →

Back to top | Article view | comp.lang.c

csiph-web