Trimming Strings From A Recordset

Hi All,

I have a macro that pulls in a ADODB recordset into excel and pastes it in the spreadsheet.  This all works well but the problem is that the strings (numbers and dates are ok) come with a bunch of trailing spaces.  Its all pretty strange as they are not like that in the database.

At the moment I have added some code to the macro that trims all the cells after the pasting however it takes a while to do so I was wondering if there was a better solution out there?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I'm using exactly the same approach to view data in excel from MSSQL without any issue.
what db engine are you using? Also what datatype is defined on the affected field's data in DB?
matt_mAuthor Commented:
The data comes from a Sybase databse.  Not sure ababout the datatypes as I don't have direct access to the database

It mighrt be worth noting I use Microsft query to pull data in and there is no problems.
I wonder if the problem is with the ado recordset object in excel. Try to look at the data itself and count the number of characters in the field (including the spaces). If the number of characters including the spaces in the column is always the same, regardless of the actual data length, it is most probably the datatype in db and the excel recordset instance, that the culprit.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

matt_mAuthor Commented:
Yes they are all coming up as 100 characters!  Any ideas on how to proceed? Note the recordset contains 20 fields or so (numbers, dates and strings)
matt_mAuthor Commented:
I've added the relevant bits of code below:

Dim cn As New ADODB.Connection
Dim rst2 As New ADODB.Recordset
Dim constr, sqlstr As String

constr = "DSN=FMGPROD2;UID=" & strUID & ";PWD=" & strPWD & ";DB=fc_prd; Connect Timeout=10"

cn.Open (constr)

Set rst2 = New ADODB.Recordset

rst2.Open Range("query_ID").Value, cn, 1, 1

Sheets("Sheet1").Range("A2").CopyFromRecordset rst2
The problem is the char datatype in DB, you should get it as varchar into excel, so only the data without spaces are displayed. Easiest option would be to convert the column directly inside the SQL, see the CONVERT(varchar,columnname) SQL function for correct sybase syntax
Also, since I think CONVERT is MSSQL extension, have a look at sybase SQL function called CAST
matt_mAuthor Commented:
Problem is that I don't use any sql (I'm just calling a stored query)
It is stored in excel range, first I would try to display the range with stored SQL statement into the msgbox window to actually see the SQL itself:

matt_mAuthor Commented:
sure the query comes up as

 exec fc_upr_extract "09 Dec 2011", 'N', 'ALL'
Aah, it's a stored procedure, without cooperation with somebody who has the db admin role on the db server, I'm afraid you can't make anything faster & without spaces. Either option would be to change the stored procedure, or to change the datatype of the particular field of the table in DB. Hard to say what would be easier without the insight on the actual data & the procedure itself.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Perhaps it's a view, not procedure. But that's not important, the point is to change the actual SQL syntax on the fc_upr_extract DB object and add the CAST or CONVERT SQL function on the particular column.
matt_mAuthor Commented:
ok cool I'll check with IT and see if they can help me out.

I'll leave the question open for a while tos ee if anyone else has comments - thanks for your help!
I've experienced the same issue writing data from a FoxPro cursor to Excel, using a recordset. I ended up using an alternative (older) method using arrays:

&& Step 1.1 - Create array of data records

SELECT tmptable
COUNT TO lnRecCount
STORE FCOUNT() TO lnfcount
DIMENSION ga_tmptable(lnfcount,lnRecCount)
SELECT * FROM tmptable;
INTO ARRAY ga_tmptable

&& Step 3 - Open Excel and insert data

WITH loExcel.Selection.Resize(lnRecCount,lnFCount)
  .formula = (GetArrayRef('ga_tmptable'))

&& At foot of code

PROCEDURE GetArrayRef(tcArrayName)
RETURN @&tcArrayName

Open in new window

The only issue now is that occasionally I have to pre-format the spreadsheet columns to avoid Excel messsing them around, eg account numbers such as 1-1234 will sometimes get read in as dates.

Not sure whether you would be able to do the same in your environment.

matt_mAuthor Commented:
Thanks all. IT came back to me and have changed things on their end and all good now. (:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.