Extract info from SQL Server to MsExcel
Hi all.
I want to share with you, what do you do to extract info from SQL Server to MsExcel.
You have to follow this steps:
1.- create a New View in SQL.
You have to write a query (this is a single example):
SELECT docnum, doctotal, cardcode, cardname
FROM OINV
WHERE docdate between getdate()-30 and getdate()
this query show you the invoices saved in last 30 days.
2.- Go to MsExcel –> Main Menu –> Data –> other sources –> from SQL server
3.- clic on: From SQL Server
In this windows, you have to write a loca IP of your server of SQL. In my case is 10.10.1.4
You have to write a user (in my case the user is INFO), write a password.
clic in NEXT bottom
4- Select the database where you saved your view
and (below) Select the view
5.- clic on finish bottom
6- clic on OK
7- The result of your SQL View, show on msExcel
8- Ready. You have a SAP information from your SQL Database on MsExcel.
9- If you want to refresh the information, you have to go to:
msExcel Main Menu –> Data –> update all
in the windows that apear you have to write
user (in my case the user is “info”)
password
clic on OK.
And the info on msExcel from your SAP database will be updated.
Note: the user info and the password you have to create on SQL Server like a security login. (I show you in the image below)
I hope that it help you.
Best regards.
Saul Hernandez.
Hi Saul,
This is quite interesting, of course, but what's the SAP angle? Wouldn't we be extracting data into Excel using SAP's tools, rather than going directly to the database?
Cheers,
Matt
Hi Matt.
We are using this tool for users that need to make MsExcel Pivot tables with data from SAP database, this users make his pivot tables linked the data that extract of this way. The user only update data (on the excel icon) and all his pivot tables are updated too in a very short time.
For final user is very easy to update information only with clic update icon in msExcel, and for us (programmers) is better to do complicated SQL Queries that extract information directly from a SQL Views.
We are using this way only for very complex SQL Queries that the final users need.
For other cases we are using SAP's tools.
It's depends what information do we need to extract.
Thank you Matt for your comments.
Best regards.
Saul.
But you are suggesting this for SAP NetWeaver systems like SCM, ERP, CRM, etc ?
Best regards,
Luis
I would suggest that this is definitely not a best practice. Yes, it's easy to do, and very tempting when developers are more familiar with SQL than ABAP, but it can cause a number of issues for you in other ways, when looked at from the Basis angle.
For one thing, to provide this sort of access you will need to setup additional user accounts and permissions at the SQL Server database level, which requires all sorts of additional security monitoring and strategy, introducing complexity that cannot realistically be monitored effectively using SAP tools. I can see a number of audit issues right there.
For another, it would be quite easy to draw from the wrong tables without realizing it, and thus provide potentially misleading views into the data. SAP's data model can, in some places, be quite complex, with the full view of data spread out across header tables, line item tables, check tables, etc. On top of that, many times the data is only available via cluster tables (think, BSEG), which do not exist at the database level but are managed in the application's data dictionary, or via ABAP code and through a structure, which again doesn't exist at the database level. There are numerous ways the SAP application abstracts the data that simply isn't easy to replicate when bypassing the application and going straight to the data. What about HR cluster tables like PCL2? These do exist at the database level, but they are "compacted" and not easily readable except via SAP's APIs.
How are you ensuring data consistency? Much of the enqueue locking and transaction isolation level functionality is handled in the application layer, even though SQL Server is perfectly capable of handling this itself. This could lead to "dirty read" situations that are unintentional.
What happens when you upgrade the system, and something in the data dictionary changes that breaks your SQL access scheme?
If you don't want to use ABAP to access the data, why not use the many web services and other external APIs that SAP provides? There are numerous supported ways to allow .NET applications, or tools like Excel, have access to SAP data that do not bypass the application, and which are protected against underlying changes in upgrades.
Hi Matt.
Respect to sql's user security: Yes, you have reason. I have manage another security control for this user.
About data consistency: For now the database consistency is correct and no problem whit this.
And for upgrade system, We didn't have problem on the last upgrade system that we did.
I will use SAP tools to access SQL Data rather than this way.
Thanks for your suggestions and comments.
Best regards.
Saul.