Read from xls file (or mdb file)

[thek]

Hi,

New to the forum so be gentle ;-)

I’m trying to create a program that reads an excel sheet (or even better a access database, but haven’t any info on the ODBC yet)

I’m using Codeblocks 8.02

test.xls
Name Age Size
Homer 39 10
Bart 11 5

#include <iostream>
#include <fstream>
#include <string>
using namespace std;

int main () {
  string line;
  ifstream myfile ("test.xls");
  if (myfile.is_open())
  {
    while (! myfile.eof() )
    {
      getline (myfile,line);
      cout << line << endl;
    }
    myfile.close();
  }

  else cout << "Unable to open file";

  return 0;
}

Problem(s):

1) The output is just 6 weird looking char. Why?
2) Can I use "string" to get the numbers from the XLS file?
3) What if I want to only get Bart’s data?
4) What if I only want to get Bart’s age?
5) What if I want to add Bart’s and Homer’s size (10+5 = 15)

Thanks in advance.

[Zaita]

1) Because XLS files are stored in a proprietary binary format.
2) No. See above.
3) You need to use a specific Excel (or ODBC) connection.
4) Same above
5) Same as 3

You cannot read an Excel file like it's a text file. You need to use specific Microsoft Excel API. However, if you want to also use Access databases. Then I would recommend looking at ODBC. You could also use one of the Microsoft wrappers (but it's no longer platform independent) like ADO.

[thek]

Thanks Zaita

I've spend the last couple of hours reading about ADO, ODBC, etc.

My biggest problem is how to install eg. SQLAPI in codeblocks/my program. I keep getting a "SQLAPI.h: No such file or directory" error.

Questions:
1) Is SQLAPI the best(read: easy) tool to use?
2) How do I install it?
The doc do NOT say anything about it. I've tried to add all the files in SQLAPI in the project. Still the samme error messages.

3) Is there another ODBC/ADO program to use? (Again the keyword is EASY to install)

Thanks

[helios]

If you're such a beginner then maybe you should consider trying something a little easier regarding files. That would help you understand why you get only 6 pre-32 bytes (probably a magic number of some kind) when opening a binary file as a text file.
There's that, and also you want to open an XLS. Microsoft isn't known for sharing their format specifications.
If all you want is to read a table, how about starting from something simple. Say... XML? XML parsers are easy to find and (if you feel like it) easy to implement. Also, if I remember correctly, you can save XLSs to XML, so you don't even need to write the structure by hand.

[freddie1]

As helios said, this isn't a good beginner topic at all. However, at...

http://www.powerbasic.com/support/pbforums/showthread.php?t=24912&highlight=Using+ODBC+Direct

is a three part post in the Source Code Forum at www.powerbasic.com entitled...

ODBC Api Direct Example Using Assess Database

In the 2nd post is the program in C. It compiles easily with either Dev C++ or Visual C++. The various ODBC header files (there are 3 or 4) are included in all the C/C++ distributions of which I am aware. The functionality of ODBC is a part of Windows. All you need is to include the header files along with your other includes. I will say that very few people use ODBC direct. I do, but I'm rather 'hard core'. Most folks use OOP wrappers around ODBC in some form or other. Good luck!

[Zaita]

While I agree with freddi1 that this isn't really a beginner topic. I still think there is no reason why the OP shouldn't be able to accomplish the task. Just going to require a bit of hard work (this is where most new devs fall down).

You can go the ADO route or ODBC router (I prefer ODBC because it's multi-platform, multi-database). It's going to be a bit of a mind-fuk to begin with, but with some trial and error you should be able to do it. Just remember to start very small (simple access database, 1 table with 2 fields etc) and work your way up from there.

[freddie1]

Yes! Definitely! Thats how you learn. The example for which I posted a link above would be an excellent place to start. It is rather short and heavily commented. The example first creates an Access database, then a simple table within the database with only several fields. If I recall, a primary key integer count field, a double field, a char string field, a date field, and perhaps that's it. Then it reads a few values into the table, closes it, then dumps the data. I have about the same example over in the PowerBASIC forums showing DAO, but that's a bit dated. However, DAO was REAL FAST!. Also there are the same ODBC examples using SQL Server Express, but the Access one with ODBC is the only one where I provided a C version. All the rest are in PowerBASIC, which however is very close to C.

[thek]

Hi guys,

Thanks for all your replies.

I decided to make a binary file instead to start with and that seems to work for me. I'll return to the ODBC solution when I have more experince.

Thanks for your help.