May 29, 2024, 04:55:11 AM

News:

IonicWind Snippit Manager 2.xx Released!  Install it on a memory stick and take it with you!  With or without IWBasic!


Problem reading Excel file

Started by billhsln, May 28, 2010, 09:53:42 PM

Previous topic - Next topic

0 Members and 2 Guests are viewing this topic.

billhsln

I have 2 programs, one created from the other.  The first one reads a TAB delimited file created directly from an Excel Spread sheet.  The second one process's the Excel Spread sheet.  In both programs I load the info into an Array.  From this Array, I write out the second and the thirteenth fields.  My Excel spread sheet should only have a max of 18 columns, I allow the Array to have up to 30, just in case.  I have tried stripping down the Excel spread sheet, so I can upload it here, but when I do that it seems to fix my problem.  My problem is the TAB delimited file finds all 18 fields, but on some lines, not all, the Excel Spread sheet only pulls in less than thirteen columns of data, on a line that has the full eighteen that displays when I pull up the spread sheet.  I would be willing to put the spread sheet here, but it is company private info.  I am going to put the program here that I am using.  It just seems strange that it would not pull the entire line off of the Excel Spread sheet.

Here is the code for the program that reads the Excel Spread Sheets.

$MAIN
STRING Version$
Version$="1.0"

AUTODEFINE "off"

FILE Ifile, OfileIE
STRING FileNameO, path, filename, driver, options, rtn, flds[30]
STRING company, cmpny, t, q, c, qcq, cq, qc, ch, selct, error
STRING recin, recout
POINTER pDB
INT recs, trecs, wrecs, i, j, l, fnd, dir, attrib, eod, hstmt

t = "\t"

c = ","
q = "\""

qc = q + c
qcq = q + c + q
cq = c + q

trecs = 0
wrecs = 0

OPENCONSOLE

'path = "C:\\Users\\Owner\\Documents\\CSB\\Bakeshop\\Jerry's Actual Report\\"
path = GETSTARTPATH
FileNameO = path + "PSV0190U.CSV"
IF (OPENFILE(OfileIE,FileNameO,"W") <> 0)
PRINT "File: " + FileNameO + " Not able to Open Output"
GOTO eoj
ENDIF

dir = FINDOPEN(path + "*.xls")
IF (dir)
DO
filename = FINDNEXT(dir,attrib)
IF filename = ""
' Last Record
rtn = ""
ELSE
IF attrib & @FILE_DIRECTORY
'this is a directory
rtn = ""
ELSE
PRINT "OPEN ", filename, " = "
driver = "Microsoft Excel Driver (*.xls)"
options = ""

pDB = dbConnect(driver,getstartpath + filename,options)

if pdb = NULL then
print "Database connection is not established."
goto eoj
endif

company = UCASE$(MID$(filename,9,3))
SELECT company
CASE "7TH"
cmpny = "1"
CASE "I45"
cmpny = "2"
CASE "I35"
cmpny = "3"
ENDSELECT
'=============================================================================
' Read the database.
'=============================================================================
selct = "SELECT * FROM [Report$]"
hstmt = dbExecSQL(pDB,selct)

error = dbGetErrorCode(hstmt)
IF LEN(error)
PRINT
PRINT "Error Code: ", error
PRINT selct
PRINT "Error Text: ", dbGetErrorText(hstmt)
PRINT
GOTO FreeIt
ELSE
eod = 0
recs = 0
WHILE dbGet(hstmt) AND eod = 0
recs++
trecs++
j = 1
FOR i = 0 TO 29
flds[i] = ""
NEXT i
WHILE dbGetData(hstmt, j, flds[j-1])
j++
ENDWHILE
IF flds[0] = "ZZZZ"
eod = 1
ELSE
ch = MID$(flds[1],1,1)
IF ch >= "0" AND ch <= "9"
ch = mid$(flds[1],1,instr(flds[1],".")-1)
rtn = mid$(flds[12],1,instr(flds[12],".")-1)
IF rtn = "" THEN rtn = "0"
recout = cmpny + cq + ch + qc + rtn
wrecs++
WRITE OfileIE, recout
ENDIF
ENDIF
i++
ENDWHILE
ENDIF
ENDIF
ENDIF
'=============================================================================
' Close the database.
'=============================================================================
label FreeIt
dbFreeSQL(hstmt)
dbDisconnect(pDB)
UNTIL filename = ""
FINDCLOSE dir
ENDIF
GOTO eoj2
LABEL eoj
PRINT "---Program Aborted---"
LABEL eoj2
PRINT
PRINT "         Total Recs = ", USING("###,###",trecs)
PRINT
PRINT "       Written Recs = ", USING("###,###",wrecs)
PRINT
INPUT "Press Enter", rtn
CLOSECONSOLE
END


This program has worked before, and it seems ok 80% of the time.  It is only on a few lines that it only reads < 13 columns of data while I show 18.  I know there is 18 columns because the info in column 2 and column 18 are the same.

I may be able to send the spread sheet to an individual, but can not upload it here.

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

LarryMc

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

LarryMc

Bill
After i fixed a couple of things like the company MID$ statement picking out the wrong 3 characters and the program crashing because you're freeing hstmt when it is already freed I could really look at the program.

I thought I knew how to predict what records will fail by visually looking at the xls file.

Look at the Description field.
If there is a - or a \ in most case it will return a 0

But I found some that didn't have any extra non alphanumeric that worked ok.

So I'm still looking

LarryMc



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

LarryMc

If you replace this:
WHILE dbGetData(hstmt, j, flds[j-1])
  j++
ENDWHILE

with this 'dbGetData(hstmt, 2, flds[1],255)
'dbGetData(hstmt, 13, flds[12],255)

you will get the corrent numbers from colum 13

I think you may need to bind variables and then use dbIsNull to check fields for valid entries but that's just a guess.

I think it boils down to you are reading coulms that aren't initialize to anything and the db routines aren't liking that.

This is the first time I've ecer run a EBasic program with the ODBC driver for an xls spreadsheet so I don't have any experience with any specific issues with xls.

Hope this helps you a little anyway.

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

LarryMc

Here's an old example that shows an excel file using dbbind that may help.

LarryMc

' How to read EXCEL Workbooks and -sheets with the DataBase pak
' By Michael Hartlef, based on the original database_test.iba file
OPENCONSOLE
'These are the variables that take in the values of the columns
DEF name,code as STRING
DEF amount as INT
DEF pDrivers,pTables,pBindings as POINTER
pDrivers = dbEnumDrivers()
IF pDrivers <> NULL
   PRINT "Available database drivers:"
   FOR temp = EACH pDrivers as STRING
      PRINT #temp
   NEXT
   ListRemoveAll(pDrivers,TRUE)
ELSE
   PRINT "Unable to enumerate drivers"
ENDIF
PRINT:PRINT
'dump the layout (schema) of the database and retrieve the data from a table.
pdb = dbConnect("Microsoft Excel Driver (*.xls)",GETSTARTPATH + "test.xls","")
IF pdb <> NULL
   PRINT "Connection established to " + GETSTARTPATH + "test.xls"
   pTables = dbListTables(pdb)
   PRINT "Tables:"
   IF pTables <> NULL
      FOR temp = EACH pTables as STRING
         PRINT #temp
         PRINT "Cardinality:",dbCardinality(pdb,#temp)
         PRINT "\tColumns:"
         pColumns = dbListColumns(pdb,#temp)
         IF pColumns <> NULL
            FOR temp2 = EACH pColumns as STRING
               PRINT "\t\t",#temp2
            NEXT
            ListRemoveAll(pColumns,TRUE)
         ENDIF
      NEXT
      ListRemoveAll(pTables,TRUE)
   ELSE
      PRINT "Unable to list tables"
   ENDIF
   'list the data in the "Tabelle1" worksheet
   PRINT
   PRINT "Data :"
   'example SQL statements to retrieve datasets
   'dbExecSQL returns a handle to the executed statment or zero on failure.
   'A valid statement handle is returned even on an empty result set.
   hstmt = dbExecSQL(pdb,"SELECT * FROM [Tabelle1$]")
   error = dbGetErrorCode(hstmt)
   IF LEN(error)
      PRINT
      PRINT "Error Code: ", error
      PRINT "Error Text: ", dbGetErrorText(hstmt)
      PRINT
   ENDIF
   IF hStmt
      'bind the columns in the table to appropriate variables
      'column numbers start at 1 starting from the left.
      dbBindVariable(hstmt,1,name)
      dbBindVariable(hstmt,2,code)
      dbBindVariable(hstmt,3,amount)
      'dbGet returns TRUE if data has been retrieved and stored
      'in the bound variables. Or FALSE if the end of data has been reached or an error occured
      WHILE dbGet(hstmt)
         PRINT "Name:",name
         PRINT "Code:",code
         PRINT "Amount:",amount
         PRINT
      ENDWHILE
      dbFreeSQL(hstmt)
   ENDIF
   dbDisconnect(pdb)
ELSE
   PRINT "Unable to connect to database"
ENDIF
PRINT "Press any key to exit"
DO:UNTIL INKEY$ <> ""
CLOSECONSOLE
END
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

billhsln

Using the specific dgGetData seems to work right.

Thanks for all the help, Larry.

Will keep playing with it, maybe I can figure it out, but at least this solves the problem for now.

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