• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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?

Cheers
Matt
0
matt_m
Asked:
matt_m
  • 7
  • 7
2 Solutions
 
celazkonCommented:
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?
0
 
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.
0
 
celazkonCommented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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)
0
 
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
0
 
celazkonCommented:
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
0
 
celazkonCommented:
Also, since I think CONVERT is MSSQL extension, have a look at sybase SQL function called CAST
0
 
matt_mAuthor Commented:
Problem is that I don't use any sql (I'm just calling a stored query)
0
 
celazkonCommented:
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:

msgbox(Range("query_ID").Value)
0
 
matt_mAuthor Commented:
sure the query comes up as

 exec fc_upr_extract "09 Dec 2011", 'N', 'ALL'
0
 
celazkonCommented:
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.
0
 
celazkonCommented:
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.
0
 
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!
0
 
mikeopoloCommented:
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
		
loworkbook.ActiveSheet.Range("A2").Select

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

&& 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.

Regards
Mike
0
 
matt_mAuthor Commented:
Thanks all. IT came back to me and have changed things on their end and all good now. (:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now