Remove leading space - Strange behavour

[keepingbusy]

Hi

The following command produces a txt file with no "" containing data from two fields in a table:

COPY TO myfile DELIMITED WITH " " WITH tab

However when the file is opened in Excel or notepad, there is a space in front of the data contained in the second field.

This is not the case when you view the fields in the table BEFORE the txt file is created.

I have tried combinations:

COPY TO myfile DELIMITED WITH "" WITH tab

Error

COPY TO myfile TYPE DELIMITED

Surrounds field data with ""

I'm sure this is something easy, but cant find the answer.

Thank you guys

Lee

 

[Mike Lewis]

Lee,

What do you want to end up with? A file in which the fields are separated by commas and nothing else?

If so, this should do it:

COPY TO myfile TYPE DELIMITED WITH ","

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[keepingbusy]

Hi Mike

It should end up with a line containing a TAB separator and no , " or other characters.

My initial command works fine but has that leading space in front of the fields which is causing a problem.

It should like:

34567899(TAB here)Y
21245567(TAB here)Y
43234578(TAB here)Y

At the moment it looks like:

(SPACE)34567899(TAB here)(SPACE)Y
(SPACE)21245567(TAB here)(SPACE)Y
(SPACE)43234578(TAB here)(SPACE)Y

It is adding a space in front of the first set of numbers and also in front of the Y.

It's difficult to show it here so I hope that makes sense.

Lee

 

[keepingbusy]

Forgot to mention, I'm using version 6 in case it makes a difference.

Windows Vista
Visual FoxPro Versions 6 & 9

http://www.customsoftware.co.uk

 

[Olaf Doschke]

Copy To "filename.txt" DELIMITED WITH CHARACTER TAB WITH ""

That should work. You specify both field separator (here TAB) and string delimiter (here no char). Maybe it's VFP6 that doesn't support this.

Bye, Olaf.

 

[Mike Lewis]

Lee,

I was just about to suggest this:

COPY TO myfile TYPE DELIMITED WITH CHARACTER TAB WITH ""

but I see that Olaf has beaten me to it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[keepingbusy]

Hi Mike and Olaf

Tried both your suggestions but an error occurs:

Command contains unrecognized phrase/keyword

Copy To myfile DELIMITED WITH CHARACTER TAB WITH ""

and

COPY TO myfile TYPE DELIMITED WITH CHARACTER TAB WITH ""

Any other suggestions guys?

Many thanks


Lee

 

[Mike Lewis]

Hmmm. It works for me. I don't have VFP 6.0, but it works in 7.0.

I'm not aware of any changes to this command since VFP 6.0. Anyone else know why Lee is getting an error?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Olaf Doschke]

Lee, could you please take a look at the VFP6 help and see if COPY TO supports all the clauses, eg CHARACTER, DELIMITED? What works for me too and is even a bit better understandable is

Copy To "myfile.txt" DELIMITED WITH "" WITH CHARACTER TAB

But then foxpro was always very forgiving about the order of (optional) clauses.

I'd say it simply is not working in VFP6, if it's not working. Then you'd need to go the harder way and create the rows with fputs or fwrite the way you want them formatted.

Bye, Olaf.

 

[keepingbusy]

Olaf

Ok, I tried this out in V9 and it works so I'm guessing it must be to do with V6. I'll take a look at the alternative commands you mentioned.

Many thanks

Lee

 

[Olaf Doschke]

FCREATE, FPUTS/FWRITE, FCLOSE

FPUTS is for PUTting Strings, what it also does automatically is adding a line feed after each string. So it's quite like printing to a file. You may know / and // as ? and ?? alternatives to output to files, that would also be a valid approach. In any case you'd be preparing the string to output for each record yourself. Should ba as easy as using the universal fieldtyp to string conversion TRANSFORM(field) on FIELD(1) TO FIELD(FCOUNT()) and putting a TAB in between.

Bye, Olaf.

 

[Mike Lewis]

Lee,

As Olaf has indicated, it's not difficult doing it with FPUTS(), etc.

However, there is an alternative. Something like this:

SET CONSOLE OFF
SET ALTERNATE TO MyFile.Txt
SET ALTERNATE ON
SELECT TheTable
SCAN
  ? Field1 + CHR(9) + Field2 + CHR(9) + Field3
ENDSAN
SET ALTERNATE OFF
SET ALTERNATE TO
SET CONSOLE ON

I think that will give you the desired result.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Imaginecorp]

Lee: both these work for me, But in 9

COPY TO test.txt DELIMITED WITH "" WITH TAB
COPY TO test.xls DELIMITED WITH "" WITH TAB

No spaces, etc.

Have you included an extension (txt, xls etc) to "myfile"?

 

[keepingbusy]

imaginecorp

Yes, I agree, it does work in 9 but not in 6 as mentioned above. I am not adding an extension as I understand it defaults to .txt

There are a coiple of lines not shown here that later copies the file to a .tab and .csv (as requested by the user).

Mike

I wil give your suggestion a try out later and post back.

Since posting this, I have also suggested upgrading this particular app to V9 so I'll see how it goes first with Mike's code.

Thanks all, I really appreciate it.

Lee

 

[keepingbusy]

All

Tried out Mike's suggestion and this had the desired effect however when you open the file created there is a right arrow character chr(26) on the last line and after the last character.

As I mentioned earlier, the process works fine in Version 9, so we have now upgraded the app with no problems.

Thanks again guys for the posts.

Lee

 

[Mike Lewis]

Lee,

The right-arrow is an end-of-file marker (ASCII 26). It generally does no harm, and does not prevent the file being used -- although that will depend on the application, of course.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[keepingbusy]

Thank you Mike. Unfortunately, the chr(26) woud have an impact on this particular app as the completed .txt file is uploaded to a web server and it doesn't like it!

However, as mentioned, the app has been converted to Version 9.

Thank sgain

Lee


Windows Vista
Visual FoxPro Versions 6 & 9

http://www.customsoftware.co.uk