What is the best way to suppress a single space character within a given field? 1

[montypython1]

Greetings,

What is the best way to suppress a single space character within a given field (if it is the ONLY character within that field)?

I am exporting a file into CSV format, but can't seem to figure out how to suppress the single space character within the "MidName" field if there is NO middle name. Most of the records contain a value in this field, but the few that do not are causing an error when uploading this file to another system. I've tried using the ALLTRIM function, but it still inserts a single space in the "MidName" field. My code is pretty simple (see below):

************************************
SELECT SSN, FirstName, ALLTRIM(MidName) AS MiddleName, LastName, Wages_Qtr1, PITWG_Qtr1, TaxWH_Qtr1, WagePlan ;
FROM csrEmplData_Output2 ;
INTO CURSOR csrEmplData_Output3 NOFILTER
COPY TO "C:\Test\blankmiddleinit" TYPE CSV
************************************

I certainly appreciate any suggestions.

Thanks,
Dave Higgins

 

[Mike Lewis]

Dave,

Are you saying that you've got a field named Middlename, and that field might contain an embedded space? And, if it does, you want to get rid of that space?

If that's right, surely all you need is:

SELECT csrEmplData_Output3
REPLACE ALL Middlename WITH STRTRAN(Middlename, " ", "")

Or have I misunderstood the question?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[montypython1]

Hi Mike,

Thanks for the suggestion, but unfortunately the REPLACE ALL did not remove that space character. I tested the logic on the FirstName field and it worked as expected, since it correctly replaced the first name "PAUL" with "ZZZZ", but it doesn't seem to want to replace just a single space in the MiddleName.

---> "PAUL"," ","SMITH"
---> "ZZZZ"," ","SMITH"

Any other suggestions?

Thanks,
Dave

 

[JRB-Bldr]

Could it possibly be that the 'single character' in the MiddleName place is not a single SPACE, but instead something else like maybe a TAB (or some other Non-Space character(s))?

Have you examined the MiddleName Field Values at the ASCII level?

OR if the value happens to be Multiple Spaces you might have to execute the STRTRAN() command to remove the single space multiple times to remove ALL spaces.

Good Luck,
JRB-Bldr

 

[Olaf Doschke]

What's the field type? Is it a nullable field? You can't have a C(1) field with an empty value, you can only store an empty string length 0 in a VARCHAR field, but not in a char field. A char alwas is containing SPACES for the unused space, and therefore also a CSV results in a single space output, of course.

What's really the problem of the destination system? Is it that space or is it the quotation marks? CSV is not just one well defined format, some systems need quotation marks, others won't work with them, VFP itself has problems with CRLF within a field, but that's not the problem here.

Bye, Olaf.

 

[Mike Lewis]

Dave,

I misunderstood your question. I thought you wanted to remove a space embedded in the middle name field. What I now think you want is this: If the middle name contains a single space and nothing else, you want the output field to be empty, otherwise you want to leave it alone. Is that right?

If so, there is a problem with your SELECT. In particular:

SELECT ... ALLTRIM(MidName) AS MiddleName .....

In the resulting cursor, the width of the MiddleName field will be the width of the trimmed middle name in the first input record. Any middle names that are longer than the first one will be truncated. To fix that, wrap the ALLTRIM(Middlename) in a PADR(), in which the second param is the desired field width.

That doesn't directly solve your problem. However, I have just done a test to reproduce the problem, and it works for me as expected. If the middle name field is empty (either a single space, a NULL, multiple spaces, or just no data present), the output field is always empty (just a pair of consecutive double-quotes). If that is not what you are seeing, I must still have misunderstood the problem.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[montypython1]

Hi Mike, Jrbbldr,and Olaf,

Thank you all for your suggestions. The field in question is a character type field with a width of only 1 character. In looking at the data, there does not appear to be any strange characters in place of a blank space. Mike, I tried wrapping the code in the PADR() function, but it did not seem to work. My code is below:

SELECT SSN, FirstName, PADR(ALLTRIM(MidName),0) AS MiddleNameAlltPADR, LastName, Wages_Qtr1, PITWG_Qtr1, TaxWH_Qtr1, WagePlan ;
FROM csrEmplData_Output2 ;
INTO CURSOR csrEmplData_Output3 NOFILTER READWRITE

I'm pretty tired now so I'll probably have to look at this tomorrow morning (it is 1:20am here), but wanted to thank everyone for their input.
Any additional ideas are certainly appreciated.

Thanks,
Dave

 

[Mike Lewis]

Good morning, Dave. I hope you slept well.

Regarding PADR(ALLTRIM(MidName),0), you don't want a zero as the second parameter. The parameter should be whatever you want the width of the middle name to be. From what you said, that suggests it should be 1.

But, as I mentioned earlier, that's not really the solution to the original problem.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

If it's a C(1) field, what I already said applies: You can't remove a space in there, a char field is a fixed length field and if you store "" into a C(1) field it get's " ", spaces automatically pad the char field. You need a V(1) field (varchar), if you want to be able to make a difference between " " and "".

You can only try if a NULLable C(1) field would make a difference, and in cas eof no middle name initial would store NULL.

Bye, Olaf.

 

[Andre Globensky]

Hi Dave, i had the same problem a while ago

Take the code under bengalliboy, and work with that...

http://www.tek-tips.com/viewthread.cfm?qid=284909

in his code, change lcString to ALLTRIM(lcString),
this will remove the extra space of all field. If you want to isolate only empty C(1) use an IIF()

IF lnCount < lnFieldCount && Determines if the last field was
&& processed and sets the closing quote.
lcFieldString = lcFieldString + '"' + lcString + '"' + ','
ELSE
lcFieldString = lcFieldString + '"' + lcString + '"'
ENDif

This should solve your problem.
On my end i inserted a routine to check the delimiter so that it's not in my file

Andre

 

[Olaf Doschke]

Before you go the route of Andre and write code to create a CSV file yourself, simply try out Varchar fields:

Create Cursor curNames (vFirstname V(30), vMiddleName V(1), vLastname V(30))
Insert Into curNames Values ("Olaf","","Doschke")
Copy to d:\temp\testexport.txt TYPE CSV

I tested and the resulting file then is:
vfirstname,vmiddlename,vlastname
"Olaf","","Doschke"

If you can't change your table file, simply use CAST to query the C(1) field into a V(1) field:

SELECT .., Cast(Middlename as V(1)) As Middlename... FROM yourtable INTO CURSOR curCSVExport
Copy to ... TYPE CSV

Bye, Olaf.

 

[Andre Globensky]

Hi Olaf,
i like your solution, but i read in one of your post a while back to stay away from varchar because it could be buggy.
Since then i always took that route. Is it ok in that case.

Andre

 

[Olaf Doschke]

From when was that post?

Varchar was buggy in the Beta and perhaps in SP0, within a grid and in export, but you see it works as expected now.
Indeed you must Cast(Alltrim(Middlename) as V(1)) As Middlename to trim away the space, but it works.

Bye, Olaf.

 

[Andre Globensky]

ok good to know,

i found the last post and like i said it was i while back

http://www.tek-tips.com/viewthread.cfm?qid=1222428

At that time i did not follow all the response or evolution of the thread.
Took that as a warning and stayed clear of it.

Andre

 

[Olaf Doschke]

That bug repro Code still exists in VFP9 SP2, so the bug is still there, but you can still create a V(1) field for the export cursor, the bug is a browse/grid behaviour bug, not a field type bug. That's also what was said way back then. It's a browse/grid bug.

The downside of the varchar field is, it does not really save space. In a DBF file you still have a fixed RECSIZE for all records, V fields are padded with CHR(0) instead of SPACE and that's about the difference. varchar fields are trimmed automatically, including CSV export. That's what it's good for.

Eg you can also SELECT Cast(Len(varcahrfield) as I) as iLenVarchar and get the length of the strings put into the varchar fields. If you do that with a char field you get a constant value, the field width.

Bye, Olaf.

 

[Mike Lewis]

The downside of the varchar field is, it does not really save space. In a DBF file you still have a fixed RECSIZE for all records, V fields are padded with CHR(0) instead of SPACE and that's about the difference. varchar fields are trimmed automatically, including CSV export. That's what it's good for.

Just for the sake of completion, it's worth adding the varchars are also good in remote views, for mapping to varchars on the back end. If the view contains char fields, these get padded with spaces when you send an insert or an update to the back end (which defeats the object). That doesn't happen with varchars.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[montypython1]

Thank you Mike, Olaf, AGlobensky,

Success! Thanks so much for all your input ... it works perfectly!
For some reason, I also had to wrap the CAST function with the ALLTRIM function before it purged the space (see below):
SELECT SSN, FirstName, ALLTRIM(CAST(MidName as V(1))) AS MidNameAlltrimCastVarChar , LastName ;
FROM csrEmployee1 ;
INTO CURSOR csrEmployee2 NOFILTER READWRITE

This leads me to wonder if the original space in the database was something other than a space.
How can I see the ASCII representation of that particular field?

Again, Thank you ALL!
Dave Higgins

 

[Mike Lewis]

Glad you've got it working, Dave.

How can I see the ASCII representation of that particular field?

Get yourself a decent hex editor (or hex viewer). I use Maël Hörz's HxD, but there are many others available for free download. Then open the DBF in the editor. Look for the character in the middle name field (you'll probably recognise it because it's immediately before the last name). If the decimal code is 32 (or 20 hex), then it's a normal space If it's anything else, report back.

Mike




__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

I already added I forgot the alltrim, indeed it doesn't matter much, if you put it inside or outside of the cast, but I would prefer to cast the output of Alltrim to Varchar, so CAST(Alltrim(Middlename) as V(1)) As Middlename, that's more in the sense of SQLs CAST functionality: CAST(expression as type).

Bye, Olaf.

 

[Olaf Doschke]

>This leads me to wonder if the original space in the database was something other than a space.
Why? Alltrim worked all the way, you still didn't understand the char field pads whatever value you put in there with spaces.

So C(1) Space = chr(32) = " " inside the field is trimmed to "" by ALLtrim(field), that is stored into a result cursor, which inherits the field type C(1), which stores "" and pads it to fixed length C(1) with what? With a space!

Do you get the mechanism now? It's not Alltrim failing, it's the nature of the char field, that is not allowing you to store a trimmed string.

If you define a table with a char(10) field and store "Dave" into it, the field also will contain the Value "Dave ", not "Dave". That's just the nature of the char field. Fixed length means fixed length, no longer AND no shorter. Varchar means variable length char. Therefoire a varchar field is more like a stirng variable, storing exactly what you put into it (unless you would want to store something longer than the field size), while a char field always pads what you put into it. And this is not even foxpro specific. Also MySQL or MS SQL Server char fields are fixed size in the length you define them.

See now?

Bye, Olaf.