How to create a file that will include the DATE?

[smsmail]

Hello,

My program creates a number of CSV files. I need to create files that will include DATE and TIME stamp at the beginning or end of the file name. Is this possible?

Thank you so much for your help!

 

[GriffMG]

If you CSV files are less than 2GB, it's easy to put a time and date at the end:

Function DateStamp
  Parameters cFileName
  Private cFileName
  StrToFile(chr(13)+chr(10)+dtoc(date)+" "+Time()+chr(13)+chr(10),cFileName,.t.)
Return(.t.)

Just pass the function the full path to the file... you could add some error checking if you like!

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Just put DTOS() to the filename, eg

Filename = "..."+DTOS(DATE())
COPY TO (Filename) ... TYPE CSV ...

Bye, Olaf.

 

[GriffMG]

Doh, misread that one!

He wants it in the filename!

Filename = "..."+DTOS(DATE())+strtran(time(),":","")
COPY TO (Filename) ... TYPE CSV ...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Actually, I think TTOC(... ,1) would be better here. It gives you the full date and timestamp in one go, and is sensitive to your date and time settings:

Filename = "..." + TTOC(DATETIME(), 1)
COPY TO (Filename) ... TYPE CSV ...

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[Olaf Doschke]

Yes, Mike, I also overlooked TIME, as it was not in the thread title.

Bye, Olaf.

 

[smsmail]

First, I would like to thank you all for your help.

Please excuse me, I am new to FoxPro.

My code follows (in this instance I am creating a text file):

select * ;
from eCustomer_tbl ;
where branch = '01' ;
into table eBranch1_Customer_tbl.dbf ;
order by email

use c:\dp\eBranch1_Customer_tbl.dbf exclusive
if .not. eof() then
alter table dbf() alter column birthdate c(15)
alter table dbf() alter column zip c(15)
alter table dbf() alter column state c(15)
alter table dbf() alter column branch c(6)
go top
insert blank before
replace email with "EMAIL"
replace branch with "BRANCH"
replace name with "NAME"
replace address with "ADDRESS"
replace city with "CITY"
replace state with "STATE/PROVINCE"
replace zip with "ZIP/POSTAL CODE"
replace hphone with "hphone"
replace phone2 with "PHONE2"
replace birthdate with "BIRTHDATE"
replace custnbr with "CUSTNO"
COPY TO "\\Server\h-drive\Lyris\Reedman Toll\Reedman Toll Store 1 - Chevy\Branch1-Weekly-Customers-Roclose.txt" Delimited with CHARACTER ,
endif


Will the filename include the entire path?

Filename = "\\Server\h-drive\Lyris\Reedman Toll\Reedman Toll Store 1 - Chevy\Branch1-Weekly-Customers-Roclose+TTOC(DATETIME(), 1 .txt"

Or just the following:

Filename = "Branch1-Weekly-Customers-Roclose-+TTOC(DATETIME(), 1 "

COPY TO "\\Server\h-drive\Lyris\Reedman Toll\Reedman Toll Store 1 - (Filename).txt" Delimited with CHARACTER ,

 

[GriffMG]

You aren't quite reading it correctly:

Filename = "\\Server\h-drive\Lyris\Reedman Toll\Reedman Toll Store 1 - Chevy\Branch1-Weekly-Customers-Roclose"+TTOC(DATETIME(), 1)+".txt"   

COPY TO (Filename) Delimited with CHARACTER ,

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Mike Lewis]

Smsmail,

In fact, you were quite close. It was just a question of where you put the quotes - as Griff's example shows.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[smsmail]

It works perfectly. Thank you all so very much for your help.

I am so glad I found this sight!

Have a great day!

 

[Olaf Doschke]

Let me suggest some other changes:

#Define ccPath "\\Server\h-drive\Lyris\Reedman Toll\Reedman Toll Store 1 - Chevy\"
#Define ccFilename "Branch1-Weekly-Customers-Roclose"+TTOC(DATETIME(), 1)+".txt"
 
Function ExportTable()
   
select * ;
  from eCustomer_tbl ;
 where branch = '01' ;
  into table eBranch1_Customer_tbl.dbf ;
order by email

* You have the table open already, as you created it!
* use c:\dp\eBranch1_Customer_tbl.dbf exclusive

if .not. eof() then
   alter table eBranch1_Customer_tbl;
   alter column ;
     birthdate c(15),; 
     zip c(15),;
     state c(15),;
     branch c(6)

   go top
   insert blank before

   replace email with "EMAIL", ;
    branch with "BRANCH", ;
    name with "NAME", ;
    address with "ADDRESS", ;
    city with "CITY", ; 
    state with "STATE/PROVINCE", ;
    zip with "ZIP/POSTAL CODE", ;
    hphone with "hphone", ;
    phone2 with "PHONE2", ;
    birthdate with "BIRTHDATE", ;
    custnbr with "CUSTNO"

   COPY TO (ccPath+ccFilename) Delimited with CHARACTER ,  
endif

This is just cleaning your code a litte. Each ALTER TABLE you do rewrites the full table, each replace writes the first record. The repeated replace is not that big a performance killer, but you can do both in one step each, ALTER TABLE allows you to change many columns at once. Unless you're only new to foxpro and not to SQL, you should know.

REPLACE like SQL UPDATE can also do replace many fields in one shot.

There is one disadvantage in doing this the way you do: Not every numeric, date or other field will gracefully convert into a char field for doing the INSERT BEFORE and inserting the captions into the first record. So this will only work in limited cases of table structures.

Also INSERT BEFORE in itself again rewrites the full dbf, so even with ALTER TABLE packed into one line and INSERT BEFORE you write the dbf data twice, then output it with COPY TO a third time. you don't save time and performance, you waste it.

You could rather first do the COPY TO without any changes and then rewrite the text file once with the captions and would only output all the rows twice instead of thrice or like you do, sic times (4x alter table, 1x insert before, 1x copy to)

Bye, Olaf.

 

[Olaf Doschke]

Sorry, I made a mistake myself. The ALTER TABLE should look like this:

alter table eBranch1_Customer_tbl;
   alter column birthdate c(15); 
   alter column zip c(15);
   alter column state c(15);
   alter column branch c(6)

Bye, Olaf.

 

[smsmail]

Thank you Olaf....I will definitely use your suggestions.

I will be back with more questions..!