Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

get SQL Server text data type into Excel pivot table

Is it possible to get the full text of SQL Server TEXT data types into Excel
pivot tables?

SQL Server 7 and Excel 2002 SP3
I have a table that has "text" data type column. I have an ODBC connection
to the database. I go to Excel's PivotTable Wizard and select the ODBC data
source, and then the table and columns, but I only get a max of 253
characters in my pivot table for the text type column (I know it's being
truncated). I've found some VB code to get CLOB data from various DB types
but I still don't know if it's possible to get that into my pivot table.

Thanks in advance,
Lee


Post your answer or comment

comments powered by Disqus
Is it possible to get the full text of SQL Server TEXT data types into Excel
pivot tables?

SQL Server 7 and Excel 2002 SP3
I have a table that has "text" data type column. I have an ODBC connection
to the database. I go to Excel's PivotTable Wizard and select the ODBC data
source, and then the table and columns, but I only get a max of 253
characters in my pivot table for the text type column (I know it's being
truncated). I've found some VB code to get CLOB data from various DB types
but I still don't know if it's possible to get that into my pivot table.

Thanks in advance,
Lee

I have an actual sales data source, and budget sales data source, i need to
compare actual vs budget years, is there a way to merge two data source into
one pivot table?

I have the following problem ...

When copying data from a Memo field (more than 255 characters) in Access to
a spreadsheet cell, I lose some of the data; it's being cut off. Is this a
problem with a limit to cell size or is it due to a size limit for the Text
data type in Excel?

Does anyone have any helpful suggestions?

Thanks ...

Dear Reader,

I need length of the data type of excel and also want to know the
technique to set the Excel column data type. These things I want with
the help of Visual Basic Code.

If it is possible to do so then please reply me as soon as possible.

Regards,

Sanjay Bol

I am trying to use data in an Excel pivot table in another application, but
am finding that data sorted by row, where multiple lines are available for my
first sort on row the row label only displays against the first line, not
subsequent lines. How can I get it to display against each line?

I have been experimenting lately with Excel Pivot tables and nice Dialogs for end-users. That is, it is a much nicer experience for a manager or CEO to bring up a dialog to fill in query criteria than the MS Query engine. I would insert a screen shot but I am new to MrExcel and don't know if this is supported.

For my testing, the data has been kept in an Access 2007 database with a query in that db that produces the results. The Pivot table is linked to that query which is called Qry021_Pivot. Using the technique, after the user fills in the criteria, I change the query for qry021_pivot (at runtime in Excel), save it, and then do a refresh all on the Excel Worksheet. It seems to work
very well, Now to the question.

I wonder if it is possible to do something similar for a query type file in SQL server, and if so how? The code for how I do this using Access as the back-end follows. If anyone has an idea of how to do the same in SQL Server I
would appreciate a response.

Thank You

'CODE in OK button of Dialog
Private Sub btnOK_Click()
Dim dbCurr As DAO.Database
Dim strPristineSQL As String
Dim strWhere As String
Dim qdfCurr As DAO.QueryDef
Dim strSQL As String

'strPristineSQL is the SQL before any changes
strPristineSQL = "SELECT tblInvoices.CompanyID, ...

'This calls a function that gets the criteria from the filled in fields of the dialog
strWhere = FetchWhereString()
strSQL = strPristineSQL
strSQL = strSQL & "WHERE " & strWhere

'The line below is the line that actually changes and saves the new Access query (qry021_pivot)
Set qdfCurr = dbCurr.QueryDefs("Qry021_Pivot")
qdfCurr.Sql = strSQL

Set qdfCurr = Nothing
dbCurr.Close
Set dbCurr = Nothing
Unload frmDlg01
ActiveWorkbook.RefreshAll
End Sub

Hi everyone,

I’m having a problem with TEXT Data Type in Sybase. (TEXT is used to store “REMARKS” which may be very long)

I have this TEST1 table for testing:
create table TEST1 (
	DATE	datetime,
	REMARKS 	text
)
(1) I am able to insert some string into the “REMARKS” field but not retrieve it using "select"(Please refer to attached).
The button only attempt to display the “REMARKS” and “DATE” in “TEST1” which already has some records.
? Runtime error at the following line: ODBC--Call failed!
Any ideas how to overcome this?

(2) I am able to insert each profile name into a single line into the “REMARKS” filed.
However, retrieving the profile names line by line (to write to different rows in Excel spreadsheet) is going to be tough (Only after (1), is answered and I am able to get rstRtn("REMARKS"), of course :D).
Any ideas?

Thanks for any help

I would consider my self an intermediate user of excel, and I am just not
sure what the fastest way of formatting my text file would be.

I imported a text file into excel and now it needs some manipulation. I
know how to do most, but I am stuck on how to get my item number linked to
the appropriate data. See below.

Item: 12345
1015839 37315 12.83 12.33 .50

1015668 37315 64.17 61.67 2.50

1015661 37315 38.50 37.00 1.50

Item: 67890
1000495 13732 22.36 20.86 1.50

1000639 37233 22.36 20.86 1.50

1000339 37239 22.27- 20.77- 1.50-

1000686 37246 22.36 20.86 1.50

I need to link the item number to each line in its group so then I can sort
by item and then I can go on from there and use the data and create a pivot
table to get my answers. I just would like to know if there is something
quicker than cutting a pasting the item number next to each line that doesn't
have the item number? I have 177 items and 16,000 rows of data, and 5 more
reports like this to run. I am waiting on our IT to redo the reports itself
so it will include the item number in each line, but who knows when that will
happen. Please help if you can or give me some advice. Thanks. Sorry if I
am not using the correct terminology.

Hi! I have created Pivot tables in Excel 2000 that are using tables from a SQL Server 2k database. The client is running Win2k Professional with SP3 and Office 2000. The server is running Win2k Server with SP3, and SQL Server 2000 with SP2.

The SQL 2k installation has been setup for Mixed Security mode. To access data using the pivot tables, I created an ODBC connection in the System DSN area, with the necessary sa password for SQL authentication. The pivot tables work fine if I log on, but when I login with a test ID (created especially with proper rights to test installations) that has equal rights as myself, I notice the following things: -
1. The ODBC I created in System DSN when I logged in with my ID is nowhere to be found, so I have to create it once again as user Test.
2. The pivot table excel sheet does not refresh, it gives an ODBC error as follows: -
SQL State 28000
SQL Server error 18456
Login failed for user 'domain nameuser name'
3. A small dialog with the "Trusted Connection" checkbox ticked pops up asking for login ID and password. I untick the checkbox and enter 'sa' and the sa password - still the connecion does not happen and the data does not refresh in the pivot tables, and the error continues.
4. The problem gets sorted our ONLY if the pivot table is recreated when logged in as the Test user, or for that matter any other user who is required to use that pivot table, and the pivot tables work only on the machine and only for the user. Any other user logging on to this machine, and the pivot tables don't work!

My problem is, I cannot go around re-designing pivot tables on each client computer. The excel pivot tables have been created and saved on a shared location on the server, providing access to all the required users. I also cannot disclose the sa password to the individual users because that is a security hazard. I am just fed up trying to make this work, it refuses to budge. Your help will be really appreciated.

Your assistance will truly be appreciated.

Thanks.

Hi there,

Is it possible to get SQL to run directly from an Excel cell. What I mean is is it possible to say have a prewritten SQL statement in cell A1 that when the spreadsheet is opened or refreshed the values generated are entered into cell A2?

Thanks in advance.

I'm calling a stored procedure on our SQL server to generate a pivot
table. I want to be able to set one worksheet cell to a "start date"
value and one to an "end date" value, then have the stored procedure
called with these cell values as arguments @start and @end. I think I
am almost there but I don't know how to get the value from the cell
into the Pivot Table properties. If I open the Script Editor, I see the
section

<x:PTSource>
...snip..
<x:QuerySource>
...snip..
<x:CommandText>exec sp_sales '2/1/2006',
'2/28/2006';</x:CommandText>
...snip...
</x:QuerySource>
</x:PTSource>

If I change the values in the CommandText line, it make the Pivot Table
use those arguments the next time I refresh it. This is exactly what I
want, but I want to have the values of, say, cells A1 and A2 instead of
'2/1/2006' and '2/28/2006'.

Any ideas? I'm a super-noobie at programming in Excel, I'm sure this
must have a simple answer.

Thanks!

I'm stuck with ms excel as an interface with ms access as my back-end. This
started off as a simple project and as simple goes, evolved into complex. My
problem is how to display a memo data type in excel OLE objects.

thanks in advance.

I want to use Pivot Table with SQL Server

Which books is the best?

Pivot Table Data Crunching (Business Solutions)

OR

Pivot Table Data Crunching for Microsoft Office Excel 2007 (Business Solutions)

OR

OTHER

Thanks

I currently have an excel spreadsheet which contains a lot of data. I am
then manipulating this data to find root cause. I am graphing the data in
several differnt formats. The questions I have is would it make sense to
move this data into a pivot table and pivot charts to be able to control the
data managment better? Also if you can send me simple links in regard to how
pivot tables work and thier benefits it would be appreciated. Thanks for the
help, Darlene

Hello,

sorry for my english, i have this problem: i have pivot table in excel (pivot table data is connected to access database), when i double-click to show detail, i see the detail in new sheet, but the limit for 1 sheet is 65536 rows but the count of rows in detail is much more - so how can I get the data with VBA before excel show the data in new excel-sheet and show the detail in new access file or a text file?... How can i do it in VBA? Or without VBA? Thank's

I am currently using an Access Frontend with Tables to create several
Excel Pivot tables and charts. Everything is working great but I was
wanting to simplify the code a little if possible. Here is the code
snippit I am looking at changing:

MyDataSetConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=" & datasetpath & "" & datasetDB & ";" & _
"DefaultDir=" & datasetpath & ";" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5)"

With newbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = MyDataSetConn
.CommandType = xlCmdSql
.CommandText = "SELECT " & _
"IPO_History_tbl.IpoDate, " & _ etc.

The Datasetpath and datasetDb are located in the Access file that this
code is running from. I don't like to hardcoat the paths. I do
suppose that I could get the file path but I would like to go one step
further. Can the .PivotCaches.Add, Point to itself? Why go through
the ODBC connection when the Data is in the current Access file.
Could I do something like this :

Get rid of the "MyDataSetConn =" section and

With newbook.PivotCaches.Add(SourceType:=internal)
.CommandType = xlCmdSql
.CommandText = "SELECT " & _
"IPO_History_tbl.IpoDate, " & _ etc.

Any help would be greatly appreaciated.

Thank you,

Darren

Hi,

I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).

Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.

Any other solution to enable the automatic refresh on open the excel workbook?

Or Access can overwrite the exist file or save it as another file name with timestamp ?

Thanks

I am currently using an Access Frontend with Tables to create several
Excel Pivot tables and charts. Everything is working great but I was
wanting to simplify the code a little if possible. Here is the code
snippit I am looking at changing:

MyDataSetConn = "ODBC;DSN=MS Access Database;" & _
"DBQ=" & datasetpath & "" & datasetDB & ";" & _
"DefaultDir=" & datasetpath & ";" & _
"DriverId=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5)"

With newbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = MyDataSetConn
.CommandType = xlCmdSql
.CommandText = "SELECT " & _
"IPO_History_tbl.IpoDate, " & _ etc.

The Datasetpath and datasetDb are located in the Access file that this
code is running from. I don't like to hardcoat the paths. I do
suppose that I could get the file path but I would like to go one step
further. Can the .PivotCaches.Add, Point to itself? Why go through
the ODBC connection when the Data is in the current Access file.
Could I do something like this :

Get rid of the "MyDataSetConn =" section and

With newbook.PivotCaches.Add(SourceType:=internal)
.CommandType = xlCmdSql
.CommandText = "SELECT " & _
"IPO_History_tbl.IpoDate, " & _ etc.

Any help would be greatly appreaciated.

Thank you,

Darren

Hi,

Is there a way to copy a pivot table to a new worksheet, and then have the sub-total and total formulas put into the pivot table automatically?

I have a spreadsheet with a pivot table on it, that I need to send to a 3rd party monthly. I have been copying the pivot table as values and then puting in the formulas manually. The reason I do this is so the 3rd party can't double-click the pivot tabel and get to the data behind it.

If I could automate this somehow that would be great.

Thanks,

Hi there. My name is Wilbert. I have 4 workbooks and each workbook has 2-4 worksheets which have the same headings. What I would want to do is to combine all these workbook and worksheet data into 1 pivot table.

Using the multiple consolidation range is not too good because of its limitations.

I am familiar with VBA but not too familiar working with database lingo particularly odbc, sql, external data sources,etc.

Can anyone help me here? Thanks

So here i am with another issue that has popped up with this dam project im working on. Here is the idea:

I have created queries in an access database that compare 2 sets of data, previous and current, and compares them using a function that i wrote in VBA (fComp()). Instead of writing a HUGE iif statement in access, i wrote this function to make it nice and clean. It works very well.

Now comes the second part of my project, Excel pivot tables. Because everyone that will be using the data found in the Access database wants pivot tables, i must give them pivot tables. So i create a spreadsheet for each comparison query that i have written in Access and start setting up a pivot table using the Access database as the datasource. As simple as that sounds, i get this error "Undefined function 'fComp' in expression." I know what it's telling me, but i dont know WHY it's telling me this. Since Access would be runnign the query to populate the data for the pivot table, i cannot understand why it's pooping out on me. Is there a way to pass that function from Access VBA to Excel VBA?

help.

Emanuele

Hello Board,

I need a way to consolidate information from 5 plants with 7+ salesmen ea.who may have 25-30 customers each. I need to take a excel form I created for each salesperson with forcast and monthly customer info from each salesperson and summarize it for the regional sales manager with meanful data. There is currently a column for Forcast data, current month data, previous month data and last year at this time date. Should I consider using excel, excel pivot table or access. Each salesperson emails this data month to me and its my job to summarize it for the manager. Right now the only thing I can think of, is a spreadsht with tabs for each of the 5 plants and list the salesperson down a column etc...then somehow combine that information into a summary for the month after all 40-45 salespeople have turned in their info, but is there a better, quicker and more effective way to summarize this data...any suggestions? Thanks in advance

I am converting lab data into a Pivot Table, and the data has a less than
sign(

Although I realize this isn't the typical method of using a pivot table, I'm
attempting to display text in the data field for my pivot table. The three
source columns consist of 1) a strategy, 2) an activity focused at attacking
that strategy and 3) an organizational contribution that coincides with both.
For example, the source worksheet column name, and input would look
something like this for a single entry:

Source Columns: Strategy Activity Team Contribution
Row Inputs: Safety Study Accrue Data

I realize I can display all three by inputting then in the row area of the
pivottable, but I'd like to input the strategy in the row area, activity in
the column area, and team contribution in the data area to display the
information a bit differently. Is it possible, with a source file input
change, to show the data in the "data field" as text rather than numbers?
Thanks.

-Allan


No luck finding an answer? You could always try Google.