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 but when I tried to import an Excel file (which I had done at least a hundred times on the project in development mode), it failed and I got an error about a missing ') '. Nothing I tried 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, I traced the error to this line in Craig's code:
INSERT INTO (m.tvWorkarea) (&lcFieldList) SELECT &lcNvlFieldList FROM (m.lcSQLAlias) WHERE &tcTableForExpr
I tested importing the client spreadsheets multiple times on my development machine and every time, AppendXLSX.prg would work perfectly in development mode and fail in same spot when run as part of my executable program. I added lots of messagebox items to see the values of the compiled code and eventually I changed the INSERT INTO line above to a TEXT..ENDTEXT command so I could store the fully expanded command as a string and save the string to a file to do in-depth comparisons between development and compiled versions.
When I compared the expanded commands character by character, I realized the client had a column header in their spreadsheet which started with a number: '3RDPARTY' and the INSERT TO was failing in executable mode because FoxPro doesn't allow field names that start with a number. But I still couldn't explain why the same code worked in development mode.
After working my way back through the code, I found prior to the INSERT INTO command mentioned above, Craig issues the following command to retrieve data from the Excel worksheet:
m.lnResult = SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + [" Where ] + m.tcExcelWhereExpr, m.lcSQLAlias)
And this is where it gets weird. When this command is issued from within the development environment, the the column header '3RDPARTY' is automatically changed to '_RDPARTY' BUT this automatic substitution DOES NOT occur when the command is issued from within a compiled EXE and the column name remains '3RDPARTY' which then causes a failure in the INSERT INTO command which 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, but the command failed because the initial cursor is a remote table. After trying to make the initial cursor a READWRITE cursor it failed again because there are 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. 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 work 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
--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---