removing commas from textfile before importing into dbf 1

[dougerstew]

I have received the "basic" program to remove commas from a text file before importing into foxpro and it has worked well until now.

If all you want to do is replace ALL commas, this will get you started:

STORE 0 TO nInHandle, nOutHandle
STORE '' TO cTmpStr
STORE 'InFile.TXT' TO cInFile

nInHandle = FOPEN(cInFile)
nOutHandle = FCREATE('OutFile.TXT')

IF nInHandle < 0 .OR. nOutHandle < 0
?'Error'
RETURN
ENDIF

DO WHILE !FEOF(nInHandle)
cTmpStr = FGETS(nInHandle)
cTmpStr = ChrTran(cTmpStr , ',', '')
=FPUTS(nOutHandle, cTmpStr )
ENDDO
=FCLOSE(nInHandle)
=FCLOSE(nOutHandle)
ERASE (cInFile)
RENAME outfile.txt TO (cInFile)

Can someone tell me how to tweak this to only eliminate commas in numbers and add back in space before the numbers?

Thanks!

Doug Stewart

 

[DSummZZZ]

Well, the bad thing is there is no quick and easy solution, but of course, with Fox it can be done. However, have you thought about using a different delimiter than a comma in the export?
If that's an option, it would be worth considering. You wouldn't have to do any twiddling then.
If it's not an option, you may be able to create separate columns for the numeric values on different sides of the commas and concatonate them later. Otherwise, you'll just have to parse through the data, keep track of whether you are dealing with a character or numeric, (or date or whatever), and substitute appropriately.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[JRB-Bldr]

If you have just straight text without delimiters you might try somethings like this simplistic data parsing approach:

* --- From above post ---
DO WHILE !FEOF(nInHandle)
cTmpStr = FGETS(nInHandle)
=killcomma()
=FPUTS(nOutHandle, cTmpStr )
ENDDO
<whatever else>


FUNCTION killcomma
mnStrLen = LEN(mcTmpStr)
FOR i = 1 TO mnStrLen - 2
mcTstChr1 = SUBSTR(mcTmpStr,i,1)
mcTstChr2 = SUBSTR(mcTmpStr,i+1,1)
mcTstChr3 = SUBSTR(mcTmpStr,i+2,1)

IF mcTstChr2 = &quot;,&quot;
IF BETWEEN(ASC(mcTstChr1),48,57);
AND BETWEEN(ASC(mcTstChr3),48,57)
* -- Comma Preceeded & Followed By Numerics --
* --- Eliminate Comma Between Numerics ---
mcTmpStr = STUFF(mcTmpStr,i+1,1,&quot;&quot

* --- Reset Pointer & String Length ---
mnStrLen = mnStrLen - 1
i = i - 1
ENDIF
ENDIF
ENDFOR
RETURN .T.

However this simplistic routine will not check for numerics preceeding from previous GETS() &quot;record&quot;. But you can modify it to do so pretty easily.

As for &quot;add back in space before the numbers&quot;...
I am not sure what you are after. If the numeric is already preceeded by a space, I would think that nothing more needs to be done.

If your data is originally in columns (as Dave suggests above) and you wish to retain its visual appearance, then you need to add some similar code to check for a numeric preceeded by a space (indicating the Start of a number) and then use the STUFF() command again to insert a space at that position after each comma in the number is removed.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[dougerstew]

JRB-Bldr,

When removing the commas the date will change like this:

12,345.25 5,000.00 changes to
12345.25 5000.00

All data to the right moves over a space. Ideally, I would like it to change like this:

12,345.25 5,000.00
12345.25 5000.00

Thanks for your input,

Doug

 

[JRB-Bldr]

Have you made an attempt to follow the suggestion above:

If your data is originally in columns (as Dave suggests above) and you wish to retain its visual appearance, then you need to add some similar code to check for a numeric preceeded by a space (indicating the Start of a number) and then use the STUFF() command again to insert a space at that position after each comma in the number is removed.

It would be a good learning opportunity for you.

Good Luck,

JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[AnanthaP]

If your data is originally in columns, then the clause. This doesn't care about commas.


... TYPE SDF ...
is all you need as in.

Select a
Append from FILEA.TXT TYPE SDF.