I've been giving that some thought and I'm leaning toward something like this as a pre-processor to overwrite the default Foxpro functionality:
SQLEXEC(m.lnSQL,[SELECT ] + m.tcExcelFieldList + [ FROM "] + m.tcSheet + [" Where ] + m.tcExcelWhereExpr, m.lcSQLAlias) AFIELDS( laArrayName, m.lcSQLAlias) ....Loop through fields to fix bad fieldnames CREATE CURSOR FROM ARRAY laArrayName APPEND FROM DBF(m.lcSQLAlias) ...and so on I did take a few minutes this morning to test whether AFIELDS would blow up and it does not! That means the logic I've outlined here might work. Once I get a few more minutes I'll see if I can flesh out this line of logic further. Suggestions are appreciated. Paul -----Original Message----- From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Frank Cazabon Sent: Thursday, April 19, 2018 11:01 AM To: profoxt...@leafe.com Subject: RE: INSERT INTO...SELECT Issue If you have the source code to this program, can you change the processing that builds up lcNvlFieldList to check for bad characters? On 19 April 2018 11:42:55 GMT-04:00, "Paul H. Tarver" <p...@tpcqpc.com> wrote: >I could, but that would require expanding the field name definitions >out in >advance of calling the routine which would be very difficult because at >the >time the command is issued, the program may or may not know all of the >fields in the import file. > >This code comes from the AppendXLSX.prg and there are often times when >importing an Excel file, the main program won't know the full context >of the >Excel file to imported. > >Paul > > >-----Original Message----- >From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of >Frank >Cazabon >Sent: Thursday, April 19, 2018 8:52 AM >To: profoxt...@leafe.com >Subject: Re: INSERT INTO...SELECT Issue > >Can you rename the field in your SELECT like this: > >SELECT BadFieldName AS GoodFieldName > >? > >Frank. > >Frank Cazabon > >On 19/04/2018 09:38 AM, Paul H. Tarver wrote: >> I posted a longer version of this situation previously, but it may >have >been >> mis-titled or included too much detail because I've gotten no >responses. >> >> >> >> Here's the low down and dirty version: >> >> >> >> When you use INSERT INTO.SELECT from a remote data source that >includes >> fieldnames that begin with numbers in the development environment, >VFP9 >> automatically changes the leading digit in the field name to an >underscore >> ('_'). However, when executing the same command as a compiled >executable >> this automatic substitution does not occur and VFP will allow >fieldnames >> with a leading digit to be returned in the resultant cursor. This >causes >> major problems later if you use the remote data cursor to create a >new >> cursor because Foxpro won't allow field names with a number in the >first >> position. ALTER TABLE cannot be used on the remote data cursor and >even if >> you make the cursor READWRITE, the length of the fieldnames can often >exceed >> 10 characters. >> >> >> >> My question is this: Has anyone here run into this issue and if so, >Is >there >> any another way or procedure around this situation that would allow >me to >> manually fix the fieldnames which start with a number assuming I have >no >way >> to change the source data. >> >> >> >> Thanks in advance. >> >> >> >> Paul >> >> >> >> >> >> --- StripMime Report -- processed MIME parts --- >> multipart/alternative >> text/plain (text body -- kept) >> text/html >> --- >> [excessive quoting removed by server] _______________________________________________ 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/09d401d3d7fe$5df7c3e0$19e74ba0$@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.
