May 24, 2022, 02:40:51 AM

News:

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


Importing Excel files into a listview

Started by Brian, December 21, 2013, 03:59:06 AM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Brian

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

Bill-Bo

December 21, 2013, 09:47:39 AM #1 Last Edit: December 21, 2013, 04:57:40 PM by Bill-Bo
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

LarryMc

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.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

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


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

Brian

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

LarryMc

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))
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,

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

LarryMc

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



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,

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

LarryMc

send me a copy of the xlsx file and I'll look at it.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

I had no problem reading the data in the sheet once I got rid of the space  in the file name.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

LarryMc

Brian
I could read the proper number of rows from all 10 of the files you sent me.
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Brian

December 24, 2013, 07:33:34 AM #12 Last Edit: December 24, 2013, 11:47:19 AM by Brian Pugh
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)