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

---

_______________________________________________

Post Messages to: ProFox@leafe.com

Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox

OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech

Searchable Archive: http://leafe.com/archives/search/profox

This message: http://leafe.com/archives/byMID/profox/0b5a01d3d657$8a9e10f0$9fda32d0$@tpcqpc.com

** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.