SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to update a table on SQL Server from Excel ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jzurbo77
Starting Member

USA
21 Posts

Posted - 07/23/2009 :  10:27:00  Show Profile  Reply with Quote
I have a small MS Access application that uses linked tables and pass-thru and append queries. Its single purpose is to refresh a table in SQL Server with data from an Excel table. Access app uses ODBC connection to access SQL Server. Everything works OK.

The problem is that if I want to use that Access/Excel setup from another machine is it not enough to just copy two files (.mdb abd .xls) - I need to build new ODBC connection on that machine.

Is there a way to embed ODBC information into .mdb so it will be portable. I do not see anything machine-specific during ODBC creation and therefore do not see why it can not be copied inside or along with the mdb/xls.

I was thinking to move the *.dsn file from C:\Program Files\Common Files\ODBC\Data Sources but it has one machine-specific parameter: WSID - could I just change to the name of the target machine?

I tried to accomplish same results (update SQL server from Excel file) using linked servers - got an unspecified error message, tried to use ad hoc distributed query but it is turned off on the production server and I do not want to change any of its settings.

any comments / suggestions would be appreciated

thanks

madhivanan
Premature Yak Congratulator

India
22754 Posts

Posted - 07/23/2009 :  10:35:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

craigwg
Posting Yak Master

USA
154 Posts

Posted - 07/23/2009 :  11:35:14  Show Profile  Reply with Quote
I did some really cool stuff that connected Excel directly to SQL. Access is commonly used as a go between but its not necessary if you are comfortable working with VBA in Excel. I started by connecting SQL to Excel using VBA and then creating a custom button so that end users could click the button in Excel and a select statement is run and the results appeared in the excel spreadsheet.

Once I had that working I did some research and found that I could have users update, delete, and insert on any SQL table (or view for that matter) from Excel. That meant I could have end users on Excel updating the database records and never give them access to SQL. Everybody was happy.

I have posted the document I wrote on how to get the basic select working. A little more tooling around and you should be able to get the insert, update and delete working as well. If you like I don't mind posting the Excel file I used as my test. Here is my starting document!

Using Excel to pull data directly from SQL

Before starting it helps to know:
• Name of server to access: USSLCSQLD11
• Name of database to access: MMRS
• Name of table(s) to access: Event, rf_unit
• Name of columns to access: dateop, shift, timecode, duration, unit, description, fleet

1. Open a blank worksheet in Excel.

2. Click Data >> Import External data >> New Database Query.

3. If you have connected to databases in the past you will see those past queries saved here. That means if clients set this up, they only have to do it once per data source. They can just select the old connection and continue.

In our case we are going to select <New Data Source> and then OK.

4. In this new dialog box enter a meaningful name for the data source. This is what will be seen in the future to identify the connection in step 3 above. We will name ours D11Connect.

5. In the next drop down menu, select the driver, SQL Server.

6. Click the Connect button and a new dialog box appears.

7. You can use the drop down menu to add the name of the server, but it’s easier to type it, if known.

8. Click the Options button. This is important!

9. Click the dropdown marked Database and choose the database. In our case we will choose MMRS. Notice that not all databases are available. This may have something to do with rights(?).

10. Leaving all other defaults, click OK. This returns you to the previous dialog box. You can choose a default table if you like but this is optional as it’s likely that you will be joining tables in the next step. Click OK here.

11. Notice that in our original dialog box we now see our named connection, D11Connect. Click OK here.

12. This brings up a new dialog box that has the names of the available objects. You should click the Options… button which will allow you to display only the database objects you want. In our case, insure only the Tables object is checked and click OK.

13. In the next dialog box we can choose our tables or specific columns to display. Notice that when selecting a specific column you can click the Preview Now button which displays a sample of the data. This is helpful for knowing which columns to select.

For now insert the following columns:
Event: dateop, shift, timecode, duration, unit
Rf_unit: description, fleet

14. Click Next. You can then enter filters that are essentially an SQL where statement. In our example we will display only results from July 1st, 2008. When that is entered click Next with no additional filters.

15. You can enter sorting information. In our case we will not sort and click Next again.

16. The next dialog box gives you an option to create the table in Excel exactly as it would appear in a TSQL window. We can also view data and edit query using a tool called Microsoft Query. This will allow to build the query as in MS Access to add more filters, sorting, more columns, etc. The end result of this will be a table in Excel. The third option allows you to build a pivot table. In our case we will choose the second option View data or edit query in Microsoft Query and click Finish.

17. Here we see the results of the query as well as the join conditions. You can add or remove columns more freely here. To add a column, double click it from the tables above. To remove one, select it by clicking the column header in the table and press Delete on your keyboard. Once the query looks the way you want, close the window by clicking the X in the upper right corner.

18. The last step will return you to Excel and ask where you want to place the data. In this case we will leave the default at cell A1 and click OK.

19. Notice the data is all there for manipulation in Excel. So why go through all this trouble? We may be directing clients through how to do this when they don’t have access to Management Studio, or if they want to pull data into Excel for building charts, or other reporting purposes.

The important thing to realize is that Excel can be used to connect directly to an SQL database rather than pulling data from SQL, exporting it as a text file, or copying and pasting it. This may be beneficial to clients who are unfamiliar with SQL or who are very familiar with Excel.

Sources:
http://www.nickhodge.co.uk/pre2007/gui/datamenu/dataexamples/externaldataexamples.htm



Craig Greenwood
Go to Top of Page

srucker
Starting Member

USA
26 Posts

Posted - 09/03/2009 :  11:16:16  Show Profile  Reply with Quote
This was really nice.
Could you share on how users can write the updates back to the SQL Database?
Go to Top of Page

jzurbo77
Starting Member

USA
21 Posts

Posted - 09/04/2009 :  15:53:56  Show Profile  Reply with Quote
quote:
Originally posted by madhivanan

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Madhivanan

Failing to plan is Planning to fail


I used example 2:
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\d4mtpqg1\c$\Excel\junk1.xlsx;HDR=NO',
'SELECT * FROM [Sheet1$]')
Here is what I got:
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
Go to Top of Page

jzurbo77
Starting Member

USA
21 Posts

Posted - 09/04/2009 :  16:08:17  Show Profile  Reply with Quote
quote:
Originally posted by craigwg

I did some really cool stuff that connected Excel directly to SQL. Access is commonly used as a go between but its not necessary if you are comfortable working with VBA in Excel. I started by connecting SQL to Excel using VBA and then creating a custom button so that end users could click the button in Excel and a select statement is run and the results appeared in the excel spreadsheet.

Once I had that working I did some research and found that I could have users update, delete, and insert on any SQL table (or view for that matter) from Excel. That meant I could have end users on Excel updating the database records and never give them access to SQL. Everybody was happy.

I have posted the document I wrote on how to get the basic select working. A little more tooling around and you should be able to get the insert, update and delete working as well. If you like I don't mind posting the Excel file I used as my test. Here is my starting document!

Using Excel to pull data directly from SQL

Before starting it helps to know:
• Name of server to access: USSLCSQLD11
• Name of database to access: MMRS
• Name of table(s) to access: Event, rf_unit
• Name of columns to access: dateop, shift, timecode, duration, unit, description, fleet

1. Open a blank worksheet in Excel.

2. Click Data >> Import External data >> New Database Query.

3. If you have connected to databases in the past you will see those past queries saved here. That means if clients set this up, they only have to do it once per data source. They can just select the old connection and continue.

In our case we are going to select <New Data Source> and then OK.

4. In this new dialog box enter a meaningful name for the data source. This is what will be seen in the future to identify the connection in step 3 above. We will name ours D11Connect.

5. In the next drop down menu, select the driver, SQL Server.

6. Click the Connect button and a new dialog box appears.

7. You can use the drop down menu to add the name of the server, but it’s easier to type it, if known.

8. Click the Options button. This is important!

9. Click the dropdown marked Database and choose the database. In our case we will choose MMRS. Notice that not all databases are available. This may have something to do with rights(?).

10. Leaving all other defaults, click OK. This returns you to the previous dialog box. You can choose a default table if you like but this is optional as it’s likely that you will be joining tables in the next step. Click OK here.

11. Notice that in our original dialog box we now see our named connection, D11Connect. Click OK here.

12. This brings up a new dialog box that has the names of the available objects. You should click the Options… button which will allow you to display only the database objects you want. In our case, insure only the Tables object is checked and click OK.

13. In the next dialog box we can choose our tables or specific columns to display. Notice that when selecting a specific column you can click the Preview Now button which displays a sample of the data. This is helpful for knowing which columns to select.

For now insert the following columns:
Event: dateop, shift, timecode, duration, unit
Rf_unit: description, fleet

14. Click Next. You can then enter filters that are essentially an SQL where statement. In our example we will display only results from July 1st, 2008. When that is entered click Next with no additional filters.

15. You can enter sorting information. In our case we will not sort and click Next again.

16. The next dialog box gives you an option to create the table in Excel exactly as it would appear in a TSQL window. We can also view data and edit query using a tool called Microsoft Query. This will allow to build the query as in MS Access to add more filters, sorting, more columns, etc. The end result of this will be a table in Excel. The third option allows you to build a pivot table. In our case we will choose the second option View data or edit query in Microsoft Query and click Finish.

17. Here we see the results of the query as well as the join conditions. You can add or remove columns more freely here. To add a column, double click it from the tables above. To remove one, select it by clicking the column header in the table and press Delete on your keyboard. Once the query looks the way you want, close the window by clicking the X in the upper right corner.

18. The last step will return you to Excel and ask where you want to place the data. In this case we will leave the default at cell A1 and click OK.

19. Notice the data is all there for manipulation in Excel. So why go through all this trouble? We may be directing clients through how to do this when they don’t have access to Management Studio, or if they want to pull data into Excel for building charts, or other reporting purposes.

The important thing to realize is that Excel can be used to connect directly to an SQL database rather than pulling data from SQL, exporting it as a text file, or copying and pasting it. This may be beneficial to clients who are unfamiliar with SQL or who are very familiar with Excel.

Sources:
http://www.nickhodge.co.uk/pre2007/gui/datamenu/dataexamples/externaldataexamples.htm



Craig Greenwood


very nice script - I was able to pull data into Excel 2007 database - no problem.

But

1) How to (push) export it back from Excel (2007)?

2) How to (pull) read it from Excel 2007 from SQL Server? I tried
select *
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\d4mtpqg1\c$\Excel\junk1.xlsx;HDR=NO',
'SELECT * FROM [Sheet1$]') and got error:
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
thanks
Go to Top of Page

JCirocco
Constraint Violating Yak Guru

USA
392 Posts

Posted - 09/04/2009 :  17:03:26  Show Profile  Visit JCirocco's Homepage  Click to see JCirocco's MSN Messenger address  Reply with Quote
To my knowledge, Microsoft's MS/Query Tool within Excel is One Way only. That is you can read but not update.

Updates are possible if you use Access. Can you embed the Access into Excel? Not sure.

John

"The smoke monster is just the Others doing barbecue"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.19 seconds. Powered By: Snitz Forums 2000