I am stuck again because I still ha 2

[EBOUGHEY]

I am stuck again because I still have to get the right data in the correct directories that I set up earlier with your assistance. Alas, my code is just not up to snuff...

example of data:

Field Name: SHELL / MAILDATE / JOBNO / NAME / ADDRESS / CSZ
Record1 Auto 20020802 NM7000 jdoe
Record2 Tire 20020802 NM7001 jdoe2

what I want to do is copy all of the data that has the job number NM7000 to C:\INKJET\AUTO\20020802\NM7000.DBF, all the data for NM7001 to C:\INKJET\TIRE\20020802\NM7001.DBF.

Would this be easy to do? I hate to have to ask again, but like I said, I am very, very rusty!

I would appreciate any help you may be able to give me.

Thank you,

Elena Boughey

 

[Mike Gagnon]

You could do :
select myTable
set filter to JOBNO = "NM7000"
copy to C:\INKJET\AUTO\20020802\NM7000.DBF
select myTable
set filter to JOBNO = "NM7001"
copy to C:\INKJET\TIRE\20020802\NM7001.DBF

 

[MCTCO]

try this
select [origintable]
set order to
scan for inlist(jobno,"NM7000","NM7001&quot
scatter memvar
if jobno=,"NM7000"
insert into NM7000 from memvar
tableupdate() &&if buffered
else
insert into NM7001 from memvar
tableupdate() &&if buffered
endif
endscan

this will ignore any other job numbers

 

[EBOUGHEY]

I need it to copy everything automatically because I could have literally hundreds of different job numbers in the file.

I'm not sure how to set it up exactly. I am copying all the unique job numbers to a file then setting a relation between the data file and job number file. After I set the relation I thought I could just copy the files by the variable LNEWDIR but I am getting an error "Variable LNEWDIR not found":

SET SAFE OFF
CLOS ALL
CLEAR
USE TEMP
INDEX ON JOBNO UNIQ TO T
COPY TO jobno
CLOS ALL
sele a
use jobno
index on shell+jobno to t
sele b
use temp
set rela to shell+jobno into a
go top
lcprefix = "C:\INKJET\"
lcNewDir = lcPrefix+alltrim(B.SHELL)+"\";
+DTOS(B.MAILDATE)
jobFILE = B.JOBNO
COPY TO LNEWDIR+"\"+B.JOBFILE+".DBF" FOR !EOF(1)
ENDFOR
SET SAFE ON

 

[Leland123]

Hi EBOUGHEY,

It appear your copy to line is giving you an error because it does not have a valid path/file. You might try stepping through this line of code with the debugger. Place the COPY TO LNEWDIR+"\"+B.JOBFILE+".DBF" FOR !EOF(1)
in the debugger's watch window to see how the program is interpreting it.
Leland F. Jackson, CPA
Software - Master (TM)

https://www.smvfp.com

Nothing Runs Like the Fox

 

[wgcs]

Give this code a try.
Some comments, first:
Note the ALLTRIM(temp.JobFile)... any spaces in the field ended up before the ".DBF" before.
Note the MD command, it makes sure the directory is there if it wasn't before.
The COPY TO ... WHILE ... command leaves the current record as the record after the last one copied to the file, or at EOF().

lcprefix = "C:\INKJET\"

SET SAFETY OFF
CLOSE ALL
CLEAR
USE TEMP
INDEX ON JOBNO TAG JobNo
lcOldJobNo = Temp.JobNo
DO WHILE NOT EOF('TEMP')
  lcNewDir = lcPrefix+alltrim(B.SHELL)+"\";
               +DTOS(B.MAILDATE)+"\"
  lcNewFile = lcNewDir+ALLTRIM(temp.JobFile)+".DBF"
  IF NOT Directory(lcNewDir)
    MD (lcNewDir)
  ENDIF
  COPY TO (lcNewFile) WHILE temp.JobNo=lcOldJobNo
  lcOldJobNo = temp.JobNo
ENDDO
SET SAFETY ON

 

[rgbean]

While I haven't checked all of wgcs's code, you'll at least need a SKIP before the ENDDO to process all the records in TEMP, or change this to a SCAN ... ENDSCAN loop.

Rick

 

[wgcs]

Rick, the SKIP isn't necessary: The COPY TO properly moves the record and leaves the rec pointer to the next record to process (the first record that HASN'T yet been copied), which is exactly where we want to be when evaluating the WHILE condition. Read my notes at the beginning of the post.

 

[danceman]

I would run a select DISTINCT on the table to retrive the job numbers.

SELECT DISTINCT JOBNO FROM jobtable INTO CURSOR tempjob

then loop thru this cursor to then run a cursor for the values needed.

SCAN

SELECT what ever FROM jobtable WHERE jobtable.jobno = tempjob.jobno INTO TABLE newtable

ENDSCAN

I did not repeat the privious info provided for creating the directories. before running select make your directory, change the directory run the select into table.
Attitude is Everything

 

[EBOUGHEY]

I keep coming up with the error "Expression is not valid outside of WITH/ENDWITH". I've tried to fix this using different scenarios to no avail.


Current Code (set up for working environment):

** Created By Elena Boughey
** Assistance by Tek-Tips Forum /

http://www.tek-tips.com

** Date: 8-21-2002
** Note the ALLTRIM(NEWMOVER_EXPORT.JobFile)... any spaces in the field ended up before the ".DBF" before.
** Note the MD command, it makes sure the directory is there if it wasn't before.
** The COPY TO ... WHILE ... command leaves the current record as the record after the last one copied to the file, or at EOF().


lcprefix = "N:\INKJET\NEWMOVER\"
SET DEFAULT TO N:\INKJET\NEWMOVER\HOUSE

SET SAFETY OFF
CLOSE ALL
CLEAR
USE NEWMOVER_EXPORT
INDEX ON JOBNO TAG JobNo
lcOldJobNo = NEWMOVER_EXPORT.JobNo
SCAN
lcNewDir = lcPrefix+alltrim(.SHELL)+"\";
+DTOS(.MAILDATE)+"\"
lcNewFile = lcNewDir+ALLTRIM(NEWMOVER_EXPORT.JobFile)+".DBF"
IF NOT Directory(lcNewDir)
MD (lcNewDir)
ENDIF
COPY TO (lcNewFile) WHILE NEWMOVER_EXPORT.JobNo=lcOldJobNo
lcOldJobNo = NEWMOVER_EXPORT.JobNo
ENDSCAN
SET SAFETY ON

 

[DSummZZZ]

.SHELL and .MAILDATE are your culprits. Get rid of the '.' or use the table name:

alltrim(SHELL) or alltrim(NEWMOVER_EXPORT.SHELL )

etc.
Dave S.

 

[EBOUGHEY]

Alrighty then!!! I have the entire program running properly thanks to all of you...

In addition to having job number files under the shell, I had to have a file that combined those job numbers too.

Here is my final Code:

lcprefix = "N:\INKJET\NEWMOVER\"
SET DEFAULT TO N:\INKJET\NEWMOVER\HOUSE

SET SAFETY OFF
CLOSE ALL
CLEAR
USE NEWMOVER_EXPORT
INDEX ON JOBNO TAG JobNo
lcOldJobNo = NEWMOVER_EXPORT.JobNo
SCAN
lcNewDir = lcPrefix+alltrim(SHELL)+"\";
+DTOS(MAILDATE)+"\"
lcNewFile = lcNewDir+ALLTRIM(NEWMOVER_EXPORT.JobNo)+".DBF"
IF NOT Directory(lcNewDir)
MD (lcNewDir)
ENDIF
COPY TO (lcNewFile) WHILE NEWMOVER_EXPORT.JobNo=lcOldJobNo
lcOldJobNo = NEWMOVER_EXPORT.JobNo
ENDSCAN

INDEX ON SHELL TAG Shell
lcOldShell = NEWMOVER_EXPORT.Shell
SCAN
lcNewDir = lcPrefix+alltrim(SHELL)+"\";
+DTOS(MAILDATE)+"\"
lcNewFile = lcNewDir+"PRESORT.DBF"
IF NOT Directory(lcNewDir)
MD (lcNewDir)
ENDIF
COPY TO (lcNewFile) WHILE NEWMOVER_EXPORT.Shell=lcOldShell
lcOldShell = NEWMOVER_EXPORT.Shell
ENDSCAN

SET SAFETY ON

Again, I cannot express my gratitude to all of you enough.... Many Thanks!

Elena

 

[wgcs]

Okay, one more fix. Take out the SCAN..ENDSCAN and put back the DO WHILE .. ENDDO. As it is now, your output files (except for the very first one) are all missing their first record, because the ENDSCAN skips it before it can be output by the COPY TO ... WHILE.

Sorry for not correcting the "B." alias reference in my code sample.. that led to the latest issue. Thenks DSummZZZ for pointing it out.

- Bill

 

[EBOUGHEY]

Now that you mention it, that's exactly what it is doing.

Doesn't matter whether I use DO WHILE or SCAN though. It always misses one record...

 

[EBOUGHEY]

Never Mind.... Fixed.

Thanks again and no bother on the sample code. I pulled out the "B" but not the period.

 

[DSummZZZ]

I think you would be better off doing it more like:

USE NEWMOVER_EXPORT
INDEX ON JobNo TAG JobNo
SELECT DISTINCT JobNo ;
FROM NEWMOVER_EXPORT;
ORDER BY JobNo ;
INTO CURSOR JobNo

SELECT JobNo
SET RELATION TO JobNo INTO NEWMOVER_EXPORT

SCAN
SELECT NEWMOVER_EXPORT
lcNewDir = lcPrefix+alltrim(SHELL)+"\";
+DTOS(MAILDATE)+"\"
lcNewFile = lcNewDir+ALLTRIM(NEWMOVER_EXPORT.JobNo)+".DBF"
IF NOT Directory(lcNewDir)
MD (lcNewDir)
ENDIF
COPY TO (lcNewFile) FOR NEWMOVER_EXPORT.JobNo=JobNo.JobNo
SELECT JobNo
ENDSCAN

And so on.
Dave S.

 

[wgcs]

Use the DO WHILE, But don't put in any SKIP commands, it's NOT necessary... the COPY TO does the record pointer movement...

When dealing with thousands of records, it'll be much faster than DSummZZZ's suggestion: Each COPY TO...FOR will pass through the entire file, while the COPY TO...WHILE just continues through the next section then stops.

Basically, the operation has, with DSummZZZ's algorithm, an "N-Squared" completion time (where N is the # records to be processed), while using COPY TO..WHILE has an "N" completion time. It's several orders of magnitude faster to use WHILE.

 

[EBOUGHEY]

I've left it as is... It is working very quickly. One of my main concerns was that I am dealing with upwards of 100,000 records at any given time and speed will be important.


Final Code:

*** CREATED BY ELENA BOUGHEY / WITH THE HELP OF TEK-TIPS GURUS
*** DATE: 08-20-2002
*** THIS PROGRAM EXPORTS THE DATA FILES ALONG WITH THE
*** ZIP CODES THAT HAVE BEEN ASSIGNED TO THE CLIENT FOR LASER PROCESSING


*** SETS DEFAULT TO NEWMOVER HOUSE DIRECTORY

SET DEFAULT TO N:\INKJET\NEWMOVER\HOUSE


*** SELECTS THE DATA FROM 2 FILES:
*** [NEWMOVER.DBF - INFOUSA NEW MOVERS FILE]
*** [EXPORT.DBF - DATABASE EXPORT FILE FROM ACCESS FOR JOBS]
*** ONLY OPEN STATUS JOBS [STATUS = 'O']

*** SEPARATES THE NEW MOVER DATA BY SHELL/JOBNO AND EXPORTS IT TO NEWMOVER_EXPORT.DBF

SET FILTER TO status = 'O'
SELECT DISTINCT Export.shell, Export.jobno, Newmover.zip, Newmover.first,;
Newmover.last, Newmover.address_1, Newmover.city, Newmover.state,;
Newmover.phone, Newmover.maildate, Newmover.list_no, Newmover.origin,;
Newmover.vendor, Newmover.dp2, Newmover.dpc, Newmover.crrt, Newmover.lot,;
Newmover.lot_ord, Newmover.company, Export.zip1, Export.zip2, Export.zip3,;
Export.zip4, Export.zip5, Export.zip6, Export.zip7, Export.zip8, Export.zip9,;
Export.zip10, Export.status;
FROM export INNER JOIN newmover ;
ON Export.zip1 = Newmover.zip;
OR Export.zip2 = Newmover.zip;
OR Export.zip3 = Newmover.zip;
OR Export.zip4 = Newmover.zip;
OR Export.zip5 = Newmover.zip;
OR Export.zip6 = Newmover.zip;
OR Export.zip7 = Newmover.zip;
OR Export.zip8 = Newmover.zip;
OR Export.zip9 = Newmover.zip;
ORDER BY Export.jobno, Newmover.address_1;
INTO TABLE newmover_export.dbf
REPLACE ALL LIST_NO WITH JOBNO

*** THIS SECTION CREATES AND COPIES THE DIRECTORIES AND FILES TO THE PROPER LOCATION
*** Note the ALLTRIM(NEWMOVER_EXPORT.JobFile)... any spaces in the field ended up before the ".DBF" before.
*** Note the MD command, it makes sure the directory is there if it wasn't before.
*** The COPY TO ... WHILE ... command leaves the current record as the record after the last one copied to the file, or at EOF().


lcprefix = "N:\INKJET\NEWMOVER\"
SET DEFAULT TO N:\INKJET\NEWMOVER\HOUSE

SET SAFETY OFF
CLOSE ALL
CLEAR
USE NEWMOVER_EXPORT
INDEX ON JOBNO TAG JobNo
lcOldJobNo = NEWMOVER_EXPORT.JobNo
GO TOP
DO WHILE NOT EOF('NEWMOVER_EXPORT') AND STATUS = 'O'
lcNewDir = lcPrefix+alltrim(SHELL)+"\";
+DTOS(MAILDATE)+"\"
lcNewFile = lcNewDir+ALLTRIM(NEWMOVER_EXPORT.JobNo)+".DBF"
IF NOT Directory(lcNewDir)
MD (lcNewDir)
ENDIF
COPY TO (lcNewFile) WHILE NEWMOVER_EXPORT.JobNo=lcOldJobNo
lcOldJobNo = NEWMOVER_EXPORT.JobNo
ENDDO

*** COMBINE RECORDS BY SHELL AND COPY DATA TO PRESORT.DBF FOR EACH SHELL

INDEX ON SHELL TAG Shell
lcOldShell = NEWMOVER_EXPORT.Shell
DO WHILE NOT EOF('NEWMOVER_EXPORT') AND STATUS = 'O'
lcNewDir = lcPrefix+alltrim(SHELL)+"\";
+DTOS(MAILDATE)+"\"
lcNewFile = lcNewDir+"PRESORT.DBF"
IF NOT Directory(lcNewDir)
MD (lcNewDir)
ENDIF
COPY TO (lcNewFile) WHILE NEWMOVER_EXPORT.Shell=lcOldShell
lcOldShell = NEWMOVER_EXPORT.Shell
ENDDO

SET SAFETY ON

 

[wgcs]

Looks Good!

 

[DSummZZZ]

Did I say COPY TO FOR... ? I meant COPY TO WHILE...
Sorry.

The reason I suggested it that way is that you wouldn't be skipping records in the actual source table. But if you got working, then peachy!
Dave S.