Pipe "|" Delimited file import is giving an unexpected result

[montypython1]

Greetings,

I'm trying to import a file that is Pipe "|" delimited. This should be straight-forward, but I am getting a result that doesn't make sense to me.


My source data file looks like this:
02|BLDEALER|020000360|02|0000360|C|
02|BLDEALER|020000362|02|0000362|C|
02|BLDEALER|020000365|02|0000365|P|
02|BLDEALER|020000350|02|0000350|C|
02|BLDEALER|020000366|02|0000366|C|


My output (after importing) looks like this (with only the first field containing any data):
02|BL
02|BL
02|BL
02|BL
02|BL


My Code looks like this:
*==========================================================*
LOCAL lcFiles_Data, lcFileName_a, lcFileName_b
lcFiles_Data = "C:\ZZZ\Test"
lcFileName_a = "test.txt"
lcFileName_b = "test_b.csv"
lcFileName_c = "test_c.csv"
*

CREATE CURSOR automate_a ;
( ;
f01StoreNum c(05) , ;
f02Dealership c(10) , ;
f03DealNumLong c(9) , ;
f04Future01 c(2) , ;
f05DealNum c(7) , ;
f06Stat c(2) ;
)

IF FILE(lcFiles_Data + "\" + lcFileName_a) THEN
APPEND FROM lcFiles_Data + "\" + lcFileName_a DELIMITED WITH "|"
ELSE
WAIT WINDOW "missing file"
ENDIF
****************************************************************

1
Browse Normal NoCaption
COPY TO lcFiles_Data + "\" + lcFileName_c TYPE CSV

RETURN
*==========================================================*


It seems like it is ignoring the pipe character, even though I am using the format recommended in VFP help.

Interestingly, I can kinda sorta get close if I convert the file to CSV by replacing the pipe symbol using FILETOSTR, STRTRAN, and STRTOFILE, but this approach misses the leading and ending set of quotes (besides, I need to know how to import pipe delimited files anyway).

Does anyone have any suggestions of what I might be missing?
Thank you in advance.

Dave

 

[Mike Lewis]

Dave,

You're missing a vital keyword:

  APPEND FROM lcFiles_Data + "\" + lcFileName_a DELIMITED WITH [b]CHARACTER[/b] "|"

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

DELIMTED WITH "|" means, that fields are still expected to be delimited with comma, but strings are delimited with pipe instead of quotes.

So all the row was taken in as the first column only, and as that is defined as C(5) you only get the first 5 chars of each row including the pipe symbol.

Bye, Olaf.

 

[TamarGranor]

This is one place where the VFP docs are quite confusing, and many developers also use ambiguous terminology. The data you show isn't pipe-delimited, but pipe-separated.

DELIMITED WITH CHARACTER always indicates the _separator_. DELIMITED WITH sometimes indicates the delimiter and sometimes the separator.

Tamar

 

[montypython1]

Hi Mike, Olaf and Tamar,

Thanks for putting me on the right path. Your suggestions worked like a charm (which is no surprise).

I had read about the option DELIMITED WITH CHARACTER, but thought it a substitute to surrounding a text field with something other than quotation marks ... obviously, I was wrong.

A lesson learned.

Thanks,
Dave

 

[Mike Lewis]

Dave,

You're welcome.

It's very easy to get it wrong. As you've found, the terminology is confusing, and the Help page doesn't do a good job of clarifying it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Well,

monty, we have something in common, because I learned this from Tamar, too.

Bye, Olaf.