Remove leading space in TAB file

[keepingbusy]

Hi

How can I remove the space at the front of the numbers shown below?

mdateplay=DATE()
COPY TO completed.txt FIELDS listingid, daterecevd ;
  FOR mdateplay=dateposted ;
  TYPE DELIMITED WITH " " WITH CHARACTER BLANK
copy file completed.txt to completed.tab
dele file completed.txt

The above produces:
12345678 15/06/2007
23456789 15/06/2007
45678901 16/06/2007

Where as I require:
12345678 15/06/2007
23456789 15/06/2007
45678901 16/06/2007

I know the above produces the data required without "" wrap arounds. How could I also create a CSV file using the same fields above?

VFP - Version 6

Thank you

Lee

 

[Mike Gagnon]

Use filetostr() on your completed.tab and remove the first tab and put it back with strtofile()


Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.
ReFox XI (www.mcrgsoftware.com)

 

[keepingbusy]

Use filetostr() on your completed.tab and remove the first tab and put it back with strtofile()

Sorry Mike, I've heard of the commands but not sure how to implement them.

Can you give me a pointer not the sollution?

Lee


Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Olaf Doschke]

Why first create a txt file, then copy to tab and delete txt???

If I run this code, I don't get spaces in front of the first numbers:

Create Cursor curTest (listingid I, daterecevd D)
Insert into curTest values(1,Date())
Insert into curTest values(10232,Date()-100)

COPY TO c:\my\vfp\tests\completed.tab FIELDS listingid, daterecevd ;
  TYPE DELIMITED WITH " " WITH CHARACTER BLANK

What type is your listingid field?

To create a CSV file you simply do

COPY TO completed.csv FIELDS listingid, daterecevd TYPE CSV

If VFP6 supports that.

Which results in the first line being comma seperated field names, the followin lines comma seperated values.

Bye, Olaf.

 

[Mike Lewis]

Lee,

This is off the top of my head, and not tested:

lcText = FILETOSTR("Completed.Txt")
ALINES(laText, lcText)
FOR lnI = 1 TO ALEN(laText)
  lcTrimmed = LTRIM(laText(lnI))
  STRTOFILE(lcTrimmed +CHR(13) + CHR(10), "Trimmed.Txt", 1)
ENDFOR

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[dgrewe]

Could try (not tested)

CREATE TABLE Tempdbf FREE nInput C(10), dInput c(10) && (Change sizes to match your data sizes)
USE TempDBF EXCLU
APPEND FROM (Completed.txt) TYPE SDF
REPLACE ALL nInput WITH ALLTRIM(INPUT), dInput WITH ALLTRIM(dInput)
COPY TO COMPLETED.SDF TYPE SDF
COPY TO COMPLETED.CVS TYPE CVS

David W. Grewe Dave

 

[Imaginecorp]

Hello Lee:
Like Olaf, when I run your code, I do not get any leading spaces as the Copy to command converts numeric & dates to Character.
But I did a straight copy to a .tab file without the middle step of a .txt file.
Unfortunately my CSV opens in Excel, so its difficult to determine if there are leading spaces in it.
Though: If "listingid" is not always the same number of digits, i.e. 123456, 7890, etc. there will always be leading spaces in some numbers.

 

[Mike Lewis]

Imaginecorp,

Unfortunately my CSV opens in Excel, so its difficult to determine if there are leading spaces in it.

But if you right-click on it and select Open With, you can open it in Notepad.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Imaginecorp]

Duh... I am getting old and senile, Should have remembered that...Thanks Mike.

 

[Stella740pl]

Mike,

But if you right-click on it and select Open With, you can open it in Notepad

You could also open it with MODIFY FILE from VFP, especially if the file is big. FoxPro opens text files much faster than Notepad.

 

[keepingbusy]

Sorry for the delay in replying.

I had to make some changes to part of the code to add a letter Y in a field which was required for a customer order completed confirmation process via a phpmyadmin upload. I managed to resolve the issue using suggestions from this thread for which I'm grateful:

mdateplay=DATE()
SELECT listingid, sent FROM MYTABLE where ;
  DATEPOSTED=mdateplay INTO TABLE ANOTHERTABLE.DBF
REPLACE ALL SENT WITH "Y"
COPY TO completed.tab FIELDS listingid, sent ;
  TYPE DELIMITED WITH " " WITH CHARACTER BLANK
copy file completed.tab to completed.txt
copy file completed.tab to completed.csv

This gave the user an option to use a txt or csv file.

My sincere thanks to all

Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Olaf Doschke]

Hmm, Copy File just copies the file as is, Dave used two COPY TO commands with different TYPEs, which each have a different result. Are you sure that solves your problem, just renaming the file?

Bye, Olaf.

 

[Imaginecorp]

Hello Lee:

Glad you got it working... A suggestion if I may, rather than create a table, why not use a cursor? When you are creating a table, especially a large one, you may run into disk space or user rights issues.

the following example does exactly what you are trying to do without the variable or the Global replace.

SELECT listingid, "Y" as sent FROM MYTABLE where ;
  DATEPOSTED=DATE() INTO cursor ANOTHERTABLE

 

[keepingbusy]

OlafDoschke

Are you sure that solves your problem, just renaming the file?

Yes. It gave the user the option to either upload a txt or csv file

Imaginecorp

Good point. As the user is dealing with a few hundred orders a day, this could be an issue although a table with just a reference number and the letter "Y" doesn't take up much space. The table is deleted (later on in the code) to make way for a new one each time the process is run. I will adopt your suggestion but would be interested if this would take up memory space?

Thanks both

Lee


Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Imaginecorp]

Hello Lee:

True, the table is deleted and will hardly take up any space at all, Also these days Disk space is cheap and not an issue, My point was more to do with user rights i.e. rights to create a table... Administrators are Not normal people {g}

Creating a cursor, like the table, with only a reference number and the letter "Y" will have minimal impact on memory.

 

[keepingbusy]

Imaginecorp

Many thanks for the reply

Lee

Administrators are Not normal people

Your quote, not mine!

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk