Additional fields in table exported to txt

Additional fields in table exported to txt

• aligatrjoe

  I don't think you can exactly what you want in all circumstances using COPY TO (or any variant of it). COPY TO depends on rule-based processing which relies on data consistency - if you have inconsistent data then you cannot use it. Sounds to me like you need to do this manually and check each compnent individually.

  Just use a SCAN loop to process all records, a FOR...NEXT loop to iterate through the fields in each record and create the output string yourself. That way you can handle things like embedded quotes explicitly. Then use STRTOFILE() to write out each 'record' as you finish.

  The following code snippet will dump the contents of the TasTrade customer table to a comma separated text file and should work in any version of VFP from 7.0 onwards (if not, check the STRTOFILE() options - they were different in VFP 6) and should give you the basis to do what you want....

  CLOSE ALL
  IF FILE( "output.txt" )
  DELETE FILE output.txt
  ENDIF
  USE ( HOME() + "SAMPLES\TASTRADE\DATA\CUSTOMER.DBF" )
  SCAN
  lcOutStr = ""
  FOR lnCnt = 1 TO FCOUNT()
  *** Get field name
  lcField = FIELD( lnCnt )
  *** Get field values as a character string
  lcVal = ALLTRIM( TRANSFORM( &lcField ))
  *** Remove embedded quotes, or other unwanted stuff
  lcVal = CHRTRAN( lcVal, [",'-/~{}], "" )
  *** Create output string, with "," separator
  lcOutStr = lcOutStr + IIF(EMPTY(lcOutStr), "", "," )
  lcOutStr = lcOutStr + lcVal
  NEXT
  *** Write the string out - don't forget CRLF!!!
  STRTOFILE( lcOutStr + CHR(13) + CHR(10), 'Output.txt', 1 )
  ENDSCAN
  MODIFY FILE output.txt nowait
  

  
  
  

• gbeez

  Glad it helped. If your output files are not large I think you can simply use such a code. If they're large (at least several megabytes) then visit universalthread.com for a FLL solution (as I remember though original code is old and not searchable, Naomi Nosonovsky created an FAQ or download entry for it, along with details how to create the FLL).
  

• Lebowski

  Hi Dave,

  Thanks for your message. I sometimes use copy to csv but the data is address data and the text file is used by software running an inkjet machine. Where there are elements of the address that are enclosed within double quotes, these are enclosed again within a further set of double quotes and this causes a problem with the inkjet software. For example a house name <<"Dunroamin">> becomes <<""Dunroamin"">> when I copy to csv. I need to avoid these double, double quotes.

  Paul.

  
  
  

• Mahyar154572

  copy to mytext.txt type delimited
  

• don wingate

  Hi Cetin

  >>Copy to with a delimiter that doesn't exist in data normally (ie: a tilde)

  Ouch! That's a dangerous assumption! I certainly have data that contains the tilde character!

  In fact it is very widely used as an "embedded separator" to indicate where breaks should be inserted when porting data in character (or even sometimes in XML) format. For example the following is the result string posted (in an XML feed) from a web page to a middle tier component:

  cur_person*psn_pk~12*psn_nme~James Jameson*dob_dte~02/14/1985<br />

  Note that both tilde and asterisk are used here as embedded separators. Applying CHRTRAN() on these characters would destroy this data.

  This is always the problem with any 'global' solution - which is why I prefer the parser-based (interpretive) approach to these sorts of issues. It takes a little more code but the result is definitely more controllable because you can use conditional logic to determine whether or not a character is significant in a given context.

  
  
  

• Tyde Inside

  Hi,

  Thanks for this but I have the same problem with double, double quotes as I have described in my reply to Dave.

  
  
  

• GhostWalker 28

  Thanks Cetin,

  That is the simplest solution.

  Paul.

  
  
  

• Shanknbake

  Hi, you can try this :

  COPY TO test.txt DELIMITED WITH "" WITH CHARACTER ","

  
  
  

• Rahul.Bhot

  It sounds Like ypu want as CSV File...
  
  Why not use "copy to mytext.txt type csv"
  
  Dave
  
  
  
  

• Prakash1

  Thanks Andy,

  You are absolutely right. The data is valid and is consistent with addressing methods but the software running the inkjet doesn't see it this way.

  Thanks again for all your help.

  Paul.

  
  
  

• BrianCogs887

  VFP6 doesn't support without a field delimiter option. You might workaround removing the delimiters after copy to.

  -Copy to with a delimiter that doesn't exist in data normally (ie: a tilde)

  -After copy to remove all existence of that char from output file.

  
  lcOutput = "myText.txt"
  use customer
  copy to (m.lcOutput) type delimited with "~"
  strtofile(chrtran(FileToStr(m.lcOutput),"~",""), m.lcOutput)

  
  

• VarunK

  Hi Paul

  >> I don't see the double quotes as being inconsistent data. An address (record) may be:

  Inconsistent is not the same as "invalid"

  The CSV definition is that character fields are delimited with double quotes, it doesn't matter what the contents are! So because there is no 'interpretation' of the data in the export process, unless the source data matches the output requirements you will get inconsistent results.

  The problem here is that the printer is expecting character fields to be enclosed in a pair of double quotes followed by a comma to identify the next field. i.e. "aaa","bbb"

  It is barfing when it gets what IT (i.e. the printer - not VFP or Excel!) sees as an empty string followed by something that is not a comma! That occurs because your data allows for a condition that is not consistent with the printer's expectations for the source file type.

  In other words your printer is expecting to see a file like this:

  "field","Home Sweet Home","next"

  instead it gets:

  "field",""Home Sweet Home"","next"

  Notice that there is no comma between what IT sees as an empty field ("") and the word "Home" - that is the inconstency! As I said, "inconsistent" is not the same as "invalid" - your data is, as you say, perfectly valid - but is still inconsistent with the use to which you are trying to put it.

  
  
  

• Island1

  I will have a look on universalthread.com as the files I send to the inkjet printer are sometimes several megabytes in size. Mailings may be as large as 100,000 addresses or more.

  I don't think there will be any tilde characters in the data I process but I will search first to check. If the tilde was used I could probably find another character to use instead.

  Thanks again,

  Paul.

  
  
  

• Terence Curd

  Thanks Andy, I can use that code and have learnt something.

  I don't see the double quotes as being inconsistent data. An address (record) may be:

  "Home Sweet Home"

  123 My Street

  My Town

  My County

  The double quotes are legitimate to indicate a house name. They are characters in the original data just like any other character. I receive address data in Excel and import it into VFP for various processing that I need to do. Both Excel and VFP correctly interpret the address. If I output from VFP to a csv, I can mailmerge ok using MS Word but our inkjet software cannot cope with the double double quotes. That's why I wanted to output to a comma delimited text file where the only double quotes would be those supplied in the original data. I had almost achieved this but ended up with additional blank fields which seemed to be appearing where delimiters would have been.

  Anyhow, I can do what I need to do with the code you have supplied.

  Thanks again,

  Paul.

  
  
  
• Additional fields in table exported to txt