Hi David.
>I am a novice working with a single-table database using FoxPro Version 6, and I generally just use FoxPro to concatenate fields, replace fields, or summarize data for files that are larger than I can work with in Excel. I have a file in excel with a listing of replacements I want to do for a specific field in my database. Is there anyone out there that can provide me the commands I need to replace FIELD1 with a field from my excel file, if FIELD2 is equal to another field from my excel file?
Depending on the version of Excel that created the file, this will be fairly easy (Excel 2005 or earlier) or a little harder (Excel 2007 or later). For the former, use IMPORT to create a table from the Excel file. For the latter, you'll need to use ODBC to open the Excel file and then query it to a cursor, using code such as:
lnHandle = sqlstringconnect('driver=Microsoft Excel Driver (*.xls); dbq={name and path for excel file}')
sqlexec(lnHandle, 'select * from [Sheet1$]', 'ExcelCursor')
Note that in place of Sheet1$, substitute the name of the sheet within the Excel file if it isn't Sheet1.
Now it's easy. Here's one simple way (not the most efficient but easy to understand):
select ExcelCursor
scan
select MyTable
locate for FIELD2 = ExcelCursor.Field2
if found()
replace FIELD1 with ExcelCursor.Field1
endif
endscan
Doug