Create CSV File from data within a table

[benny7]

I want to select certain items from a table and put these into a csv file. The code below puts them into a txt file but has the heading and comma seperating the fields. Is it possible to send data straight to a csv file, with no headings from a table?

select barcode, Descr from plu to file "temp.txt" where upper(DESCR) like "%" + SEARCHDESC + "%

 

[Mike Lewis]

Benny,

How about this:

SELECT Plu
COPY TO temp.txt TYPE DELIM FOR upper(DESCR) $ SEARCHDESC

I think that'll do what you want.

Mike



Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[benny7]

Hi,

Once again thanks for this. I've used something similar but string contents have quotation marks. I have wrote a program to search this text file and remove the quotation marks. Is there a way to do it without these quotation marks?

 

[Mike Lewis]

Benny,

I suggest you study the Help for the COPY command (which is not the same as COPY FILE, by the way). There are several options for controlling the format of CSV files. Look at the various sub-options for TYPE and DELIMITED.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[TheRambler]

Hi,

Also, try SET HEADING OFF before SELECT, maybe that is all you need now.

 

[benny7]

hi,

setting heading off has helped. Problem I have now is getting the data into a txt/csv file seperated with only commas. The above method puts data into txt file but has massive spaces between each field, becomes un-readable.

I've been through the help, looking at copy to. it actually states in there that additional commas are added with the "copy" command. Don't want these, need to have file similar to below:

smith,andrew,27 walk road
jones,john,35 bun street

not

"smith","andrew","27 walk road"
"jones","john","35 bun street"

 

[Stella740pl]

benny7,

If you cannot find what you want in COPY TO, SELECT and other methods, you can always create the output you want looping through the table and sending records to your text file with StrToFile() function.
Take a look at thread184-938391. I think it's what you need.

Stella

 

[benny7]

Hi

Thanks to everyone for there help, I'm just about there now. However with the code below I now get the "press any
key to continue..." window. This apears for each record created in the text file. Can I stop this from happening?

SET HEADING OFF
SET SAFETY OFF
set talk OFF
STRPAR = "NAIL"
use e:\temp\rkb
select barcode, descr from RKB to file e:\temp\temp.txt where upper(DESCR) like "%" + STRPAR + "%"
messagebox("complete")

 

[StewartUK]

Benny,

I think you need to SET CONSOLE OFF before and SET CONSOLE ON afterwards.

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[benny7]

Thanks, I've just located that. Thanks anyway

 

[patyavila]

Don't know if you want to do this repeatedly. If so, try this:
Select the records using a make-query, for the first time export the new table to a text file and use the advanced features to create an export specification in which yo set the formatting you need (string qualifier, field delimiter).
After that you can use the specification many times if you save it and use:

DoCmd.TransferText acExportDelim, "Export Spec", "TblExport", filename, False

Hope this help... Patricia

 

[baltman]

Try This.

Brian

lcNewFile=[MyFile.csv]
lcTable=GETFILE('dbf')
lcFilter=[ for upper(DESCR) $ SEARCHDESC]
CLOSE ALL

lcHandle=fcreate(lcNewFile)
lcString=[]
lnMaxlen=0
USE (lcTable)

FOR ln = 1 TO FCOUNT()
 lcString=lcString+[^]+FIELD(ln)
ENDFOR
lcString=RIGHT(lcString,LEN(lcString)-1)

FPUTS(lcHandle,lcString)

SCAN &lcFilter

IF MOD(RECNO(),100)=0
 WAIT WINDOW NOWAIT [On record ]+TRANSFORM(RECNO())
ENDIF

lcString=[]
FOR ln = 1 TO FCOUNT()
 lcString=lcString+[,]+IIF(RTRIM(TRANSFORM(EVALUATE(FIELD(ln))))==[0],[],RTRIM(TRANSFORM(EVALUATE(FIELD(ln)))))
ENDFOR
lcString=RIGHT(lcString,LEN(lcString)-1)

lnMaxlen=MAX(lnMaxlen,LEN(lcString))
FPUTS(lcHandle,lcString)
ENDSCAN
CLOSE ALL

 

[baltman]

Or even more simply with:

COPY TO temp TYPE DELIMITED WITH , for upper(DESCR) $ SEARCHDESC