SQL ODBC data displayed different then expected

Nite0wls

New Member
Joined
May 21, 2014
Messages
33
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
Hi all,

I have a question regarding the formatting of SQL data the is retrieved with ODBC data link.
When I run the query in SQL this is the data returned:

CRM_Ref_IDi3_identitycallid
7184428:L5701607260007509502001343998
5727866:P5701607260007095792001344950

<tbody>
</tbody>


When I do the same using an ODBC connection I get this as result:

CRM_Ref_IDi3_identitycallid
7184428:L5.70161E+172001343998
5727866:P5.70161E+172001344950

<tbody>
</tbody>

I tried to set the Excel format to display all as text but without success.

Any suggestions as to how I can see the real results and not the scientific notation in the second column would be appreciated.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Hi Francoise,

This is the query:
Code:
USE I3_Dialer
SELECT CRM_Ref_ID, ch.i3_identity, callid, callplacedtime, SKILLNAME, reason, finishcode, agentid, [length]
  FROM dbo.CH_Pipeline ch WITH (NOLOCK)
 INNER JOIN dbo.CL_Pipeline cl WITH (NOLOCK)
    ON ch.I3_IDENTITY = cl.I3_IDENTITY
   AND ch.campaignname = cl.CampaignName
 WHERE SKILLNAME = @Agent
   AND agentid != @Agent
   AND callplacedtime >= @FromWhen
 ORDER BY callplacedtime;

adding the chr(32) & to the ch.i3_identity part doesn't work, do you have any suggestions on this?
 
Last edited:
Upvote 0
Sorry for my prompt answer that is not working.

I don't use SQL Server but I have an SQL Express 2008 on my home system.

When I answered I was thinking of the same problem results when you use MS query importing a query into Excel from another Excel file. Actually after posting I realised that for Excel to Excel actually str("fieldname") will SOLVE the formatting problem and retain the number stored as text as text. It is the same as casting.

I have entered a mock up database not to perform your query by just with one table and 2 other fields to see how it comes back to Excel.

ie:

<code>
SELECT I3_IDENTITY, Qty, Price, id5
FROM dbo.CH_pipeline
</code>


For my data type, I have chosen I3_IDENTITY(char(18),NULL) and for my id5 (text,NULL)

Now it seems to return the proper format using ODBC. I use Excel 2010. I tried getting the ODBC source via different alternative but it seems to keep the formatting.

Not sure what your data type for I3_IDENTITY is and how to you get your query results:by vba macro or by manually creating the link.

May be you would like to explain a little further.

I realised that:
<code>
SELECT SPACE(2) + I3_IDENTITY AS I3_IDENTITY, Qty, Price, CAST(id5 AS char(18)) AS ID5
FROM dbo.CH_pipeline
</code>
retain the formatting when copying from the results pane in SQL and pasting into Excel, so you may want to try these as accepted by SQL syntax check.

Pasting the query results without space(2)......cast......, converts number stored as text as number general format.

I am going to look further into it. I don't think the query is the problem if it return the correct results but incorrect formatting.

I am sorry if at this point I am not that much help. Will keep you posted.
 
Upvote 0
Hi Francoise,

It does not matter what version SQL it is. the issue is this part: ch.I3_Identity

In your query you target it without the ch. what makes the query different and it would work if there is only one I3_Identity, but there are two:
dbo.CL_Pipeline
dbo.CH_Pipeline
both contain the same values but they are different target tables, that's why I need the ch.I3_Identity to tell SQL where to pull the value from, what makes it harder to manipulate it by adding spaces as the query (even in SQL manager itself) doesn't accept this and throws an error.
 
Upvote 0
I am afraid I am not going to be much more help as I am not enough proficient in SQL.

I have created the following view in SQL Server 2008. I haven't used parameters as I know how to use them in Excel or Access but not really in SQL Server. Not quite sure how to return procedure using ODBC.

However the point was how to have the i3_identity displayed properly in an Excel worksheet using ODBC to an SQL query.

Now I created this view in SQL server, creating 2 tables prior to it to mimic your query. Now, if I use ODBC to connect to SQL and return the view, it displays properly so it is not a matter of having same field in 2 differents tables that seems to be the problem as the result in my Excel 2010 is as the table below.

<code>
WHERE SKILLNAME = @Agent
AND agentid != @Agent
</code>
In my query the logical with the above from your query is
SKILLNAME !=agentid

To produce my tables, I used Excel and VBA to produce random data. These tables will not appeared logical as I don't have proper data so I had to improvise. My first table had 21 rows and my second table had 6 rows and the result query returned 5 rows.

I am confident with VBA but not that much with SQL.
You may want to repost your post but unfortunately I think the problem is that people will have to know SQL Server pretty well and also knows Excel on a same level.
I apologise if I wasn't much help and will keep an eye as I am interested in an answer to your problem. I will post a new post to see what other thoughts will come up.

<code>SELECT TOP (100) PERCENT dbo.CH_Pipeline.CRM_Ref_ID, dbo.CH_Pipeline.i3_identity, dbo.CH_Pipeline.callid, dbo.CH_Pipeline.SKILLNAME,
dbo.CH_Pipeline.callplacedtime, dbo.CH_Pipeline.reason, dbo.CH_Pipeline.finishcode, dbo.CH_Pipeline.agentid, dbo.CH_Pipeline.length
FROM dbo.CH_Pipeline INNER JOIN
dbo.CL_Pipeline ON dbo.CH_Pipeline.i3_identity = dbo.CL_Pipeline.i3_identity AND
dbo.CH_Pipeline.campaignname = dbo.CL_Pipeline.campaignname AND dbo.CH_Pipeline.SKILLNAME <> dbo.CL_Pipeline.agentid
WHERE (dbo.CH_Pipeline.callplacedtime >= '2003/01/01')
ORDER BY dbo.CH_Pipeline.callplacedtime
</code>
<table><tr><td></td><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr><tr><td>1</td><td bgcolor='#FFFFFF'>CRM_Ref_ID</td><td bgcolor='#FFFFFF'>i3_identity</td><td bgcolor='#FFFFFF'>callid</td><td bgcolor='#FFFFFF'>SKILLNAME</td><td bgcolor='#FFFFFF'>callplacedtime</td><td bgcolor='#FFFFFF'>reason</td><td bgcolor='#FFFFFF'>finishcode</td><td bgcolor='#FFFFFF'>agentid</td><td bgcolor='#FFFFFF'>length</td></tr><tr><td>2</td><td bgcolor='#FFFFFF'>4823232:Z</td><td bgcolor='#FFFFFF'>411156445643299791</td><td bgcolor='#FFFFFF'>5447499687</td><td bgcolor='#FFFFFF'>skill4</td><td bgcolor='#FFFFFF'>30/08/2008</td><td bgcolor='#FFFFFF'></td><td bgcolor='#FFFFFF'>124</td><td bgcolor='#FFFFFF'>skill3</td><td bgcolor='#FFFFFF'>356</td></tr><tr><td>3</td><td bgcolor='#FFFFFF'>5727866:P</td><td bgcolor='#FFFFFF'>668907511407170244</td><td bgcolor='#FFFFFF'>6937867992</td><td bgcolor='#FFFFFF'>skill6</td><td bgcolor='#FFFFFF'>9/02/2003</td><td bgcolor='#FFFFFF'>rea2</td><td bgcolor='#FFFFFF'>164</td><td bgcolor='#FFFFFF'>skill2</td><td bgcolor='#FFFFFF'>292</td></tr><tr><td>4</td><td bgcolor='#FFFFFF'>7811649:T</td><td bgcolor='#FFFFFF'>104085417457928901</td><td bgcolor='#FFFFFF'>7816322216</td><td bgcolor='#FFFFFF'>skill2</td><td bgcolor='#FFFFFF'>17/12/2003</td><td bgcolor='#FFFFFF'></td><td bgcolor='#FFFFFF'>332</td><td bgcolor='#FFFFFF'>skill4</td><td bgcolor='#FFFFFF'>340</td></tr><tr><td>5</td><td bgcolor='#FFFFFF'>7184428:L</td><td bgcolor='#FFFFFF'>604823032449308201</td><td bgcolor='#FFFFFF'>8651511220</td><td bgcolor='#FFFFFF'>skill2</td><td bgcolor='#FFFFFF'>30/05/2011</td><td bgcolor='#FFFFFF'>rea1</td><td bgcolor='#FFFFFF'>444</td><td bgcolor='#FFFFFF'>skill4</td><td bgcolor='#FFFFFF'>107</td></tr><tr><td>6</td><td bgcolor='#FFFFFF'>1275917:A</td><td bgcolor='#FFFFFF'>179662455690404690</td><td bgcolor='#FFFFFF'>5383103161</td><td bgcolor='#FFFFFF'>skill1</td><td bgcolor='#FFFFFF'>3/05/2012</td><td bgcolor='#FFFFFF'></td><td bgcolor='#FFFFFF'>454</td><td bgcolor='#FFFFFF'>skill3</td><td bgcolor='#FFFFFF'>251</td></tr></table>
 
Upvote 0
Thanks for your response, I might re-post this on an SQL forum instead
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top