Navigate to directory

[emboughey]

I can't figure out how to allow someone to navigate to a specific directory to start the program.

Can anyone help?

I'm using the following syntax type.... (old stuff I know)


@ 5,5 say 'Enter Directory to convert data from: '

thefile = getdir()???
use alltrim(lower(thefile))


Thanks is advance.

Eboughey

 

[emboughey]

This is the program I'm writing. I have no idea even where to put the getdir() so it can append from all the files in that directory. They are in two different locations...



SET TALK OFF
SET SAFETY OFF
set defa to c:\working\premier
USE east-cent
ZAP

lcPathAndMask = "C:\working\PREMIER\*.xls"
lcFile = Sys(2000, lcPathAndMask)
Do While !Empty(lcFile)
APPEND FROM (lcFile) DELIMITED WITH tab
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)
ENDDO
SET TALK ON

DO c:\working\premier\multi-import1.prg

 

[emboughey]

I'm to here now. Getting an error with the lcfile though... Any advice at all would be greatly appreciated.


@ 5,5 say 'Enter Directory to get data from: '

thedir = getdir()

SET TALK OFF
SET SAFETY OFF
USE east-cent
ZAP

lcPathAndMask = thedir+"\*.xls"
lcFile = Sys(2000, lcPathAndMask)
Do While !Empty(lcFile)
APPEND FROM (lcFile) DELIMITED WITH tab
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)
ENDDO
SET TALK ON

 

[Zografski]

Try this:

lcMyDbf = GETFILE("dbf","Find dbf")
IF EMPTY(lcMydbf)
MESSAGEBOX("No choice")
RETURN
ENDIF

USE (lcMyDbf)
BROWSE NORMAL

 

[Mike Lewis]

Hi Eboughey,

It's probably because you have an extra backslash between thedir and the filename.

GETDIR() return a directory name with a trailing backslash. When you do this:

lcPathAndMask = thedir+"\*.xls"

you are adding a second backslash, which is probably causing the error.

Also, it's much easier for us to diagnose these problems if you tell us what the error message is, and on which line. Merely to say "Getting an error with the lcfile" is not very helpful.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

emboughey,
Are you familiar with the SET STEP ON Command ???


David W. Grewe Dave

 

[brigmar]

Using ADIR to get your list of files, as opposed to the SYS(2000) call, makes the loop easier:

SET TALK OFF
thedir = ADDBS(GETDIR(SYS(5)+SYS(2003),"Locate the data directory"))
USE east-cent EXCLUSIVE
SET SAFETY OFF
ZAP
SET SAFETY ON

FOR i = 1 TO ADIR( aFiles, thedir + "*.xls" )
  lcFile = aFiles[i]
  APPEND FROM (lcFile) DELIMITED WITH tab
  REPLACE filename WITH (lcfile) FOR EMPTY(filename)
NEXT i

SET TALK ON

 

[dgrewe]

THere is a function is this forums FAQ area that does what you want
faq184-6591

David W. Grewe Dave

 

[emboughey]

Thanks everyone for your help!

I know it seemed like a really simple question, but I've been away from the programming side for awhile and find myself quite rusty...

I'm setting several programs (almost identical) into action through this one because I have several database structures. I won't ask now because I need to look at my loops first and re-teach myself but it does the job for the time being.

THANKS AGAIN!

 

[emboughey]

Here's the code I've written....

The copy command (lcnewfile) doesn't work though. I want to actually get the date to print first ie:06282007_importfiles.csv, but I just can't get it to work for me.




*** Premier File Import, Data Work, and Export Program
*** Created by Emboughey
*** 06/28/2007
*** This program imports multiple files with different structures into separate databases
*** This program then combines them into one file, performs data work for back end reporting
*** and exports the data to a file to be sorted for the best postal discounts


*** Standard commands during foxpro program

CLOSE ALL
clear
SET TALK OFF
SET SAFETY OFF
lcprefix = "\\Lettershop\SharedDocs\DP\#DAYTONA-NEW\"

*** Asks for the directory that files are to be imported from

@ 5,5 say 'Enter Directory to import files from: '

thedir = GETDIR()


*** This section imports multiple files to the different table structures provided by client.

USE east-cent.dbf
ZAP

lcPathAndMask = thedir+"*live.xls"
lcFile = Sys(2000, lcPathAndMask)

Do While !Empty(lcFile)
APPEND FROM (lcFile) DELIMITED WITH tab
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)

USE dr-files
zap
lcPathAndMask = thedir+"dr*.csv"
lcFile = Sys(2000, lcPathAndMask)
Do While !Empty(lcFile)
APPEND FROM (lcFile) DELIMITED
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)

USE newleads
zap
lcPathAndMask = thedir+"new leads.xls"
lcFile = Sys(2000, lcPathAndMask)
Do While !Empty(lcFile)
APPEND FROM (lcFile) XLS
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)

USE resends
zap
lcPathAndMask = thedir+"resend*.xls"
lcFile = Sys(2000, lcPathAndMask)
Do While !Empty(lcFile)
APPEND FROM (lcFile) XLS
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)

USE brown
zap
lcPathAndMask = thedir+"brown*.xls"
lcFile = Sys(2000, lcPathAndMask)
Do While !Empty(lcFile)
APPEND FROM (lcFile) XLS
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)

USE canada
zap
lcPathAndMask = thedir+"can*.csv"
lcFile = Sys(2000, lcPathAndMask)
Do While !Empty(lcFile)
APPEND FROM (lcFile) DELIMITED
replace filename WITH (lcfile)for EMPTY(filename)
lcFile = Sys(2000, lcPathAndMask, 1)
ENDDO
ENDDO
ENDDO
ENDDO
ENDDO
ENDDO
SET TALK ON
CLOSE ALL
CLEAR

*** Import data from all files into main file for export

USE PREMIER-IN
ZAP
APPEND FROM east-cent
APPEND FROM dr-files
APPEND FROM brown
APPEND FROM newleads
APPEND FROM resends
APPEND FROM canada


*** Datawork. Clean up fields and update for reporting later***

DELETE FOR EMPTY(first) AND EMPTY(last)
REPLACE ALL DPDATE WITH DTOC(DATE())
REPLACE ALL FILENAME WITH UPPER(FILENAME)
REPLACE ALL ORIGIN WITH FILENAME
PACK

*** Updates filename for reporting purposes

repL all filename with 'NY' FOR LEFT(FILENAME,4) = 'DRNY'
repL all filename with 'FL' FOR LEFT(FILENAME,4) = 'DRFL'
REPLACE ALL FILENAME WITH 'CENTRAL' FOR 'PREMCENT'$FILENAME
REPLACE ALL FILENAME WITH 'BROWN' FOR 'BROWN'$FILENAME
REPLACE ALL FILENAME WITH 'EAST' FOR 'PREMEAST'$FILENAME
REPLACE ALL FILENAME WITH 'NEW LEADS' FOR 'NEW LEADS'$FILENAME
REPLACE ALL FILENAME WITH 'RESENDS' FOR 'RESEND'$FILENAME
REPLACE ALL FILENAME WITH 'EAST' FOR 'SERVICE CARD'$FILENAME

*** Updates the table with 'EAST' or 'CENT' depending on filename (state for New Leads and Resends)

REPLACE ALL CENTEAST WITH 'EAST' FOR FILENAME = 'NY'
REPLACE ALL CENTEAST WITH 'EAST' FOR FILENAME = 'NY WEB'
REPLACE ALL CENTEAST WITH 'CENT' FOR FILENAME = 'CENTRAL'
REPLACE ALL CENTEAST WITH 'EAST' FOR FILENAME = 'EAST'
REPLACE ALL CENTEAST WITH 'EAST' FOR FILENAME = 'BROWN'
REPLACE ALL CENTEAST WITH 'EAST' FOR FILENAME = 'FL'
REPLACE ALL CENTEAST WITH 'CENT' FOR FILENAME = 'BROWN'
REPLACE ALL CENTEAST WITH 'CENT' FOR FILENAME = 'FL'
REPLACE ALL CENTEAST WITH 'CENT' FOR 'RESEND'$FILENAME

a="CT-NH-NJ-NY-MI-RI-VT"
REPLACE ALL CENTEAST WITH 'EAST' FOR FILENAME = 'NEW LEADS' AND STATE$A
REPLACE ALL CENTEAST WITH 'CENT' FOR FILENAME = 'NEW LEADS' AND EMPTY(CENTEAST)
REPLACE ALL CENTEAST WITH 'EAST' FOR FILENAME = 'RESENDS' AND STATE$A
REPLACE ALL CENTEAST WITH 'CENT' FOR FILENAME = 'RESENDS' AND EMPTY(CENTEAST)



*** Appends the job number and Week/Drop (WK26-DP5)

CLIST_NO = SPACE (10)
CMAILDATE = SPACE (15)

@ 11,5 say 'Enter Job Number: ' get CLIST_NO
@ 15,5 say 'Enter Week and Drop: ' get CMAILDATE
READ

repl all list_no with alltrim(Clist_no)
REPLACE ALL WKDROP WITH ALLTRIM(CMAILDATE)

*** exports the merged, clean data to a file for import into BCC and presort.


lcnewfile = lcprefix+ALLTRIM(dpdate)+"importfiles.csv"
COPY TO (lcnewfile)csv


SET SAFETY on
SET TALK on
CLOSE ALL
CLEAR

 

[Olaf Doschke]

lcnewfile=lcprefix+dtos(date())+"importfiles.csv"
copy to (lcNewfile)

not

copy to (lcnewfile)csv

Bye, Olaf.

 

[Stella740pl]

Shouldn't it be

COPY TO (lcnewfile) TYPE CSV

 

[Olaf Doschke]

Of course, Stella, othewise it's a dbf with csv extension.

Bye, Olaf.