Solved

Alter Cursor Results

Posted on 2014-10-06
4
125 Views
Last Modified: 2014-11-07
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
0
Comment
Question by:campbme
  • 2
4 Comments
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 40406080
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.
0
 
LVL 41

Expert Comment

by:pcelba
ID: 40406133
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.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40406417
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.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now