Append Txt File Delimited By |'s

[cghoga]

Hello.

I was wondering if someone could help me.

I am trying to append a txt file to an existing table, but cannot get the data to append correctly. My client uses the | character (above the Enter key) as the data delimiter. I have tried and tried to get this to work.

Here's the code I'm using:

USE TEST EXCL
ZAP
APPEND FROM ( mFILENAME ) DELI WITH '|'

Where mFilename is a variable containing a path I'm feeding it.

Has anyone else had any experience working with files delimited by this character?

Any help is welcomed.

Thanks.

 

[ormsk]

Hi

Not sure of the character you are using, but there may be a work around...

In the text file, why not replace all the instances of the character you mention, to a more recognisable character, such as a comma and then import?





There is more than one way to skin a cat...but who wants a skinned cat?

 

[cghoga]

That is a good idea, but I've already tried that. The size of the data file is so large.

The character I am referring to is the "pipe" character. It is above the Enter key and below the Backspace key.

Thanks for your help.

 

[ormsk]

Hi

I created a dummy text file delimited with pipes, but all I got was the first field being appended.

I take your point about the size of the file, but the only other way I can think of looking into, is to look at modifying the file through low level programming within dbase and replace the character that way, then import it.

At least it would just be a single program that could complete these two operations.

There is more than one way to skin a cat...but who wants a skinned cat?

 

[BillPSU]

I can think of (3) ways to get your file imported.

Open the file in WordPad and do a replace the "|" with a comma. Depending on the file size.

Create a new database and import into a single field and parse the field into individual fields. If the data is longer than 255 characters create enough 255 wide fields to accept all the data and import as a fixed length text file. Again you parse and then put any leftover data into a joined field.

Write a C++ program to replace the "|".

 

[repete]

Hi

i use foxpro, and this works ok there. change your line of code

APPEND FROM ( mFILENAME ) DELI WITH '|'

to

APPEND FROM ( mFILENAME ) DELI WITH |

(ie leave out the quotes around the delimiter)

and see how you go



Pete Bloomfield
Down Under

 

[cghoga]

Thanks everyone for your feedback.

Repete,
I tried what you suggested and it still did not work with DBASE.

BillPSU,
I tried your 2nd suggestion and there was so much data it took forever to run.

The workaround I am using is to write a VBA script in Access that imports the data and then exports a dbf that in turn DBASE picks up and.....

Thanks again for all the suggestions.

 

[1oldfoxman]

According to the dBase documentation, the DELIMITED WITH <delimiter> clause refers to the delimiter with which character data will be surrounded and assumes all fields separated by commas. I.e. the pipe will be expected to be seen surrounding character data only, not used as the actual field separator.

A little different I think than you were expecting.

dennis

 

[dbMark]

Are all lines of data the same length or do you know the length of the longest line? Is it 254 characters or less? Then just add a character field as the first field in the table of sufficient length to hold the longest line. If that's lot long enough, then add as many as necessary. Then append...

APPEND FROM ( mFILENAME ) TYPE SDF

Now you have all the data in table fields. Simply search and replace all the 'pipe' delimiters for commas. Need the nice FoxPro function STRTRAN() emulated in dBase? Get it from faq290-3518. Remember that this emulated STRTRAN() cannot handle more than 254 characters. Then copy the modified records back to a file...

COPY TO ( mFILENAME2 ) TYPE SDF

Now you should have your original file but with the standard comma delimiters. Warning, I haven't tested this so don't overwrite your original file without being sure you have it doubly backed up.

dbMark