Replace " with nothing in textfile via Foxpro

Question

I've created a program in FoxPro that takes a CSV file with limited data and assigns values and other things and then exports it to a comma delmited text file. The problem I've run into is that there are 2 dates in a row in this file, each of which will change each time this program is run.

Here's a small sample of the text file as is: 5137851,"CU","0",5/25/2013 0:00:00,"5/27/2013 0:00:00,"","","",1,41,30,3,41,32,4

I need to remove the quotation mark before the "5" in "5/27/2013 00:00:00

Right now I have this code in place to remove the quotation marks in certain areas.

strRecord = STRTRAN(strRecord, '0:00:00"," "', '0:00:00",') 
strRecord = STRTRAN(strRecord, '0:00:00"," "', '0:00:00",')
strRecord = STRTRAN(strRecord, '0:00:00",', '0:00:00,')
strRecord = STRTRAN(strRecord, '"CU","0","', '"CU","0",')
strRecord = STRTRAN(strRecord, '0:00:00,"5','0:00:00,5')

The issue arises with that last line because the number is not always going to be a 5, it will be whichever month that the file comes in as. I can't do this either:

strRecord = STRTRAN(strRecord, '0:00:00,"', '0:00:00,')

because it will get rid of the first quotation mark in the three consecutive empty fields.

I can't think of any other way to edit this file to remove that quotation mark prior to the 5 in the second datetime field.

Thanks for your help in advance!

score 0 · Answer 1 · answered May 20 '13 at 20:49

If you know that the field you need to change is also in a certain position in the list, use code that affects only that position. From your code sample, it looks like you already have the single row in a variable. So, you have a few choices:

1) use ALINES() to break it into pieces and then go directly to the item of interest. Then, loop through the array to put the row back together; 2) use OCCURS() to find the nth comma and then operate on what follows.

Since this is VFP, there are a ton of other ways you can do this, but I'd go with one of these two.

score 0 · Answer 2 · answered May 21 '13 at 02:36

My approach is different from alines() processing. It looks like your file is delimited with commas between the fields, and unsure of consistency of where/why " are (not) being applied.

This sample to simulate creating the text file your record(s) are coming from

 TEXT TO lcVar noshow
 5137851,"CU","0",5/25/2013 0:00:00,"5/27/2013 0:00:00,"","","",1,41,30,3,41,32,4
 ENDTEXT
 STRTOFILE( CHRTRAN( lcVar, '"', ''), "myText.txt" )

Now, read the original file you have, use CHRTRAN() and strip out all " characters from it. Then immediately write it back out once to new file -- in this case "myTextNoQuotes.txt" file.

STRTOFILE( CHRTRAN( FILETOSTR( "myText.txt" ), '"', ''), "myTextNoQuotes.txt" )

Next, create a cursor of the columns you expect from the data context. Since I don't know the expected actual column names or confirmed types, I've just sampled with all fields being character of a few spaces.

CREATE CURSOR C_TmpImport;
    (   SomeID      c(10),;
        CUField     c(10),;
        Field3      c(5),;
        SomeDate1   c(20),;
        SomeDate2   c(20),;
        Field6      c(5),;
        Field7      c(5),;
        Field8      c(5),;
        Field9      c(5),;
        Field10     c(5),;
        Field11     c(5),;
        Field12     c(5),;
        Field13     c(5),;
        Field14     c(5),;
        Field15     c(5) )

Now, append into the cursor from the "NoQuotes" version text file and exeplicitly let it know the delimiter is the comma.

APPEND FROM myTextNoQuotes.txt DELIMITED WITH ","

Now, if you obviously know that fields 9-15 are numeric, just change the CREATE CURSOR and give more appropriate names. Once in a table format, you can do other cleanup...

Sometimes when importing, and I know I need to do cleanup, I'll actually add EXTRA columns of final data types and put the "cleaned" version of the data with bulk replace/update commands... something like...

            ...original part of create table..
 Field14      c(5),;
 Field15      c(5),;
 CleanDate1   t,;
 CleanDate2   t )

"t" is data type for "Date/Time" field. Then...

REPLACE ALL 
   CleanDate1 WITH CTOT( SomeDate1 ),;
   CleanDate2 WITH CTOT( SomeDate2 )

score 0 · Answer 3 · answered May 19 '14 at 17:32

If your strings are going to be fairly consistent structurally, you can run an update for that one based on its position in the string.

strRecord = Strtran(strRecord,',"',',',3,1)

Strtran has additional parameters to define what occurrence of the string to replace as well as how many of them.

If you ran the line above before your other replaces, it will work.

Replace " with nothing in textfile via Foxpro

3 Answers3