Hello,
Now I can read xlsx files into a listview using the new drivers, I am having problems with
the data that's being read in
For example, data that should be "80" is now showing as "80.0" and similarly data that
gives the year, eg, "2013" is now showing as "2013.0"
Any ideas? Are there any switches I can use in the connection string options? Been
trying a few, with no success
Brian
Brian,
It seems to me that it's the Excel file.
The numbers need to be formatted, in
Excel, as numbers and with zero "0" for
the number of decimal places.
Bill
sort of crude but you can take each numeric string you read from the spreadsheet, convert the string to an integer and then convert that back to a string before putting it in the listview.
Brian sent me two excel files; one an .xls and the other and .xlsx.
They both appear to contain the same data.
Here's what I was able to discern using the demo program I posted above.
I also used my OpenOffice spreadsheet program to view the excel files.
With the .xls file, it appears that when a new sheet is created it creates a sheet that has a phantom empty column labeled F1(which I could not find visually) and causes the dbCardinality command to report that one data row exists although no user data entries have been made. (This does not happen when a sheet is added to the .xlsx file in exactly the same way.
The work around for this would be to do the following:
When opening and reading a spread sheet (either .xlx or .xlsx) always test a column that ALWAYS will have an entry and see if it is blank. If it is then don't process the record.
That will ignore the phantom row in .xls files.
As to the "2013" is now showing as "2013.0" problem.
That's an easy fix.
In my demo I have
DEF lname,fname,info as STRING
WHILE dbGet(hstmt)
dbGetData(hstmt, 12, lname)
dbGetData(hstmt, 13, fname)
dbGetData(hstmt, 14, info)
PRINT "Last Name:",lname
PRINT "First Name:",fname
PRINT "Information:",info
that means I'm reading the data into strings
If I change the variables to integers for the numeric columns then the .0 goes away
DEF x,y,z as INT
WHILE dbGet(hstmt)
dbGetData(hstmt, 12, x)
dbGetData(hstmt, 13, y)
dbGetData(hstmt, 14, z)
PRINT "#1:",x
PRINT "#2:",y
PRINT "#3:",z
Good stuff, Larry
I had a better test result by setting the tableName with: IF rowNum>1 THEN tableName=#temp
rather than IF rowNum>0, but your idea is far better
I am using some of Fletchie's code to fill the listview, and he is using #<STRING> in a loop
to fill the listview cells, so it is a bit awkward to change an individual cell to an INT
Will look into it further...
Brian
Quote from: Brian Pugh on December 22, 2013, 11:23:48 AM
I am using some of Fletchie's code to fill the listview, and he is using #<STRING> in a loop
to fill the listview cells, so it is a bit awkward to change an individual cell to an INT
So he's using a pointer that has been typecasted as a string.
so all you should have to do is take the x,y,z in my example above and convert the value to strings before loading the LV
Maybe something along this line(without seeing your code)
#<string>mypointer =ltrim$(str$(x))
Larry,
Here's Fletchie's code for filling a listview (very elegant, I may say):
SUB DataGridFill(connection:POINTER,sqlStatement:STRING,winHandle:WINDOW,dataGridID:INT)
recordSet=dbExecSql(connection,sqlStatement)
conLVColumnListSet(winHandle,dataGridID,dbListColumns(connection,"",recordSet),100,1)
colCount=dbGetNumCols(recordSet)
rowNames=NEW(STRING,colCount)
FOR loop=1 TO colCount
dbBindVariable(recordSet,loop,#<STRING>rowNames[loop-1,0])
NEXT loop
rowNum=0
conDrawOff(winHandle,dataGridID)
WHILE dbGet(recordSet)
CONTROLCMD winHandle,dataGridID,@LVINSERTITEM,rowNum,#<STRING>rowNames[0,0]
#<STRING>rowNames[0,0]=""
FOR loop=2 TO colCount
CONTROLCMD winHandle,dataGridID,@LVSETTEXT,rowNum,loop-1,#<STRING>rowNames[loop-1,0]
#<STRING>rowNames[loop-1,0]=""
NEXT loop
rowNum++
ENDWHILE
conDrawOn(winHandle,dataGridID)
dbFreeSQL(recordSet)
dbDisconnect(pDB)
ENDSUB
Brian
I found in the old pyxia forums where you were using that subroutine back in 2004.
It appears that subroutine was initially written for processing csv files which contains all strings.
At first glance it appears that it would require major mods to be able to handle xls files that contain numerically format cells in a universal, generic way.
In order to solve your specific problem with the '.0' you can modify this
FOR loop=2 TO colCount
CONTROLCMD winHandle,dataGridID,@LVSETTEXT,rowNum,loop-1,#<STRING>rowNames[loop-1,0]
#<STRING>rowNames[loop-1,0]=""
NEXT loop
to something like this
FOR loop=2 TO colCount
select loop
case 12 'an entry for each numeric column in the spreadsheet
case& 13
case& 14
if RIGHT$(#<STRING>rowNames[loop-1,0],2)=".0"
#<STRING>rowNames[loop-1,0]=LEFT$(#<STRING>rowNames[loop-1,0],LEN(#<STRING>rowNames[loop-1,0])-2)
endif
endselect
CONTROLCMD winHandle,dataGridID,@LVSETTEXT,rowNum,loop-1,#<STRING>rowNames[loop-1,0]
#<STRING>rowNames[loop-1,0]=""
NEXT loop
Larry,
Thanks for your help. I didn't really realise that the DataGridFill was intended for CSV files,
so of course it's going to struggle with xls-type files
But your code is enough to get me through this particular fire-fighting exercise!
This afternoon someone sent me an xlsx file from work which on the face of it looks
just like the others, but will it show any data - not a cat in (well, you get the picture).
It shows the header, but refuses to show any data. Table name is standard, as well
It appears to be getting more and more complicated. I don't know enough about the
Excel file formats to be able to write code that will handle anything I throw at it
I'll kleep on truckin' !
Brian
send me a copy of the xlsx file and I'll look at it.
I had no problem reading the data in the sheet once I got rid of the space in the file name.
Brian
I could read the proper number of rows from all 10 of the files you sent me.
Larry,
Right, thanks for testing the files. I think I am going to re-write all of the
import procedure, using your code as a base. I will worry about the spaces
in filenames after. Don't understand how it can see one with spaces and
not another, but heyho...
Merry Christmas to you and your family
Brian
PS: Now got a working console application, with all the modifications (.0 at the end, etc),
plugged in and working, and it opens all filenames (even with spaces in them)