FoxPro Help and Support - problem with "Append from..... type delimited"

Author

problem with "Append from..... type delimited"

Barley Man

2005-05-03, 7:25 am

I have a huge text file (MMBASLIB_INVMST.txt) from which I need to 'import'
(append from) records. The data seesm to be held with 'colon seperated
varaibles' but that each 'character' field is also surrounded by inverted
commas; here's a snippet it of a record:-

229272;2;"CARRIER BAGS/MISCE/777 ";"A";"CARRIERBAG";"G9994
";....... (etc.)

I am using the code as follows to append each record:-
Append From MMBASLIB_INVMST.txt Type Delimited With Character ";"

However, VFOFox 7 appears also to be using inverted commas as a delimiter AS
WELL as the ';'. This causes HAVOC as some of the text within the records use
" to denote inches and that wrecks the inetgrity of "imported" data' . How
can I 'import'/append such records forcing VFP to do what it's (damned well!)
told and treat ONLY ';' as the delimiter and ignore the (blasted) inverted
commas!?????

Stephen Ibbs

2005-05-03, 11:24 am

I think the problem is the ; delimiting character. vfp uses this
character to allow line breaks in code.

I took the first line of your snippet, created a table of six fields
(first two numeric, last four character). I then converted your text
file to look like this:

229272*2*"CARRIER BAGS/MISCE/777 "*"A"*"CARRIERBAG"*"G9994"

Note the ; character has been replaced with the * character.

I then imported using this line:

APPEND FROM c:\temp\sample.txt DELIMITED WITH " with CHARACTER *

It then imported correctly.

HTH

Stephen

**************

Barley Man wrote:
> I have a huge text file (MMBASLIB_INVMST.txt) from which I need to 'import'
> (append from) records. The data seesm to be held with 'colon seperated
> varaibles' but that each 'character' field is also surrounded by inverted
> commas; here's a snippet it of a record:-
>
> 229272;2;"CARRIER BAGS/MISCE/777 ";"A";"CARRIERBAG";"G9994
> ";....... (etc.)
>
> I am using the code as follows to append each record:-
> Append From MMBASLIB_INVMST.txt Type Delimited With Character ";"
>
> However, VFOFox 7 appears also to be using inverted commas as a delimiter AS
> WELL as the ';'. This causes HAVOC as some of the text within the records use
> " to denote inches and that wrecks the inetgrity of "imported" data' . How
> can I 'import'/append such records forcing VFP to do what it's (damned well!)
> told and treat ONLY ';' as the delimiter and ignore the (blasted) inverted
> commas!?????
>

Barley Man

2005-05-03, 11:24 am

No. Thanks for trying but it still doesn't work.

Taking your eaxmple, imagine an entry describing a 26 inch bicycle wheel
with the text of:-

'Red 26" bike wheel'

which looks as follows:

229272*2*"Red 26" bike wheel"*"A"*"CARRIERBAG"*"G9994"

That should split the fields as:-
229272
2
Red 26" bike wheel
A
CARRIER BAG
G9994

but. it actually creates:-
229272
2
Red 26
bike wheel
A
CARRIER BAG
G9994

For precision, I replaced the ';' delimiter with 'PIPE' (|) as the
delimiter '*' has it's own meaning in fox and the exact code I'm using is as
follows:-

Append From (ALLTRIM(m.rawdire)+"\MMBASLIB_INVMST.txt") DELIMITED WITH "
with CHARACTER |

ian

"Stephen Ibbs" wrote:

> I think the problem is the ; delimiting character. vfp uses this
> character to allow line breaks in code.
>
> I took the first line of your snippet, created a table of six fields
> (first two numeric, last four character). I then converted your text
> file to look like this:
>
> 229272*2*"CARRIER BAGS/MISCE/777 "*"A"*"CARRIERBAG"*"G9994"
>
> Note the ; character has been replaced with the * character.
>
> I then imported using this line:
>
> APPEND FROM c:\temp\sample.txt DELIMITED WITH " with CHARACTER *
>
> It then imported correctly.
>
> HTH
>
> Stephen
>
> **************
>
> Barley Man wrote:
>

Anders Altberg

2005-05-03, 1:24 pm

Hi Barley
The ; are not the problem. WITH CHARACTER ";" takes care of the column
separator
The best solution is for you to tell you supplier to change his file
creation so as not to use " " as delimiters.
Given the file you have:
x = FILETOSTR('source.txt' )
x = STRTRAN(x, [";],[;] )
x = STRTRAN(x, [;"].[;] )
STRTOFILE(x, 'source2.txt' )
APPEND FROM source2.txt DELIMITED WITH "" WITH CHARACTER ";"

-Anders

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:04A44D89-9927-4609-B4F6- C29AE05419F5@microso
ft.com...
> No. Thanks for trying but it still doesn't work.
>
> Taking your eaxmple, imagine an entry describing a 26 inch bicycle wheel
> with the text of:-
>
> 'Red 26" bike wheel'
>
> which looks as follows:
>
> 229272*2*"Red 26" bike wheel"*"A"*"CARRIERBAG"*"G9994"
>
> That should split the fields as:-
> 229272
> 2
> Red 26" bike wheel
> A
> CARRIER BAG
> G9994
>
> but. it actually creates:-
> 229272
> 2
> Red 26
> bike wheel
> A
> CARRIER BAG
> G9994
>
> For precision, I replaced the ';' delimiter with 'PIPE' (|) as the
> delimiter '*' has it's own meaning in fox and the exact code I'm using is
as[color=darkred]
> follows:-
>
> Append From (ALLTRIM(m.rawdire)+"\MMBASLIB_INVMST.txt") DELIMITED WITH "
> with CHARACTER |
>
> ian
>
>
> "Stephen Ibbs" wrote:
>
'import'[color=darkr
ed]
inverted[color=darkr
ed]
delimiter AS[color=darkred]
records use[color=darkred]
How[color=darkred]
well!)[color=darkred
]
inverted[color=darkr
ed]

Barley Man

2005-05-03, 8:25 pm

Well, that nearly works. The final problem is that the final field in the
record is 'character' field (;"N", to be precise) and thus the " is not
followed nor preceded by a ; and thus the STRTRAN misses it. I haven't worked
out what the final charactater is (Lf, Cr, etc..) so that I can tell it what
to STRTRAN (e.g. "+chr(13))

However, to my mind, this shows a serious flaw in the design of VFP here. I
SPECIFICALLY asked it to use ONLY the delimiter of ';' but it INSISTS on
treating "" as a delimiter as well which is not always desirable;
specifically, in this instance.

Your (and other's) help is GREATLY appreciated but the problem still a
complete 'PAIN' in the............

Ian

"Anders Altberg" wrote:

> Hi Barley
> The ; are not the problem. WITH CHARACTER ";" takes care of the column
> separator
> The best solution is for you to tell you supplier to change his file
> creation so as not to use " " as delimiters.
> Given the file you have:
> x = FILETOSTR('source.txt' )
> x = STRTRAN(x, [";],[;] )
> x = STRTRAN(x, [;"].[;] )
> STRTOFILE(x, 'source2.txt' )
> APPEND FROM source2.txt DELIMITED WITH "" WITH CHARACTER ";"
>
> -Anders
>
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:04A44D89-9927-4609-B4F6- C29AE05419F5@microso
ft.com...
> as
> 'import'
> inverted
> delimiter AS
> records use
> How
> well!)
> inverted
>
>

Alan C. Sheffield

2005-05-04, 9:24 am

As you complain about how VFP handles this, keep the following in mind. If
VFP appended the data as you expect, you'd still have a problem. You would
end up with a " at the beginning and end of each field in your data after
the append. You would end up writing the same code to strip the " characters
out.

Alan
"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:97B0B05B-21E2-4BC7-B32C- F6B26B9F574A@microso
ft.com...
> Well, that nearly works. The final problem is that the final field in the
> record is 'character' field (;"N", to be precise) and thus the " is not
> followed nor preceded by a ; and thus the STRTRAN misses it. I haven't
worked
> out what the final charactater is (Lf, Cr, etc..) so that I can tell it
what
> to STRTRAN (e.g. "+chr(13))
>
> However, to my mind, this shows a serious flaw in the design of VFP here.
I[color=darkred]
> SPECIFICALLY asked it to use ONLY the delimiter of ';' but it INSISTS on
> treating "" as a delimiter as well which is not always desirable;
> specifically, in this instance.
>
> Your (and other's) help is GREATLY appreciated but the problem still a
> complete 'PAIN' in the............
>
> Ian
>
> "Anders Altberg" wrote:
>
wheel[color=darkred]

is[color=darkred]
WITH "[color=darkred]
seperated[color=dark
red]
data' .[color=darkred]
(damned[color=darkre
d]

Dan Freeman

2005-05-04, 1:24 pm

You're mixing and matching terminology here. In your file, the delimiter is
" and the *separator* is ;. The delimiter marks beginning and ending of a
data field.

You have further confusion having your delimiter included in your data.
Garbage in, garbage out.

VFP is good, but it's no mind reader.

Yet.

Dan

Barley Man wrote:[color=darkred
]
> Well, that nearly works. The final problem is that the final field in
> the record is 'character' field (;"N", to be precise) and thus the "
> is not followed nor preceded by a ; and thus the STRTRAN misses it. I
> haven't worked out what the final charactater is (Lf, Cr, etc..) so
> that I can tell it what to STRTRAN (e.g. "+chr(13))
>
> However, to my mind, this shows a serious flaw in the design of VFP
> here. I SPECIFICALLY asked it to use ONLY the delimiter of ';' but it
> INSISTS on treating "" as a delimiter as well which is not always
> desirable; specifically, in this instance.
>
> Your (and other's) help is GREATLY appreciated but the problem still a
> complete 'PAIN' in the............
>
> Ian
>
> "Anders Altberg" wrote:
>

Anders Altberg

2005-05-04, 1:24 pm

What version of VFP are you using. I seem to remember there was a bug with
DELIMITED WITH "" (meaning no delimiters) in some version.
Don't worry about the ending ". When I tested this in VFP9 this leftover "
at the end of the line was ignored and removed by VFP in the APPEND process.
The end-of-line character is CHR(13) or CHR(13)+CHR(10), known as CRLF.

-Anders

"Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
news:97B0B05B-21E2-4BC7-B32C- F6B26B9F574A@microso
ft.com...
> Well, that nearly works. The final problem is that the final field in the
> record is 'character' field (;"N", to be precise) and thus the " is not
> followed nor preceded by a ; and thus the STRTRAN misses it. I haven't
worked
> out what the final charactater is (Lf, Cr, etc..) so that I can tell it
what
> to STRTRAN (e.g. "+chr(13))
>
> However, to my mind, this shows a serious flaw in the design of VFP here.
I[color=darkred]
> SPECIFICALLY asked it to use ONLY the delimiter of ';' but it INSISTS on
> treating "" as a delimiter as well which is not always desirable;
> specifically, in this instance.
>
> Your (and other's) help is GREATLY appreciated but the problem still a
> complete 'PAIN' in the............
>
> Ian
>
> "Anders Altberg" wrote:
>
wheel[color=darkred]

is[color=darkred]
WITH "[color=darkred]
seperated[color=dark
red]
data' .[color=darkred]
(damned[color=darkre
d]

Barley Man

2005-05-05, 7:25 am

As a very long-term (but pretty useless) user of Foxpro since the release of
FPW26, I can understand your defence of this 'old friend' but as a 'friends',
that should not prevent us fro being able to critcise it.

Be fair guys; if one tells it to use delimiter X or Y, one does not expect
it to make an executive decision to ALSO us delimiter Z as well!

On the 'results' side, I made the executive decision that I didn't care
about '26" bike wheels' so I stripped all the " marks from the text file and
the loss of that fractional amount of data was less devastating than being
unable to read any of it.

As always, I'd be completely at sea without you guy's help, it is SERIOUSLY
appreciated. I haven't a clue what I'd do without you lot! Thanks again!

Ian

"Anders Altberg" wrote:

> What version of VFP are you using. I seem to remember there was a bug with
> DELIMITED WITH "" (meaning no delimiters) in some version.
> Don't worry about the ending ". When I tested this in VFP9 this leftover "
> at the end of the line was ignored and removed by VFP in the APPEND process.
> The end-of-line character is CHR(13) or CHR(13)+CHR(10), known as CRLF.
>
> -Anders
>
> "Barley Man" < BarleyMan@discussion
s.microsoft.com> wrote in message
> news:97B0B05B-21E2-4BC7-B32C- F6B26B9F574A@microso
ft.com...
> worked
> what
> I
> wheel
> is
> WITH "
> seperated
> data' .
> (damned
>
>

Sponsored Links