Solved

Alter Cursor Results

Posted on 2014-10-06
4
137 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

910 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

22 Experts available now in Live!

Get 1:1 Help Now