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
Good info
Thanks!
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
Err, Bill, what do you mean by C12?
Brian
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.
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)
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
Good stuff, Larry - and with the new Excel driver, as well
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
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
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
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.