March 28, 2024, 07:44:46 PM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Reading Excel files

Started by Brian, December 18, 2013, 02:33:05 PM

Previous topic - Next topic

0 Members and 2 Guests are viewing this topic.

Brian

Hello,

One of our customers sent an Excel file with an xlsx extension. Seems Microsoft have changed the format
of Excel files to an xml type of structure, to save space, I think. The old xls format has a lot of padding
in them

I struggled and struggled at home to get one to read in to IWB, then I found these drivers:
http://www.microsoft.com/en-US/download/details.aspx?id=13255

You have to be careful to download the correct flavour - 32-bit or 64-bit. I am on Windows
XP SP3 here, so the 32-bit was for me

The connection string is slightly different. Here's what I used:

STRING driver = "Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)"
STRING filename = GETSTARTPATH + "filename.xlsx"
STRING options = ""

pDB = dbConnect(driver,filename,options)

It worked!

Seems to be backward compatible, as well, as it has opened Excel 97-2003 Worksheet
files, as well as the new Office Excel Worksheets

My Vista PC at work already has these new drivers, I just discovered, but it was my workaday
PC at home that was short of the new drivers. You should maybe check yours first to see if the
new drivers are already loaded

Hope this helps someone,

Brian

LarryMc

LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Could you give examples of how to 1) Read the data from C12 and 2) write data into C12.  I do a lot of Spread Sheet reading and updating.  Right now I do it thru Foxpro, because it can.  I would prefer to do it thru IWB.  Any thing else that can be done as examples would also be nice to have, in case other things need to be done with Excel files.

Thanks,
Bill
When all else fails, get a bigger hammer.

Brian

Err, Bill, what do you mean by C12?

Brian

LarryMc

Quote from: Brian Pugh on December 18, 2013, 04:00:58 PM
Err, Bill, what do you mean by C12?

Brian
I think he means column c, row 12.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

The attached zip contains a console based source file and a xls file.

The xls file has 26 sheets(one for each letter of the alphabet.
Each sheet is identical in structure and contains names and other info.

The iwb file
1)opens the xls file
2)gives you the count of the # of records in each sheet and the sheet names
3) the column names in each sheet(commented out)
4)gets  the 1st 3 columns of all the records from sheet "U" and prints out the results
5)adds a new row to the "U" sheet

To get a specific cell do a query for just that column and then do a loop for "row-number" times

pay attention to where I've placed [,], and $

This should help get you going(I had never done this before)
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Thank you, Larry.  That sounds exactly like what I am looking for.  I do lots of stuff with spread sheets, so since I could not do it in IWB, I had to use Foxpro, which would do it.  Maybe now I can rewrite these utilities in IWB.

Thanks again,
Bill
When all else fails, get a bigger hammer.

Brian

Good stuff, Larry - and with the new Excel driver, as well

Brian

Brian

Larry,

Re your example: You have an Excel file with 26 sheets, all with populated rows.
You already know this. I have an Excel file that has 3 sheets, but there is only
data in the first sheet. When you enumerate the sheets, the FOR NEXT loop
ends up on the last sheet name

How can I code it so that it automatically selects Sheet1$ as the default sheet,
or discards any sheet that is empty?

Any ideas?

Brian

LarryMc

Quote from: Brian Pugh on December 20, 2013, 12:13:53 PM
Larry,

Re your example: You have an Excel file with 26 sheets, all with populated rows.
You already know this. I have an Excel file that has 3 sheets, but there is only
data in the first sheet. When you enumerate the sheets, the FOR NEXT loop
ends up on the last sheet name

How can I code it so that it automatically selects Sheet1$ as the default sheet,
or discards any sheet that is empty?

Any ideas?

Brian
First of all, the program I posted above is a database demo like the one that comes with IWBasic that I modified to read/write an excel file.

2nd, the "enumeration" of the tables has nothing to do with the "selection" of a table.
In the demo the enumeration of the tables just tells you names of each of the tables(and in the example) tells you how many records are in each table with the dbCardinality(pdb,#temp) applied to each table.
You could use that loop to "process" any tables that weren't empty
(i.e dbCardinality(pdb,#temp) > 0)

If I already know the names of the tables/sheets I'm interested it I can just check to see if it is empty and act accordingly
(i.e. if dbCardinality(pdb,"U$) > 0) note: since my example was using sheet U that reads the number of records for that sheet.

The "selection" of a sheet is handled in the query.
In my example I selected records from sheet U
If you wanted Sheet1 then your query to get that sheets records would look like this:
hstmt = dbExecSQL(pdb,"SELECT * FROM [Sheet1$]")
and to insert a new record it would become
hstmt = dbExecSQL(pdb,"INSERT INTO [Sheet1$] ([LAST NAME], [FIRST NAME]) VALUES ('Larry', 'McC')")

All the stuff in the Database section of the help file applies if you substitute the word sheet for the word table.
And you'll have to remember the [,],$ thing.

Hope that answers your questions
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Brian

Larry,

Thank you, it answered all my questions, but . . .

I had a brainwave, and put in this line as part of the loop:
ntables=0
FOR temp=EACH pTables AS STRING
   rowNum=dbCardinality(pDB,#temp)
IF rowNum>0 THEN tableName=#temp  <<< EXTRA LINE <<<
   nTables++
NEXT

This worked for one Excel file, and picked up the first sheet name that had data in. But trying it on another
Excel file, which "appeared" to only have data in the first sheet, and "appeared" to have no data in the other
two sheets, picked up the last sheet name, so there must have been something lurking in there

Wonder if there is another way to see if there is actually "data" in an apparently empty sheet?

Brian

LarryMc

QuoteBut trying it on another
Excel file, which "appeared" to only have data in the first sheet, and "appeared" to have no data in the other
two sheets, picked up the last sheet name, so there must have been something lurking in there

Wonder if there is another way to see if there is actually "data" in an apparently empty sheet?
Like I said before, this is the first time I've tried this.
Email me the excel file and I'll see what I can see and do.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library