Alter Cursor Results

I am trying to create a VFP 6 program that list all CSV files in a location. All CSV Files are updated daily. All CSV Files are named by building, date, and time, for example, 90_2014-9-17_9-10.  Also, there are three digit buildings like 100_2014-9-17_9-29.  I want the user to select a csv file, and once the csv file is selected, the building and date are appended along with the cursor MyResults fields Fb and Fc to the VFP Ltable.  After the cursor NewBatch is created, you can get the building number from Row 2 Column Fb.  The date is appended as mdy.  Once I get the cursor MyResults, how can I alter cursor MyResults to match VFP Ltable structure to append the Fb and Fc data?  

Here is my code:
CLOSE ALL

SET DEFAULT TO H:\OBRA\LDY\SORTREPORTS\
CSVsheet = getfile(",CSV")
NEWFILE = (CSVSHEET)
 MESSAGEBOX("FILE NAME IS "+ALLTRIM(NEWFILE),0, "NAME OF IMPORT CSV")
&&&& CSV START

SET SAFETY OFF

THISDOC = "H:\OBRA\LDY\SORTREPORTS\FILENAME.CSV"
  

CREATE CURSOR NEWBATCH;
  (FA N(2), FB N(5), FC C(15))
                

APPEND FROM &CSVSHEET DELIMITED


&& APPEND FROM H:\OBRA\LDY\SORTREPORTS\FILENAME.CSV] DELIMITED


SELECT * FROM NEWBATCH WHERE FA > 0 INTO CURSOR MyResults

Alter

BROWSE

Open in new window



Please check the attached file to understand the question.
     

Each Fb data will need to be appended and multiplied by the appropriate pd field in the Ltable for each Fc data.  For example of Fc(Blankets) and Fb(5), Blankets would be appended in the Ltable field called Blktdescript,, 5 would be appended in the Ltable field called blktamtin, than the blktamtin would need to be multiplied by Ltable field called blktpd to get amount for Ltable field blktpdin. Each Fc and Fb fields would need be done the same. Check the attached file.
C--Users-campbme-Documents-mycursor-resu
campbmeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Your specification is a little bit unclear...
You should post the Ltable example before and after the process.

I can just suppose the Ltable exists already, it has just one record and you have to update it.

You may start with the following code:
SELECT lTable
GO TOP

SELECT MyResults
SCAN ALL
  
  STORE '' TO lcDescrFld, lcQtyFld
  DO CASE
  CASE fc = 'Blankets'
    lcDescrFld = 'Blktdescript'
    lcQtyFld = 'Blktamtin'
  CASE fc = 'Flat Sheets'
    lcDescrFld = 'ShtbtDescript'
    lcQtyFld = 'Shtbtamtin'
  *--- etc.
  ENDCASE
  
  IF !EMPTY(lcDescrFld)
    SELECT Ltable
    REPLACE (lcDescrFld) WITH MyResults.fc, (lcQtyFld) WITH MyResults.fb
    
    REPLACE blktpdin WITH blktpdin + MyResults.fb * blktpd
    
  ENDIF
  
ENDSCAN

Open in new window

Note: You should distinguish between append and replace/update terms.

Append always means "add new record" in database terminology.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
Note to your code:

Better than
APPEND FROM &CSVSHEET DELIMITED

is
APPEND FROM (CSVSHEET) DELIMITED

or possibly
APPEND FROM ("&CSVSHEET") DELIMITED

It allows folder and file names containing spaces. The & macro substitution always slows the code execution speed down but this not significant in your case.
Olaf DoschkeSoftware DeveloperCommented:
Totally unclear.

How does a CSV file look like, what's in it?
How does the ltable look like?

AS far as I can judge the data you import into NEWBATCH contains instructions on what to add where in some table "ltable", so you won't be able to alter the cursor and simply APPEND it. You'd need to process each imported record and see what it instructs to do in ltable and then do so. Especially if you want to add amounts to already existing data you won't be able to do that via APPENDS, you'll need updates. And since you intent to update different fields depending on data this is something you neither will do via ALTER and APPEND, nor a complex UPDATE-SQL

You need something like
SELECT NEWBATCH
SCAN
   Do Case
     Case somecondition1
        Update ltable set fieldx=fieldx+NEWBATCH.somfield WHERE somecondition
     Case somecondition2
        Update ltable set fieldy....
    ...
  EndCase
ENDSCAN

Open in new window


But maybe I still am far away of understanding your problem.

Bye, Olaf.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.