export delimiters

[USMA99]

Is there a way to specify a delimiter for the fields on export, but NOT have a framing character. If I want pipe-delimited such as this:

name|address|city|state|zip

I tried all types of derivatives of the copy to command such as "copy to filename delimited with char "|"" for example, but I get a default framing character. How do I specify that there be no framing character?

 

[Mike Gagnon]

USMA99

There seems to be a different between

COPY TO c:\test2 DELIMITED WITH "|"
and
COPY TO c:\test2 DELIMITED WITH CHARACTER "|"

Try the first version.
Mike Gagnon

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

 

[ramani]

Hi
COPY TO test.txt DELIMITED WITH tab WITH "|"
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[Mike Gagnon]

Ramani

COPY TO test.txt DELIMITED WITH tab WITH "|"

I get a weird result with your suggestion if the fields are blank.
Mike Gagnon

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

 

[USMA99]

I tried both:

COPY TO c:\test.txt DELIMITED WITH "|"
and
COPY TO c:\test2.txt DELIMITED WITH CHARACTER "|"

the first yields a comma delimited file with framing characters of "|"

the second yields a "|" delimited file with framing characters of double quotes

I am after a delimter of "|" with no framing character

 

[baltman]

Convert your "|" to | using this program

&&&
lnNewFile=fcreate("c:\test2_output.txt&quot
lnhandle= fopen("c:\test2.txt&quot
do while not Feof(lnHandle)
lcString = chrtran(fgets(lnHandle,1000),chr(34),"&quot
= fput(lnNewFile , lcString)
enddo
close all
&&&

Brian

 

[monkey666]

morning,

I initially thought of something similar to Brians idea of replacing the " in the file with 'nothing' - but on many occasions with client supplied delimited files the actual data may contain the " character e.g around house names which they insist on maintaining.

The replace would work but be aware that it will replace all instances of the " character. A more exact way of performing the replace would be to replace the entire ' "¦" ' character string with 'nothing'.

monkey

 

[Neil Toulouse]

It looks to me like you need to create the export file yourself so you can get the level of control you need.

Take a look in the help at FCREATE(), FPUTS() etc

HTH
Neil "I like work. It fascinates me. I can sit and look at it for hours..."

 

[Neil Toulouse]

An example. Adjust to your own needs:

LOCAL ARRAY laFields[1]
LOCAL lcValue, lcStr, lnFor, lcField, lnHandle, lcFile

lcFile = 'C:\EXPORT.TXT'

IF FILE(lcFile)
DELETE FILE &lcFile
ENDIF

lnHandle = FCREATE( lcFile, 0 )

DIMENSION laFields[1]

CREATE CURSOR curTest ( CHAR C(1), Num N(1), DATE D(8) )
INSERT INTO curTest VALUES ( 'A', 1, {11/10/1970} )
INSERT INTO curTest VALUES ( 'B', 2, {11/11/1970} )
INSERT INTO curTest VALUES ( 'C', 3, {11/12/1970} )
INSERT INTO curTest VALUES ( 'D', 4, {11/01/1971} )

SELECT curTest
SCAN

lcStr = ''

FOR lnFor = 1 TO AFIELDS( laFields, 'curTest' )

lcField = 'curTest.' + FIELD( lnFor, 'curTest' )
lcValue = &lcField

DO CASE

CASE Type('lcValue') == 'C'
lcStr = IIF(EMPTY(lcStr), ALLTRIM(lcValue), ALLTRIM(lcStr) + '|' + ALLTRIM(lcValue))

CASE Type('lcValue') == 'N'
lcStr = IIF(EMPTY(lcStr), ALLTRIM(STR(lcValue)), ALLTRIM(lcStr) + '|' + ALLTRIM(STR(lcValue)))

CASE Type('lcValue') == 'D'
lcStr = IIF(EMPTY(lcStr), ALLTRIM(DTOC(lcValue)), ALLTRIM(lcStr) + '|' + ALLTRIM(DTOC(lcValue)))

ENDCASE

ENDFOR

FPUTS( lnHandle, ALLTRIM(lcStr) )

SELECT curTest
ENDSCAN

FCLOSE( lnHandle )

CLOSE ALL


HTH
Neil "I like work. It fascinates me. I can sit and look at it for hours..."