I have been using AppendXLSX.prg written by Craig Boyd for many years with great success. Until now.
Last week, I was training a client on some software I wrote for them which had been extensively tested (I thought!) but when I tried to import an Excel file (which I HAD tested at least a hundred times on this project in development mode), it failed and I got an error about a missing ')'. Nothing I tried in front of the client would work and so I asked them to use CSV files in the interim until I could get the problem figured out.
After extensive review today, I narrowed the problem down to this line in Craig's code:
INSERT INTO (m.tvWorkarea) (&lcFieldList) SELECT &lcNvlFieldList FROM (m.lcSQLAlias) WHERE &tcTableForExpr
I tested the problem in development mode and after I compiled to an .EXE about 50 times, I think. Every time, it would work perfectly in development mode and fail in executable mode. I added lots of messagebox items to see the values of the compiled code and eventually I changed the line above into a TEXT..ENDTEXT command so I could put the fully expanded command into a variable and save the string to a file to do in-depth comparisons between development and compiled. But, even after changing from the macro expansion to using delimited merge elements, it would still work in development mode and fail in executable.
When I began reviewing the expanded commands after the STRTOFILE command was put in place, I realized the client had a field header in their spreadsheet which started with a number: '3RDPARTYSICKPAY'.
Prior to the INSERT INTO SELECT command mentioned above, Craig issues the following command to retrieve the columns of data from the Excel worksheet:
m.lnResult = SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + [" Where ] + m.tcExcelWhereExpr, m.lcSQLAlias)
The weird part is when this command is issued from within the development environment, the command automatically changes the field name '3RDPARTYSICKPAY' to '_RDPARTYSICKPAY' whereas this substitution DOES NOT occur when the command is issued from a compiled EXE and the column name remains '3RDPARTYSICKPAY' which then causes a failure in the INSERT INTO SELECT command that occurs after the remote table has been created. I tried just renaming the field name prior to the INSERT INTO command, but this creates a field not found error. I tried ALTER TABLE after the cursor is returned from the SQLEXEC command to rename the column earlier in the procedure, but the command failed first because the initial cursor is a remote table and second because if I try to make the cursor a readwrite cursor it fails again because I have field names larger than 10 characters.
My client's files are created with automated processes and reflect the fieldnames within their software datastore. I do not have access to change these variable names nor do I want to do. However, I need some suggestions for potential workarounds short of having the client manually rename the column each time they create the file. Ideally, I'd like to come up with a solution that would be applicable in all cases for all clients but that may be too much to hope for. Sorry this is so long, but I wanted to include all I learned today about this problem.
Any thoughts?
Paul