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 1 of 4  [1] 2 3 4  Next page →

#98476 — Using C to convert excel spreadsheet into mysql

Hello!

I will be grateful for your help on this matter: I have searched google, and have found numerous 
suggestions how to convert excel spreadsheet into mysql table with php, but I need to do this
using C. Please, without providing the ready solution, let me know if it is at all possible using 
reasonably basic tools appropriate for a student? If yes, I will continue the search, and will 
be grateful for tips on this issue (but, please, do not write a code for me; please). 

Thank you very much!

[toc] | [next] | [standalone]

#98477

On 09/12/16 10:50, Alla _ wrote:
> Hello!
>
> I will be grateful for your help on this matter: I have searched google, and have found numerous
> suggestions how to convert excel spreadsheet into mysql table with php, but I need to do this
> using C.

That little word "need" bothers me. Is this another of those C courses 
that so impedes your learning?

> Please, without providing the ready solution, let me know if it is at all possible using
> reasonably basic tools appropriate for a student?

There is a way to do this using standard C. You might not like it, but 
it will work.

Step 1: save the Excel spreadsheet data in .csv format.
Step 2: LOOK at the saved file. Learn what .csv format is.
Step 3: write C code to read the .csv file and produce SQL statements as 
output.
Step 4: save the SQL statements in a script, and run the script through 
mysql.

You told me not to write the code, so I won't. But I'll give you some hints:

a) you'll get a lot further with fgets and strtok than you will with fscanf.
b) use a while loop, not a do/while loop.
c) don't even think about trying to produce mysql binaries yourself. 
Create the SQL statements using ordinary printf, and then run the 
resulting statements. It's ludicrously simple to do, whereas producing 
mysql binaries is ludicrously complicated.

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

#98481

On Friday, December 9, 2016 at 2:14:06 PM UTC+3, Richard Heathfield wrote:
> On 09/12/16 10:50, Alla _ wrote:
> > Hello!
> >
> > I will be grateful for your help on this matter: I have searched google, and have found numerous
> > suggestions how to convert excel spreadsheet into mysql table with php, but I need to do this
> > using C.
> 
> That little word "need" bothers me. Is this another of those C courses 
> that so impedes your learning?
> 
> > Please, without providing the ready solution, let me know if it is at all possible using
> > reasonably basic tools appropriate for a student?
> 
> There is a way to do this using standard C. You might not like it, but 
> it will work.
> 
> Step 1: save the Excel spreadsheet data in .csv format.
> Step 2: LOOK at the saved file. Learn what .csv format is.
> Step 3: write C code to read the .csv file and produce SQL statements as 
> output.
> Step 4: save the SQL statements in a script, and run the script through 
> mysql.
> 
> You told me not to write the code, so I won't. But I'll give you some hints:
> 
> a) you'll get a lot further with fgets and strtok than you will with fscanf.
> b) use a while loop, not a do/while loop.
> c) don't even think about trying to produce mysql binaries yourself. 
> Create the SQL statements using ordinary printf, and then run the 
> resulting statements. It's ludicrously simple to do, whereas producing 
> mysql binaries is ludicrously complicated.
> 
Thank you very much for helping out with this plan. I will try to implement 
this. 
As to your question about courses - no, it's nothing new. Remember I have 
mentioned that I need to write a student's program? This is the final project
for that wonderful cs introductory course, so much chastised by you ))) Of course, 
I could make my life much easier and just create some simple web site and get 
over with it, but as far as I have chosen to proceed learning C, I have decided to write a 
program using C. 

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

#98561

On Fri, 9 Dec 2016 03:50:29 -0800 (PST), Alla _
<modelling.data@gmail.com> wrote:

>On Friday, December 9, 2016 at 2:14:06 PM UTC+3, Richard Heathfield wrote:

<snip - sorry Richard>

>Thank you very much for helping out with this plan. I will try to implement 
>this. 
>As to your question about courses - no, it's nothing new. Remember I have 
>mentioned that I need to write a student's program? This is the final project
>for that wonderful cs introductory course, so much chastised by you ))) Of course, 
>I could make my life much easier and just create some simple web site and get 
>over with it, but as far as I have chosen to proceed learning C, I have decided to write a 
>program using C. 

I wonder why you think creating a web site will somehow convert an
excel spreadsheet into a mysql file.

-- 
Remove del for email

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

#98570

On Friday, December 9, 2016 at 7:43:22 PM UTC+3, Barry Schwarz wrote:
> On Fri, 9 Dec 2016 03:50:29 -0800 (PST), Alla _
> <modelling.data@gmail.com> wrote:
> 
> >On Friday, December 9, 2016 at 2:14:06 PM UTC+3, Richard Heathfield wrote:
> 
> <snip - sorry Richard>
> 
> >Thank you very much for helping out with this plan. I will try to implement 
> >this. 
> >As to your question about courses - no, it's nothing new. Remember I have 
> >mentioned that I need to write a student's program? This is the final project
> >for that wonderful cs introductory course, so much chastised by you ))) Of course, 
> >I could make my life much easier and just create some simple web site and get 
> >over with it, but as far as I have chosen to proceed learning C, I have decided to write a 
> >program using C. 
> 
> I wonder why you think creating a web site will somehow convert an
> excel spreadsheet into a mysql file.
I didn't mean that ) I was saying that creating a simple website with php
and html will be a much simpler task; nothing to do with excel or the project
I have chosen. 
> 
> -- 
> Remove del for email

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

#98515

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

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. 

And another question. I have data in the following format:
years|  year1      |      year2 |       year2
item1|  numbers| numbers |  numbers
item2|  numbers| numbers |  numbers
etc
And I will need to use both year1, etc, and item1, item 2, etc, indexes to access
particular numbers in certain column and rows. Will I be able to do that using 
C and csv file? 
Sorry for these C-unrelated questions. 
<snip>

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

#98536

On 09/12/16 15:06, Alla _ wrote:
> <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 )))

Sorry, Alla, but we need to be clear about what a CSV file is. I try 
hard not to sound too terribly condescending, but I'm aware that I don't 
always succeed and I apologise for the lapse. I'll do my best not to 
continue in that vein, but the precise details do matter, and it's as 
well to be very clear about what they are.

In this case, if I'm not very much mistaken, I didn't give you *enough* 
detail, as it seems from what follows (see below) that you may not have 
looked at the CSV file with any tool other than Excel. Excel is fine for 
creating CSV files, but it doesn't show you their format --- instead, it 
shows the data in rows and columns.

> To make sure I am on the right track (although this is yet not C):

Good! Actually, it sort of is C, in the sense that you're preparing the 
data that the C program will use, so it's vitally important to 
understand the format of that data.

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

And that's precisely what you should have seen. If you didn't see that, 
the question is /why/ you didn't see that. Which program did you use for 
inspecting the data? CSV is a text format, and a text editor is the 
perfect tool for looking at it if the format is what you care about (as 
in this case). If you looked at it /in Excel/, you wouldn't have seen 
anything different --- you'd have just seen rows and columns, and no 
commas. So --- did you use a text editor to look at the data?


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

That isn't comma-separated values format. What you should have is:

years,year1,year2,year2 (and are you sure about that last one?)
item1,numbers,numbers,numbers
item2,numbers,numbers,numbers

> etc
> And I will need to use both year1, etc, and item1, item 2, etc, indexes to access
> particular numbers in certain column and rows. Will I be able to do that using
> C and csv file?

Well, I certainly could, so I don't see why you couldn't, but the 
question is how you represent that data within your program, and that 
depends on how you wish to process it.

For example, do you want to read the whole file into memory in one go, 
or do you want to process one record at a time (the simpler approach)? 
Do you want to access a given column using an alphanumeric identifier, 
or by a simple numeric index (the simpler approach)? These are the kinds 
of questions you will have to answer before you can design your C program.

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

#98569

On Friday, December 9, 2016 at 6:51:00 PM UTC+3, Richard Heathfield wrote:
> On 09/12/16 15:06, Alla _ wrote:
> > <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 )))
> 
> Sorry, Alla, but we need to be clear about what a CSV file is. I try 
> hard not to sound too terribly condescending, but I'm aware that I don't 
> always succeed and I apologise for the lapse. I'll do my best not to 
> continue in that vein, but the precise details do matter, and it's as 
> well to be very clear about what they are.
> 
Oh, please, do not worry - I didn't mean that you sound condescending; 
I was just worried about the time you spend writing some obvious things.
On the other hand, this sort of obvious things are useful from time to time,
and, if not you, I would bring them up. Also, this approach shows your 
thought process, hence allows to learn and acquire, or recall, or not to 
forget, good habits. 
And, as I have mentioned many many times, I would never gripe about
being chastised as I have been here numerous times by everyone 
helping me - each time it was for an absolutely legitimate reason;
and that is a very helpful approach. Anyway, back to C. 

> In this case, if I'm not very much mistaken, I didn't give you *enough* 
> detail, as it seems from what follows (see below) that you may not have 
> looked at the CSV file with any tool other than Excel. Excel is fine for 
> creating CSV files, but it doesn't show you their format --- instead, it 
> shows the data in rows and columns.
> 
> > To make sure I am on the right track (although this is yet not C):
> 
> Good! Actually, it sort of is C, in the sense that you're preparing the 
> data that the C program will use, so it's vitally important to 
> understand the format of that data.
> 
> > 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.
> 
> And that's precisely what you should have seen. If you didn't see that, 
> the question is /why/ you didn't see that. Which program did you use for 
> inspecting the data? CSV is a text format, and a text editor is the 
> perfect tool for looking at it if the format is what you care about (as 
> in this case). If you looked at it /in Excel/, you wouldn't have seen 
> anything different --- you'd have just seen rows and columns, and no 
> commas. So --- did you use a text editor to look at the data?
> 
Of course, I have not - have completely forgotten to do that, and opened it only
in Excel. 
> 
> >
> > And another question. I have data in the following format:
> > years|  year1      |      year2 |       year2
> > item1|  numbers| numbers |  numbers
> > item2|  numbers| numbers |  numbers
> 
> That isn't comma-separated values format. What you should have is:
> 
> years,year1,year2,year2 (and are you sure about that last one?)
> item1,numbers,numbers,numbers
> item2,numbers,numbers,numbers
> 
Yes. Opening the file in Text editor (TextEdit) showed some unexpected
formatting. It seems (I still have to read about it) that I have to format the
original excel file to delete all spaces in numbers. Below is the bizarre result:
Spaces in Sales values and in some other places are substituted by... a Cross?! )
Sales and net income values are all right aligned in original .xlsx; all other 
values are centred. Well, seems that there are many nuances that stay on my
way )

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

> > etc
> > And I will need to use both year1, etc, and item1, item 2, etc, indexes to access
> > particular numbers in certain column and rows. Will I be able to do that using
> > C and csv file?
> 
> Well, I certainly could, so I don't see why you couldn't, but the 
> question is how you represent that data within your program, and that 
> depends on how you wish to process it.
> 
> For example, do you want to read the whole file into memory in one go, 
> or do you want to process one record at a time (the simpler approach)? 
> Do you want to access a given column using an alphanumeric identifier, 
> or by a simple numeric index (the simpler approach)? These are the kinds 
> of questions you will have to answer before you can design your C program.
> 
Yes, I have already written (on paper) these types of questions, and few versions of a plan;
but I still have to work on this. 
> -- 
> 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]

#98573

On 09/12/16 17:07, Alla _ wrote:
> On Friday, December 9, 2016 at 6:51:00 PM UTC+3, Richard Heathfield wrote:
>> So --- did you use a text editor to look at the data?
>>
> Of course, I have not - have completely forgotten to do that, and opened it only
> in Excel.

So the advice was not only not obvious but also not heeded. (Although, 
in your defence, I ought to have told you to use a text editor. I just 
assumed you would! My mistake.)

>> What you should have is:
>>
>> years,year1,year2,year2 (and are you sure about that last one?)
>> item1,numbers,numbers,numbers
>> item2,numbers,numbers,numbers
>>
> Yes. Opening the file in Text editor (TextEdit) showed some unexpected
> formatting.

If it's a text editor and it works, it doesn't matter which one.

> It seems (I still have to read about it) that I have to format the
> original excel file to delete all spaces in numbers.

Uh, no, you should not have to do that.

> Below is the bizarre result:
> Spaces in Sales values and in some other places are substituted by... a Cross?! )
> Sales and net income values are all right aligned in original .xlsx; all other
> values are centred.

In a CSV file, there is no centre, no right-align, no bold, no 10-point, 
no Arial - just text, in whatever font your editor uses.


  Well, seems that there are many nuances that stay on my
> way )
>
> Income Statement;2011;2012;2013;2014;2015
> Sales; $1†062 ; $1†252 ; $1†587 ; $1†934 ; $2†519

All right, that's bizarre. Could you please email the spreadsheet (the 
.xlsx file) to me at rjh@cpax.org.uk ? I'd like to see what on Earth is 
going on here. And why are there semicolons instead of commas? That is 
very weird data indeed. I'd like to see the original, and it isn't 
practical to do that in Usenet, hence the request for an email.

<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

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

#98576

On Friday, December 9, 2016 at 8:23:30 PM UTC+3, Richard Heathfield wrote:
> On 09/12/16 17:07, Alla _ wrote:
> > On Friday, December 9, 2016 at 6:51:00 PM UTC+3, Richard Heathfield wrote:
> >> So --- did you use a text editor to look at the data?
> >>
> > Of course, I have not - have completely forgotten to do that, and opened it only
> > in Excel.
> 
> So the advice was not only not obvious but also not heeded. (Although, 
> in your defence, I ought to have told you to use a text editor. I just 
> assumed you would! My mistake.)
> 
> >> What you should have is:
> >>
> >> years,year1,year2,year2 (and are you sure about that last one?)
> >> item1,numbers,numbers,numbers
> >> item2,numbers,numbers,numbers
> >>
> > Yes. Opening the file in Text editor (TextEdit) showed some unexpected
> > formatting.
> 
> If it's a text editor and it works, it doesn't matter which one.
> 
> > It seems (I still have to read about it) that I have to format the
> > original excel file to delete all spaces in numbers.
> 
> Uh, no, you should not have to do that.
> 
> > Below is the bizarre result:
> > Spaces in Sales values and in some other places are substituted by... a Cross?! )
> > Sales and net income values are all right aligned in original .xlsx; all other
> > values are centred.
> 
> In a CSV file, there is no centre, no right-align, no bold, no 10-point, 
> no Arial - just text, in whatever font your editor uses.
> 
Yes, yes; I have said that alignment is in the original file.
> 
>   Well, seems that there are many nuances that stay on my
> > way )
> >
> > Income Statement;2011;2012;2013;2014;2015
> > Sales; $1†062 ; $1†252 ; $1†587 ; $1†934 ; $2†519
> 
> All right, that's bizarre. Could you please email the spreadsheet (the 
> .xlsx file) to me at rjh@cpax.org.uk ? I'd like to see what on Earth is 
> going on here. And why are there semicolons instead of commas? That is 
> very weird data indeed. I'd like to see the original, and it isn't 
> practical to do that in Usenet, hence the request for an email.
I'd love to. Thank you very much!
> 
> <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

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

#98580

On Friday December 9 2016 12:23, in comp.lang.c, "Richard Heathfield"
<rjh@cpax.org.uk> wrote:

> On 09/12/16 17:07, Alla _ wrote:
[snip]
>> Below is the bizarre result:
>> Spaces in Sales values and in some other places are substituted by... a
>> Cross?! ) Sales and net income values are all right aligned in original
>> .xlsx; all other values are centred.
> 
> In a CSV file, there is no centre, no right-align, no bold, no 10-point,
> no Arial - just text, in whatever font your editor uses.
> 
> 
>   Well, seems that there are many nuances that stay on my
>> way )
>>
>> Income Statement;2011;2012;2013;2014;2015
>> Sales; $1†062 ; $1†252 ; $1†587 ; $1†934 ; $2†519
> 
> All right, that's bizarre. Could you please email the spreadsheet (the
> .xlsx file) to me at rjh@cpax.org.uk ? I'd like to see what on Earth is
> going on here. And why are there semicolons instead of commas? That is
> very weird data indeed. I'd like to see the original, and it isn't
> practical to do that in Usenet, hence the request for an email.

Richard, there may be nothing unusual in the excel spreadsheet.

When you export a spreadsheet to CSV, you can tell Excel to use an alternate
delimiter other than ','. I've often seen tab-delimited files created this
way (IIRC, Excel offers a "delimit using tab" option in it's CSV export).

It may be that Alla's Excel configuration has been set up to use semicolons as
delimiters, and you won't find that in the spreadsheet itself.

Alla, for what it's worth, there is no single, fixed format for "comma
separated files". A CSV is easy to create, but difficult to read, because
there are so many variations. Some variations that I can think of are:
- text fields are delimited by commas 
  (...,text field,...)
- text fields are delimited by doublequotes nested within commas
  (...,"text field"...)
  or
  (...,"text field, with comma",...)
- fields are delimited with commas
  (field,another_field)
- fields are delimited with tab ('\t') characters
  (field        another_field)
- fields are delimited with solidus ('|') characters
  (field|another_field)
- integer numbers are expressed with explicit decimal places
  (...,1.0000,...)
- integer numbers are expressed as integer numbers
  (....1,...)

I'm sure that there are many more variations.
You might wish to read (at least) Wikipedia's writeup on the CSV format
  https://en.wikipedia.org/wiki/Comma-separated_values

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

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

#98583

On Friday, December 9, 2016 at 8:43:37 PM UTC+3, Lew Pitcher wrote:
> On Friday December 9 2016 12:23, in comp.lang.c, "Richard Heathfield"
> <rjh@cpax.org.uk> wrote:
> 
> > On 09/12/16 17:07, Alla _ wrote:
> [snip]
> >> Below is the bizarre result:
> >> Spaces in Sales values and in some other places are substituted by... a
> >> Cross?! ) Sales and net income values are all right aligned in original
> >> .xlsx; all other values are centred.
> > 
> > In a CSV file, there is no centre, no right-align, no bold, no 10-point,
> > no Arial - just text, in whatever font your editor uses.
> > 
> > 
> >   Well, seems that there are many nuances that stay on my
> >> way )
> >>
> >> Income Statement;2011;2012;2013;2014;2015
> >> Sales; $1†062 ; $1†252 ; $1†587 ; $1†934 ; $2†519
> > 
> > All right, that's bizarre. Could you please email the spreadsheet (the
> > .xlsx file) to me at rjh@cpax.org.uk ? I'd like to see what on Earth is
> > going on here. And why are there semicolons instead of commas? That is
> > very weird data indeed. I'd like to see the original, and it isn't
> > practical to do that in Usenet, hence the request for an email.
> 
> Richard, there may be nothing unusual in the excel spreadsheet.
> 
> When you export a spreadsheet to CSV, you can tell Excel to use an alternate
> delimiter other than ','. I've often seen tab-delimited files created this
> way (IIRC, Excel offers a "delimit using tab" option in it's CSV export).
> 
> It may be that Alla's Excel configuration has been set up to use semicolons as
> delimiters, and you won't find that in the spreadsheet itself.
> 
> Alla, for what it's worth, there is no single, fixed format for "comma
> separated files". A CSV is easy to create, but difficult to read, because
> there are so many variations. Some variations that I can think of are:
> - text fields are delimited by commas 
>   (...,text field,...)
> - text fields are delimited by doublequotes nested within commas
>   (...,"text field"...)
>   or
>   (...,"text field, with comma",...)
> - fields are delimited with commas
>   (field,another_field)
> - fields are delimited with tab ('\t') characters
>   (field        another_field)
> - fields are delimited with solidus ('|') characters
>   (field|another_field)
> - integer numbers are expressed with explicit decimal places
>   (...,1.0000,...)
> - integer numbers are expressed as integer numbers
>   (....1,...)
> 
> I'm sure that there are many more variations.
Oh, I see. Thank you very much. I am worried about these nuances only
because I think how C will interpret all that, and how I will be able to extract
data I need. 
> You might wish to read (at least) Wikipedia's writeup on the CSV format
>   https://en.wikipedia.org/wiki/Comma-separated_values
> 
> -- 
> Lew Pitcher
> "In Skills, We Trust"
> PGP public key available upon request

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

#98593

On 12/9/2016 7:00 PM, Alla _ wrote:
>> I'm sure that there are many more variations.
> Oh, I see. Thank you very much. I am worried about these nuances only
> because I think how C will interpret all that, and how I will be able to extract
> data I need.
In fact C by itself would not "interpret" all that, it will process any 
input according to the rules of fscanf or strtok (whichever you choose), 
and any logic above that is entirely up to you.
This is why you need to have clear and accurate the format you are going 
to process.

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

#98588

On 09/12/16 17:43, Lew Pitcher wrote:
> On Friday December 9 2016 12:23, in comp.lang.c, "Richard Heathfield"
> <rjh@cpax.org.uk> wrote:
>
<snip>
>> I'd like to see the original, and it isn't
>> practical to do that in Usenet, hence the request for an email.
>
> Richard, there may be nothing unusual in the excel spreadsheet.

Which is precisely why I asked her to send it.

> When you export a spreadsheet to CSV, you can tell Excel to use an alternate
> delimiter other than ','. I've often seen tab-delimited files created this
> way (IIRC, Excel offers a "delimit using tab" option in it's CSV export).

Tab is in fact my preferred option (because very few people try to embed 
tabs in string cells!), but (a) the problem seems to call for commas, 
and (b) commas survive Usenet a little better than tabs do.

<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

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

#98582

On Friday, December 9, 2016 at 8:23:30 PM UTC+3, Richard Heathfield wrote:
> On 09/12/16 17:07, Alla _ wrote:
> > On Friday, December 9, 2016 at 6:51:00 PM UTC+3, Richard Heathfield wrote:
> >> So --- did you use a text editor to look at the data?
> >>
> > Of course, I have not - have completely forgotten to do that, and opened it only
> > in Excel.
> 
> So the advice was not only not obvious but also not heeded. (Although, 
> in your defence, I ought to have told you to use a text editor. I just 
> assumed you would! My mistake.)
> 
> >> What you should have is:
> >>
> >> years,year1,year2,year2 (and are you sure about that last one?)
> >> item1,numbers,numbers,numbers
> >> item2,numbers,numbers,numbers
> >>
> > Yes. Opening the file in Text editor (TextEdit) showed some unexpected
> > formatting.
> 
> If it's a text editor and it works, it doesn't matter which one.
> 
> > It seems (I still have to read about it) that I have to format the
> > original excel file to delete all spaces in numbers.
> 
> Uh, no, you should not have to do that.
> 
> > Below is the bizarre result:
> > Spaces in Sales values and in some other places are substituted by... a Cross?! )
> > Sales and net income values are all right aligned in original .xlsx; all other
> > values are centred.
> 
> In a CSV file, there is no centre, no right-align, no bold, no 10-point, 
> no Arial - just text, in whatever font your editor uses.
> 
> 
>   Well, seems that there are many nuances that stay on my
> > way )
> >
> > Income Statement;2011;2012;2013;2014;2015
> > Sales; $1†062 ; $1†252 ; $1†587 ; $1†934 ; $2†519
> 
> All right, that's bizarre. Could you please email the spreadsheet (the 
> .xlsx file) to me at rjh@cpax.org.uk ? I'd like to see what on Earth is 
> going on here. And why are there semicolons instead of commas? That is 
> very weird data indeed. I'd like to see the original, and it isn't 
> practical to do that in Usenet, hence the request for an email.
> 
> <snip>
> 
<snip>
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,
and how C will interpret dollar signs, 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. 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). 

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]

#98591

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

<snip>

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

So far so good.

>
> But I still don't understand why some commas are surrounded by quotes,

The comma separates fields. But if you have a field in which a comma is 
part of the data, how do you tell? Answer: protect the whole field by 
putting quotes around it, as a sign that any commas between the quotes 
should not be treated as field delimiters.

> and how C will interpret dollar signs,

Configure the save options so as to save just the data. I made this 
mistake on my first attempt: I forgot to clear a checkbox marked 
something like "save cell contents as shown", so LibreOffice stuffed 
those dollar signs right in there. In my second attempt, I corrected this.

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

Only the ones that are inherent in the data. Thus, for "Income 
Statement" there should be a space between the two words. But there 
shouldn't be any leading, trailing, or intervening spaces in numeric 
fields. You're right about that. The solution is to tell Excel not to 
save the dollars and so on. Look for a checkbox on the filter settings 
marked "cell contents as shown", and un-tick it.

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

Again, getting rid of "save cell contents as shown" will fix this.

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

#98761

<snip>
> Only the ones that are inherent in the data. Thus, for "Income 
> Statement" there should be a space between the two words. But there 
> shouldn't be any leading, trailing, or intervening spaces in numeric 
> fields. You're right about that. The solution is to tell Excel not to 
> save the dollars and so on. Look for a checkbox on the filter settings 
> marked "cell contents as shown", and un-tick it.
> 
> > 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).
> 
> Again, getting rid of "save cell contents as shown" will fix this.
> 
I am sorry for asking only now, but I have saved the book with a 
raw data and still have numerous commas at the end, although
I have deleted a few rows as well. I wonder what do these commas 
indicate - you do not have them in your csv file. 

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

#98770

Alla _ <modelling.data@gmail.com> writes:
> <snip>
>> Only the ones that are inherent in the data. Thus, for "Income 
>> Statement" there should be a space between the two words. But there 
>> shouldn't be any leading, trailing, or intervening spaces in numeric 
>> fields. You're right about that. The solution is to tell Excel not to 
>> save the dollars and so on. Look for a checkbox on the filter settings 
>> marked "cell contents as shown", and un-tick it.
>> 
>> > 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).
>> 
>> Again, getting rid of "save cell contents as shown" will fix this.
>> 
> I am sorry for asking only now, but I have saved the book with a 
> raw data and still have numerous commas at the end, although
> I have deleted a few rows as well. I wonder what do these commas 
> indicate - you do not have them in your csv file. 

Copying from your followup:

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

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

#98780

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

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

#98884

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.

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

Page 1 of 4  [1] 2 3 4  Next page →

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

csiph-web