Problem reading Excel file

[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.

Code Select Expand

$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

[LarryMc]

Send it to me.

LarryMc

[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]

If you replace this:

Code Select Expand

WHILE dbGetData(hstmt, j, flds[j-1])
  j++
ENDWHILE
with this

Code Select Expand

'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]

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

LarryMc

Code Select Expand

' 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


[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