On Thu, Apr 19, 2018 at 9:38 AM, Paul H. Tarver paul@tpcqpc.com 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. ...
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.
No, I think you've found one of those genuine "BUGS" in Visual FoxPro, rare as hen's teeth.
I think you need to create another step in your process, where you query the bad source, review the column names, and create a set of acceptable aliases, then build a query that uses those aliases rather than the underlying (and sometimes bad) remote column names.
If you were working with the remote data directly, SQLColumns() can return a list of columns, as of course can an empty cursor "SELECT *
From TargetTable WHERE 1=0" But since you're creating the cursor in
more-or-less of a black box, run AFIELDS() on the resulting cursor. Then, make a plays-well-with-others cursor for you to use from there. Or you could build the logic directly into the INSERT INTO... SELECT statement, though it may get long and unwieldy.
Scan the result of AFIELDS() and built a set of ColumnName-AliasName field, testing for the bad situations you've identified (integer first character, excessive length, and also duplication, since you're truncating) and then build the field list using textmerge or string concatenation as:
SELECT ColumnName as AliasName, ColumnName as AliasName, ColumnName as AliasName, ...