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

Groups > comp.lang.c > #98587

Re: Using C to convert excel spreadsheet into mysql

Newsgroups comp.lang.c
Date 2016-12-09 10:15 -0800
References (1 earlier) <o2e3j5$v0r$1@dont-email.me> <70dc8aa0-d591-41c2-8d56-329162cda0d6@googlegroups.com> <o2ejqc$plq$1@dont-email.me> <7b8881c5-ab07-48e7-bbbc-815ac078ebe6@googlegroups.com> <o2ertk$s1k$1@dont-email.me>
Message-ID <f33347c9-e64b-443e-abed-521286612ced@googlegroups.com> (permalink)
Subject Re: Using C to convert excel spreadsheet into mysql
From Alla _ <modelling.data@gmail.com>

Show all headers | View raw

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.

Back to comp.lang.c | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

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

csiph-web