Delimited files

[wlynch]

I've read as many posts as I can find to answer the question of how to produce a comma ONLY delimited file with NO quotes but I can't find how to include the header at the same time.

COPY TO c:\myfolder\mytextfile.txt DELIMITED WITH "" with CHARACTER ,

This will work but gives me a file with no header. If I add 'type csv' it will error out. I am using Foxpro 9.0

(this does not work either) COPY TO c:\myfolder\mytextfile.txt type csv DELIMITED WITH ,


I am looking for a one line solution if possible. Thanks.

 

[Olaf Doschke]

There's always the solution to add the header in a second pass, of course that means writing out the file twice.

I think there is no solution to get both header and no quotes around char type fields. But there is the solution from Microsoft to write out DBFs including Memo fields and based on that you can also skip the quotes:

http://support.microsoft.com/kb/241424/en-us

More code, but finally faster than writing the csv out twice to add headers.

Another solution might be to make use of _vfp.DataToClip(), copy data to clipboard, paste it into a new empty excel sheet beginning from row 2, add captions on row 1 and save as csv from excel.

Bye, Olaf.

 

[wlynch]

The link you gave me worked great for creation of a file with comma and no quotes. Unfortunetly it does not include the header record. I appreciate the effort.

 

[Mike Lewis]

Wlynch,

To include a header line, you need to copy to TYPE CSV. However, that doesn't give you a way of avoiding the quotes (as far as I know).

You could try something like this:

COPY TO MyFile.TXT TYPE CSV
lcString = FILETOSTR("MyFile.TXT")
lcString = STRTRAN(lcString, ["], [])
STRTOFILE(lcString, "MyFile.TX")

Keep in mind that, if one of your character or memo field contains a comma or double-quote, the copying will go wrong. But I assume that's not the case, othewise you wouldn't want to strip the double-quotes.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[JRB-Bldr]

"I am looking for a one line solution if possible"

It would be nice if there was a One Line solution, but there is not.

You can get "one from column A" OR "one from column B" but not both.

I generally use the COPY TO command to get my data into the text file as desired and then create a string for the header by using AFIELDS().
I then append the Header string to the data by going through the FILETOSTR() and STRTOFILE() commands.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

I forgot to mention that you can write your own FUNCTION to do the job and then whenever you needed to use it you would just call it and pass the parameters - it would be a one-line solution.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

Common, it's not hard to add a FWRITE line after FCREATE to print out the header line to the file:

FPUTS(lcTextFile, "field1,field2,field3,field4...")

That's all there is to it.
If it should work with any dbf, the array laGetFields created by AFIELD contains the field names, so you could do:

...
lcTextFile = FCREATE(GETFILE('txt', 'Select Text'))

lcFields = ""
FOR lnCount = 1 TO lnFieldCount
    lcFields = lcFields + "," + laGetFields(lnCount, 1)
ENDFOR
lcFields = Substr(lcFields,2)
FPUTS(lcTextFile, lcFields)
...
SCAN
...

Bye, Olaf.

 

[wlynch]

Got it!! All your suggestions and a bit of fiddling around did the trick. Still, sure would be nice if you could just copy to.... etc, etc. and get what you want. LOL

Thanks to you all.

 

[Mike Lewis]

Wlynch,

Would you care to share your solution with us? It might be of use to other folk who have the same problem.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[wlynch]

Sure, no problem. Here is what I used

CLOSE ALL
CLEAR ALL

lcFieldString = ''
lcMemo = ''

USE GETFILE('dbf', 'Select DBF') && Prompts for table to be used.

lnFieldCount = AFIELDS(laGetFields) && Builds array of fields from the
&& selected table.

*!* Prompt for Output file and use Low-Level functions
*!* to create it.
lcTextFile = FCREATE(GETFILE('txt', 'Select Text'))

*!* Starts scanning the table and converts the fields
*!* values according to their types **
SCAN
WAIT WINDOW STR(RECNO()) + ' Of ' + STR(RECCOUNT()) NOWAIT

FOR lnCount = 1 TO lnFieldCount
lcType = laGetFields(lnCount, 2)

IF lcType # 'G' && Don't try to turn a general field into a string
lcString = EVALUATE(laGetFields(lnCount, 1))
EndIf

DO CASE
CASE lcType = 'M' && Process the Memo Fields
lnMemoLines = MEMLINES(EVALUATE(laGetFields(lnCount,1)))
FOR lnLoop = 1 TO lnMemoLines
IF lnLoop < lnMemoLines
lcMemo = lcMemo + ;
ALLTRIM(MLINE(EVALUATE(laGetFields(lnCount, 1)), ;
lnLoop)) + ' '
ELSE
lcMemo = lcMemo + ;
ALLTRIM(MLINE(EVALUATE(laGetFields(lnCount, 1)), ;
lnLoop))
ENDif
ENDfor

lcString = lcMemo
lcMemo = ''
CASE lcType = 'G' && Process the General Fields
lcString = 'Gen'
CASE lcType = 'D' && Process the Date Fields
lcString = DTOC(lcString)
CASE lcType = 'T' && Process the DateTime Fields
lcString = TTOC(lcString)
CASE lcType = 'N' && Process the Numeric Fields
lcString = STR(lcString, LEN(STR(lcString)), 2)
CASE lcType = 'I' && Process the Integer Fields
lcString = STR(lcString)
CASE lcType = 'L' && Process the Logical Fields
IF lcString = .T.
lcString = 'T'
ELSE
lcString = 'F'
ENDif
ENDcase

IF lnCount < lnFieldCount && Determines if the last field was
&& processed and sets the closing quote.
lcFieldString = lcFieldString + ',' + lcString + '"' + ','
ELSE
lcFieldString = lcFieldString + ',' + lcString + '"'
ENDif
ENDfor

FPUTS(lcTextFile, lcFieldString) && Writes string to the text file.
lcFieldString = ''
ENDscan

FCLOSE(lcTextFile)

CLOSE All
CLEAR All
WAIT WINDOW 'Text File Creation Completed' NOWAIT

 

[Olaf Doschke]

Well, I mis my addition to the code to add the header line. Plus: Yes, it's a bit ridiculous that you can't control the way of CSV output, but that's not merely a foxpro problem but a problem of different implementations.

For example see here some specification:

http://supercsv.sourceforge.net/csvSpecification.html

especially

An entry may contain the separation character in which case that character is enclosed in quotes

and also

Any white-spaces at the start of a line, just after the separation character, just before a separation character, or just before a newline character is ignored.

This means not all strings need to be in quotes, but those having the sepearator (eg comma) in them should be, also having white space as start or end of a string needs quotes, so why cope with special cases and not put anything in quotes? That was perhaps the thought about the implementation of TYPE CSV in the COPY TO command.

Bye, Olaf.

 

[wlynch]

Ahh.. since I found a way to produce that the program outputting the original dbf file will add the header as the first record, it turned out all I had to worry about was producing a comma delimited file, hence I ended up not needing that small snippet you suggested. Thanks Olaf!

 

[Olaf Doschke]

Well, you can't do so if some field is numeric, date or any other non string field type.

Bye, Olaf.

 

[Mike Lewis]

Wlynch,

I have to say that the code I posted earlier is considerably simpler - as is Olaf's.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Mike, my code is just indicates a modification of the code from the Microsoft KB article I referd to in the first answer:

http://support.microsoft.com/kb/241424/en-us

His code is another modification getting rid of the quotes.

Your code is shorter, but it double writes the file, which would be acceptable with small tables only.

Bye, Olaf.

 

[wlynch]

Thanks Olaf-- you are correct, our files are many times in excess of 1MM or more records. The records need to go out to an inkjet machine in single delimiter format for some odd reason. Actually, any delimiter would work but no closing quotes can be present. I always check for imbedded commas first but may change the delimiter to another character to avoid this exta step. Again, thanks.
Will