Convert number fields to text in Fox Pro 9

[Vatchunter]

Been battling an issue with an Excel 2010 VLOOKUP, decided to create the 2 tables in Fox Pro to see if I had any better luck... and I didn't.

Saved Excel files as .CSV and imported them into Fox Pro 9.

I have added a field, call it ABC, in each table and populated the ABC field in both tables with these commands.

REPLACE ALL ABC WITH ALLTRIM(V) ALLTRIM(W)+ALLTRIM(X)+ALLTRIM(Y)+ALLTRIM(Z)
REPLACE ALL ABC WITH UPPER(ABC)
REPLACE ALL ABC WITH STRTRAN(ABC, ' ', '')
REPLACE ALL mmy WITH STRTRAN(mmy, '-', '')

Used this code to bring in the info

Select B
Index on ABC tag ABC
Select A
Set Relation to ABC into B
Replace all flag with B.Z

I have found a number of records that exist in table B that did not get a hit in table A, I think this may have something to do with the fields that make up the ABC field being number and text.

Any suggestions would be appreciated!

Thanks,

Frank

 

[DSummZZZ]

Well, you'll have to determine exactly what the differences in those particular records are and accommodate them.
If you're setting a relation, the fields have to match exactly or the records in A won't get updated.


-Dave Summers-

Even more Fox stuff at:

http://www.dave-summers.net/foxprolinks.htm

 

[Olaf Doschke]

Frank,

The values in ABC are initialized with ALLTRIM(V) ALLTRIM(W)+ALLTRIM(X)+ALLTRIM(Y)+ALLTRIM(Z), right?
At least a + is missing between ALLTRIM(V) and ALLTRIM(W)

ALLTRIM only works on String values, not on numeric values, so if V,W,X,Y or Z are numeric this would error.

You talk about CSV and how do you put this into a DBF? CSV is pure text, even if it came from numeric or other type cells. So if you APPEND a CSV file into a cursor only having char fields, you only get char/string values.

In the end you have to look what A.ABC is in the records not updated and why you don't find it's values in B.ABC. A different field length could be a reason, cut off values could be another reason.

Bye, Olaf.

 

[Dan Freeman]

The other problem is that with Alltrim() an empty field will simply be omitted. Rather than [pre]" 123"[/pre] you'll just get [pre]"123"[/pre].

You need to pay particular attention to how you build your string so that it results in a consistent length, with all the elements in a consistent position.

 

[Vatchunter]

Looks like the CLEAN function in Excel removed the invisible spaces that were causing me problem in this instance....

Sorry there were some typo's in my command in the previous post.

Yes the value of ABC is built on the command:

REPLACE ALL ABC WITH ALLTRIM(V)+ALLTRIM(W)+ALLTRIM(X)+ALLTRIM(Y)+ALLTRIM(Z)
REPLACE ALL ABC WITH UPPER(ABC)
REPLACE ALL ABC WITH STRTRAN(ABC, ' ', '')
REPLACE ALL ABC WITH STRTRAN(ABC, '-', '')

In my Excel file I have formulas to ck for the longest character count in each field and another formula to "pad" the number of characters in each field so that no data is cut off.
An IF statement to ck to make sure the "paded" number is higher then the largest number of characters in each column, once reviewed then I save the Excel file to .csv. I use the import process to bring the .csv into Fox Pro.

So it appears that invisible spaces were my issue... I thought the ALLTRIM would take care of those... Is there a Fox Pro command equivalent to the Excel CLEAN function?

Thank for all your input!

 

[Olaf Doschke]

ALLTRIM trims away Spaces and Tabs, but not chr(160), so that may have been that kind of space. You can tell ALLTRIM to include this by ALLTRIM(V,chr(160),chr(32),chr(9)) and might pass in 23 chars to trim away. ALLTRIM trims right or left, but not inbetween.

The cut off problem might still occur, because your DBF field sizes will matter, not only the source data padding. When you IMPORT or APPEND, you fist dfeine a DBF with fields, and they have a size defined by you, not by the CSV data, don't they?

And if the source values are padded to have the exact same width, then I'd not trim the data at all in ABC, but leave it as it is, why do you "CLEAN" up this data anyway? If the original value V+W+X+Y+Z is too wide without the ALLTRIMs, you could make your ABC column wider.

Also you can cause false matches, if you concatenate ALLTRIM(" 12")+ALLTRIM(" 3") or ALLTRIM(" 1")+ALLTRIM(" 23") you get the same value "123", while the original two values differ.

Bye, Olaf.

 

[Vatchunter]

Olaf,

Our data comes from many different sources. Not sure how long we have been experiencing this problem, but we just found it... one of the data sources had the invisible spaces in it. So the result was data from 2 different sources... in both tables that looked the same, but the program(s) are seeing it differently.

EX. ABC field in both tables visibly showed CHARLIEBROWN23BLUE274683, yet the return was #N/A in Excel, a blank was returned in Fox Pro. The data looked the same but was not the same because of the invisble spaces....

You are correct about Fox Pro setting the field sizes during the import of the CSV, I have found a work around for that process so that I can get my csv file into Fox Pro and set the field size I set in the "paddd" row in each column. It's not pretty but it works...

Will do some testing/research on ALLTRIM(V,chr function to see if it works.

Really appreciate all your suggestions!

Thanks,

Frank

 

[Olaf Doschke]

Well, in regard to spaces chr(160) is the only space cahracter I know besides chr(32), but it's a visible space. You might have another problem, eg chr(0) in the data, that would cut off the rest for VFP: Messagebox("Hello"+chr(0)+"World"). Many other chars I would expect to turn to ? or be some dirt, but not invisible.

You should anylize the string composition with ASC of each single char of the strings to see what is the invisible space.

Bye, Olaf.

 

[Vatchunter]

Hate to ask, but I'm hurting for time... how would I go about using ASC? Is this an ASCI format?

 

[Olaf Doschke]

ASC() is a function, which gives you the numeric value of a single character, no matter if it's a visible/printable character or not. So you'd reveal what is the invisible space in the excel data.
Something like FOR index=1 to len(string) // ? asc(substr(string,index,1)) // NEXT will give all the ASCII codes of the characters.

It even doesn't matter whether the string is ASCII or ANSI or Unicode, every string is just a concatenation of bytes and you reveal their value.

Bye, Olaf.

 

[Vatchunter]

Thanks! I will try to update you on what was found.

Again, Thanks for all your help!