Reading xls files

Hello everyone! I ran into another problem. So I have this excel spreadsheet that has like many many names and many scores on projects (school stuff). My OBJECTIVE: Find the highest score on each project and print the name of the person. PROBLEMS: Some names also have two strings in them, like "Mc Donald" or "Adaji Ruvvali", etc.

Code:

---

FILE *fp;
fp=fopen(projectscores.xls, r);

---

So after I read the xls, how do I make it read the columns, there are total of 8 columns, One for the names, and 7 for the 7 projects. I was thinking of just going through the excel file and just saying finding the highest score on each project, but I was wondering if I can write code for this, so here we are :D...

I read the tutorial on the cprogramming website, but it doesn't talk about reading xls columns and rows. If you guys have the time to give me your advice and help :D, it will be greatly appreciated.

It'd be easer to save the file as ".csv" (Comma seperated values), otherwise you're going to have to read and process the Excel spreadsheet which is not easy.

The former idea is far easier :-)

hmm... really? Hmm... is it that much harder reading it from a xls spreadsheet? can't we something like

Code:

---

FILE *fp;
fp=fopen(projectscores.xls, r);

int i;
int x;
for(i=0; i != EOF; i++)
{
      i = fgetc(fp);
      printf("Highest score is %d", i);
}

---

or something similar to that? Or am I really off.. lol

No.

Here is a 250 page document explaining the format: http://sc.openoffice.org/excelfileformat.pdf. Of course there may be libraries that do this for you, but it's still going to be more difficult than using the csv approach.

BTW, Why do this in C? You can do it in Excel pretty easily...

Hmm... 250 pages.... haha.

Well I just like coding, its fun and it takes other stress off of me. Just trying things with coding and stuff, see how far I can go and stuff, but it seems like everything I try, i always hit a wall. lol.

hey would this work? like so... ahh here's what I got so far, I was just wondering if it would work

Code:

---

typedef struct
{
  char names[50];
  int scores[10];
}PEOPLE;

---

Then after this struct, I just make a few declaration functions, like one of them would get make sure the file name is correct, the other would maybe read the file into an array or something that can hold it and malloc some memory, then like finally make a function that will get the highest score? It might a lot of work, but its going to be fun :D

You are making a very large assumption -- that if the spreadsheet has "McDonald" in it, the file also has McDonald in it somewhere. If that was true, you wouldn't need a 250 page description of how to read the data.

Quote:

---

Originally Posted by zacs7

No.

Here is a 250 page document explaining the format: http://sc.openoffice.org/excelfileformat.pdf. Of course there may be libraries that do this for you, but it's still going to be more difficult than using the csv approach.

BTW, Why do this in C? You can do it in Excel pretty easily...

---

Nice! Do you happen to have one on Power Point Format? You would be my personal hero if you did.

OpenOffice is open source, get it yourself :p

I know... I had to steal code from OpenOffice when I was doing a DB that compiled stuff out to PP slides. But as any OpenOffice user can tell you, their isn't 100% compatibility between programs.

Quote:

---

Originally Posted by master5001

Nice! Do you happen to have one on Power Point Format? You would be my personal hero if you did.

---

Microsoft does post the details for their file formats, check here:
http://www.microsoft.com/interop/doc...ryformats.mspx

Sorry I was gone for a bit. Hmm so, is there no way to read my xls file or something?

Quote:

---

Originally Posted by dlwlsdn

Sorry I was gone for a bit. Hmm so, is there no way to read my xls file or something?

---

Yes, there is, but just like you CAN build your own car from a few lumps of aluminium and some plate steel (and many other bits and pieces, most likely), it's much easier to achieve the goal in a different way: Save the .xls as .csv in Excel, then read that file - which is a relatively easy to read text-file, rather than a complex binary file.

The document from MS: http://download.microsoft.com/downlo...cification.pdf
is 349 pages long, and whilst MANY of those pages aren't necessary for what you want to do [you probably don't care about formatting options, comments on fields, revision records, etc, etc], I bet that unless someone who is REALLY experienced in this sort of thing is working on it, it will be months before you complete your project.

--
Mats

Alrighty, I saved it as a .csv. Now I'm confused on how to get the integers, how i have a clue on how to get the chars, but yeah. I'm going to read some tutorials and stuff. Thanks for the help ^^

You can translate a string (char array) to integer by using strtol() or sscanf("%d") for example. Reading tutorials is ALWAYS a good idea.

--
Mats

Here is my code at the moment, I was just thinking of a way for a while to do this... its quite long, but basically my comments are the things where it explains everything. I'll still be working on this :D, its fun. Cprogramming tutorials are awesome, but there are still questions that i have, especially about the fscan thingy...

Code:

---

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


typedef struct {
    char names[50];
    int scores[7];
}PEOPLE;

// function declarations
void the_file_name(char *filename);
int how_many_lines(char *filename);
void read_the_file_to_array(char *filename, PEOPLE peoples[], int *size);
void get_the_stats(PEOPLE peoples[], int size);

int main(void) {
    char filename[30];
    PEOPLE *peoples;
    int size;

    the_file_name(filename); // Finished the function

    peoples = malloc( (how_many_lines(filename)+1) * sizeof(PEOPLE)); // finished this function

    read_the_file_to_array(filename, peoples, &size); // NOT FINISHED......I can use this function to put things into arrays

    get_the_stats(peoples, size); // NOT FINISHED....Getting the scores..

    return EXIT_SUCCESS;
}

/* Function goes through student arrays and prints out the MAX score on each project.
 * so in the end, the function will display the person name and print the score and name
 * for each assignment, for assignment 1, it will go through that and find the max score and the name
void getStats(PEOPLE peoples[], int size){

// NOT FINISHED
}



/* My xls, cva file looks something like...
 * Hammy, Rogers (persons_id) | (what_student_number_they_are_in_class) assignment1 assignment2..  assignment7
 * I was thinking something using fscan and returning something as long as its != EOF.
 * perhaps putting it into the struct or something.
 * maybe something like, where the chars names[50] will hold the names of the students
 * and the scores[7], will hold the 7 assignments/projects they did.
 * so, each element space will hold a assignment, can i do something like this?
 * then pulling the highest number, by using a for loop?
 * also, i don't want the persons id nor the number number in class, just need assignments
 * hehe, sorry for asking so much
 */
void readFileIntoArray(char *filename, PEOPLE peoples[], int *size){

// NOT FINISHED
}


void the_file_name(char *filename){
    int length;

    do {
      printf("Enter a filename with .xls at the end please. ");
      fflush(stdout);
      scanf("&#37;s", filename);
      length = strlen(filename);
    }
    while(filename[len-1] != 's' || filename[length-2] != 'l' ||
        filename[len-3] != 'x' || filename[length-4] != '.');
//FIINISHED
}


int how_many_lines(char *filename){
    FILE *fp;
    char a;
    int count=0, new_line=0;
    fp = fopen(filename, "r");
    while(fscanf(fp, "%a", &a) != EOF){
        if(a=='\n' && !new_line){
            count++;
            new_line = 1;
        }
        else if (a != '\n')
            new_line = 0;

    }
    fclose(fp);
    return count;
// FINISHED
}

---

Code:

---

void the_file_name(char *filename){
    int length;

    do {
      printf("Enter a filename with .xls at the end please. ");
      fflush(stdout);
      scanf("%s", filename);
      length = strlen(filename);
    }
    while(filename[len-1] != 's' || filename[length-2] != 'l' ||
        filename[len-3] != 'x' || filename[length-4] != '.');
//FIINISHED
}

---

shouldn't you check that it ends with ".csv" if that is the file format?

And you could use strcmp(&filename[len-4], ".csv") != 0 to check if it's a mis-match. Of course, I personally prefer that applications just take a filename as I type it - or at least, if it's got an extension, that it's not adding another one - say I saved my file as "list.txt", but it's really a .csv file, why should your application NOT work with that?

Not sure if this is just a place-holder, but "%a" is not a standard format specifier:

Code:

---

fscanf(fp, "%a", &a)

---

It is generally not a great idea to read the file to find out how many lines it has, then allocate memory, and then read the file again. If you fell you MUST do this, why not use fgets() with a long buffer - at least then you will be reading reasonably efficiently, where as fscanf() is a pretty processor intensive way to read a file. [No, you probably won't be able to measure the difference on a file less than a few megabytes, but if no one tells you until you are wondering why your mega-huge file takes ages to read, how are you going to know the right/wrong way to do it?]

There are two ways I'd prefer to do it:
Method A:
* Have a linked list of projects (or a fixed array of "too large", e.g. 300 projects as a maximum).
* Read in a line of the file, parse the name and score. Check the project list. If it's in the list, check if the score is higher: Replace, otherwise don't do anything. If it's not in the list, it's obviously a new highest score, so add it to the list.

Method B:
* Start with mallocing a "person array" decent number of person slots, say 100. Set a size variable to 100.
* While not end of file:
* Read a line.
* check if there is space in "person array", then put it in the person array. If person array is full, use realloc to double it's size (set size variable to 2 x size), and insert it in one of the new free slots.

A note on style, you are inconistent in your file naming, choose one or the other:

Code:

---

readFileIntoArray
read_the_file_to_array

---

--
Mats

haha, sorry, I was typing it from memorization. I'm at work at the moment, hehe. Also working with textedit at the moment, to see how it will hold me up. Soon I'll be home and then the real deal starts, thanks for your insight and your quick responses. Been very helpful.

Quote:

---

Originally Posted by rdrast

Microsoft does post the details for their file formats, check here:
http://www.microsoft.com/interop/doc...ryformats.mspx

---

Aren't you a handy person :)

Wouldn't it be a lot simpler to use a Windows oriented approach such as MFC's CDatabase class?

Quote:

---

Originally Posted by BobS0327

Wouldn't it be a lot simpler to use a Windows oriented approach such as MFC's CDatabase class?

---

Except that means understanding how to use MFC, which is a different kettle of fish.

--
Mats

Quote:

---

So after I read the xls, how do I make it read the columns, there are total of 8 columns, One for the names, and 7 for the 7 projects. I was thinking of just going through the excel file and just saying finding the highest score on each project, but I was wondering if I can write code for this, so here we are ...

---

Yes, you can write a csv parser but it's not a trivial task.

An abbreviated description of a csv structure is as follows:

Quote:

---

Each record is one line (with exceptions)
Fields are separated with commas
Leading and trailing space-characters adjacent to comma field separators are ignored
Fields with embedded commas must be delimited with double-quote characters
Fields that contain double quote characters must be surounded by double-quotes, and the embedded double-quotes must each be represented by a pair of consecutive double quotes.
A field that contains embedded line-breaks must be surounded by double-quotes
Fields with leading or trailing spaces must be delimited with double-quote characters
Fields may always be delimited with double quotes
The first record in a CSV file may be a header record containing column (field) names

---

Post a few lines of your csv file so that we can see what you're working with.

BTW, the previouse code example I posted used an ODBC driver to query the excel file. ODBC also has a generic text driver that would make parsing a csv file extremely simple.