Thread: Help with getting data out of an excel file

So know the field position meanings and maximum sizes:

Code:

Max line size 172 (with newline).
                             max field length
0   userid                   7
1   age                      3
2   dob_day                  7
3   dob_year                 8
4   dob_month                9
5   gender                   6
6   tenure                   6
7   friend_count            12
8   friendships_initiated   21
9   likes                    5
10  likes_received          14
11  mobile_likes            12
12  mobile_likes_received   21
13  www_likes                9
14  www_likes_received      18

You have some sample data

Code:

    
2094382    14    19    1999    11    male    266   0    0    0    0    0    0    0    0
1192601    14    2     1999    11    female  6     0    0    0    0    0    0    0    0
2083884    14    16    1999    11    male    13    0    0    0    0    0    0    0    0
1203168    14    25    1999    12    female  93    0    0    0    0    0    0    0    0
1733186    14    4     1999    12    male    82    0    0    0    0    0    0    0    0
1524765    14    1     1999    12    male    15    0    0    0    0    0    0    0    0
1136133    13    14    2000    1     male    12    0    0    0    0    0    0    0    0
1680361    13    4     2000    1     female  0     0    0    0    0    0    0    0    0
1365174    13    1     2000    1     male    81    0    0    0    0    0    0    0    0

A couple of problems. What's up with all those zeroes? It may be okay, but it seems strange.
Also, the max field size for those fields seem far too large. Presumably mobile_likes_received is a number, but it's max field size is 21. It could be mostly spaces, though. You should take a look at that one with:

Code:

awk -F'\t' 'length($15) == 21'

Anyway, with the info above and the example program you should be able to get the job done.

Remember that you will need to skip the first line since it's just the field names. That just requires an extra call to fgets before the loop.

Then you'd want to add an if inside the tokenizing loop. For example, to count the number of females:

Code:

    if (i == 5 && strcmp(tok, "female") == 0)  // field at offset 5 is "gender"
        females++;   // a counter that we initialized to 0 and will print at the end

So to make it more clearer, my goal is to extract and print out the following statistic figures grouped by the user genders(ie. "male' and 'female'):
-Valid entry count.
-Highest likes received count.
-Lowest likes received count.
-Likes received count total (the sum of all the like received count).
-Likes received count average.
-Likes received count median.

I would appreciate if someone could help me with the codes. I have been trying to figure out how to code it for 2 days straight, but I still have no idea how to do it. Many thanks in advance.

Here's a cleaned-up version of the example program. Look up any library functions you don't know and figure out how it works.

Code:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

#define MAXLINE 1000

int main()
{
    FILE *f = fopen("pseudo_facebook.tsv", "r");
    if (!f) { perror("fopen"); exit(EXIT_FAILURE); }

    char line[MAXLINE];

    fgets(line, MAXLINE, f); // consume the header line

    while (fgets(line, MAXLINE, f) != NULL)
    {
        int i = 0;
        char *tok = strtok(line, "\t\n");
        while (tok)
        {
            if (i == 5) // the field at offset 5 (gender)
            {
                // do what you want with it
                printf("%s\n", tok);
            }

            tok = strtok(NULL, "\t\n");
            i++;
        }
    }

    fclose(f);
    return 0;
}

Then modify it to do all your stats except for the median. You don't need any structs or lists or anything for that. Just some variables to hold the counts.

Post that code when you're done.

BTW, I was confused about the large field value lengths in the data (in post #16). Those were obviously just the lengths of the field names themselves! So that max-field-length data is useless, but you don't really need it.

Originally Posted by algorism

Here's a cleaned-up version of the example program. Look up any library functions you don't know and figure out how it works.

Code:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

#define MAXLINE 1000

int main()
{
    FILE *f = fopen("pseudo_facebook.tsv", "r");
    if (!f) { perror("fopen"); exit(EXIT_FAILURE); }

    char line[MAXLINE];

    fgets(line, MAXLINE, f); // consume the header line

    while (fgets(line, MAXLINE, f) != NULL)
    {
        int i = 0;
        char *tok = strtok(line, "\t\n");
        while (tok)
        {
            if (i == 5) // the field at offset 5 (gender)
            {
                // do what you want with it
                printf("%s\n", tok);
            }

            tok = strtok(NULL, "\t\n");
            i++;
        }
    }

    fclose(f);
    return 0;
}

Then modify it to do all your stats except for the median. You don't need any structs or lists or anything for that. Just some variables to hold the counts.

Post that code when you're done.

BTW, I was confused about the large field value lengths in the data (in post #16). Those were obviously just the lengths of the field names themselves! So that max-field-length data is useless, but you don't really need it.

Thanks for the help, but using structures is one of the requirement of my assignment. I have to store certain columns from the excel file somewhere, and I need a struct for it.