Trailing Characters/Space when querying via ADODB.recordset getrows

[Jay-T]

Hi,

I'm getting a problem whereby the data that I'm querying from a mySQL based database has a trailing spaces/characters.

E.g.

Data stored

User Number 1 Number 2
---------------------------------
"Joel" , "5" , "-5"


Data retrieved

"Joel " , " 5 " , " -5 "


I've tried using trim, and it works for the numbers but it doesn't work for the string, as it's a unrecognized character hence it doesn't get trimmed as it's not a space.

Anybody knows what is going on here?

Thanks!

[millz]

1) Are you very sure the data stored does not contain that extra blank space character (for the string field)?
2) Since you posted in this Excel VBA forum, are you transferring the data into an Excel sheet?
3) If you are sure for (1), and the space always exists after extracting, and if you are putting the data into a worksheet, you can use a formula to remove trailing character using the formula: =LEFT(A1,LEN(A1)-1)

[Glenn Kennedy]

This formula will tell you what it is 32 is a regular space and 160 a non-breaking space. TRIM doesnot work with those.

=substitute(A1,CHAR(160),"")

does work...

[Jay-T]

I've realised that using varchar data type instead of text data type in the mysql DB is causing the extra spaces/characters in the user field. However as it is also a primary key that I'm querying, I can't have it as a text, it must be a char, can anyone suggest what can be done about this?

Also what can I do about the numeric fields?

Thanks!

[Jay-T]

1) Are you very sure the data stored does not contain that extra blank space character (for the string field)?
2) Since you posted in this Excel VBA forum, are you transferring the data into an Excel sheet?
3) If you are sure for (1), and the space always exists after extracting, and if you are putting the data into a worksheet, you can use a formula to remove trailing character using the formula: =LEFT(A1,LEN(A1)-1)

I'm absolutely certain the stored data has no extra space/characters.
I'm using Excel VBA to pull the data into a spreadsheet, but I need to compare it first hence the query.

[millz]

Then you can use VBA code to remove the extra character(s) first before comparing:

Please Login or Register  to view this content.

or as suggested by Glenn,

Please Login or Register  to view this content.

[Jay-T]

Seems like I can't address the fundamental issue of having a extra character,

I'll just have to remove the characters as they get produced.

Okay, thanks guys!

[Glenn Kennedy]

My post at #3 was a bit cryptic, as the formula disappearred.

=CODE(RIGHT(A1,1))

What value do you get... 32, or 160, or what

[millz]

Seems like I can't address the fundamental issue of having a extra character,

Well, I am not familiar enough with mySQL to know where the problem lies exactly. It could be the database or field setting, it could be the connection type, it could be ... I don't know

Don't remember facing such issues when querying MSSQL/Access databases though.

[Doc.AElstein]

Get rows New Line carriage return Wonks Line feed.. Lateral Thinking. vbCR vbLf

Hi I am coming in very late here, but this thread just helped me solve a similar mystery that I had had.
( and the Thread is not marked as Solved , and i am adding...a solution.. in a lateral thinking sort of a way )

My Problem:

I was developing a code to remove code lines.
This required checking the first Character in the code line to see if it was a number.

For example, I copy the three code lines 10 , 20 , 30 below to the Clipboard ( Highlight those three lines and Hit Ctrl+C )

Please Login or Register  to view this content.

Then I run my code to remove code lines, for example they start like the demo code below:

In My code below, section Rem 1) , takes the String text held in the Clipboard and '1 c) puts it in an Array , arrCodlnsIn1() ( - '1 c) uses the VBA Strings function Split to split the String from the Clipboard by the carriage return vbCr )
'1 d) checked out the first character. I expected that line 200 to catch “1” “2” and “3” . But I caught just “1” in the first loop. After that I caught what “looked” like “ “

This is what the I see in the watch window: – _...
LfSpace.JPG
http://imgur.com/rQN3qCN
Attachment 482239

_...you “see” extra spaces before “ 20” and “ 30”. But I am guessing that that is the interpretation of what the “thing” is.. (_... often an obscure Chr( ) may be present in a string and what we “see” may come out in whatever form the particular thing we use to look at it interprets it.
http://www.mrexcel.com/forum/excel-q...ml#post4214083 _......)


_..................................

After reading this thread I modified a version of my code to produce the demo code for this post
Line 220 shows me nothing in cell A1 for what I see in the watch window as “ “
In line 230 I sandwich the “thing” between a couple of pipes , | | , and display that in the immediate Window. I see in the Immediate Window this

|
|

Still not getting too far. But then from this thread I caught the Code( ) idea.

Line 240 in my code below cracked it for me. It returns the value of 10. Looking up an ASCII Table tells me that is a new line. Then I thought .. New Line, sounds like Line Feed.

Rem 2 ) Has another go at splitting the text string with a carriage return and a line feed, vbCr & vbLf

Bingo ! ( three times ! ) - So my mysterious character was a line Feed.

I am only just now trying to get clued up with “SQL’s and ADODB.recordset stuff”, but I believe it has a lot to do with interpreting data that gets chucked at you in a long text “stream”
Clearly if you are “getting rows” then various chr( ) are included staring from the end of the first row. My problem similarly to the OP’s started at the end of the first data and /or start of the second, which amounts to the same thing I suppose.

Once I found out what the character was, the solution was clear. I am not sure how that translates to the OP problem. But some lateral thinking base on my experience might suggest another idea, but I cannot think of anything just now.

Alan


P.s. Just for fun My demo code Rem 3). I split by just a vbLF. I “see” in the Immediate window at the end of the first two “rows” a “space” “thing” which is not there.
CrSpace.JPG
http://imgur.com/5i1IpMR
Attachment 482240

Surprise, surprise, Line 420 in my demo code gives ASCII character 13 – a Carriage Return, vbCr



Demo code:

Please Login or Register  to view this content.

[millz]

FYI, you can do Split function with vbCrLf

[Doc.AElstein]

Thanks.
I had noticed that, I assume it is identical to vbCr & vbLf. I notice now that a lot of people use vbCrLf. Previously I had used vbCr for no particular reason and " got away with it".
I guess vbCrLf could be a good default choice.
I find it good though to delve in a bit and "see" what is actually "there”
Were you able to reveal what character was giving you problems?
Alan