Looking for the basic procedure to join an Excel spreadsheet with a table fr
om a database running on MSDE, and then updating the MSDE table with select
columns of info from the Excel spreadsheet. I believe the first set of synt
ax will join the file with
MSDE table, and the second set of syntax will run the import/update. If you
can assist, please advise. Thank you in advance.Here is the code how to link Excel sheet to SQL Server:
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
-- Dejan Sarka, SQL Server MVPAssociate MentorSolid Quality LearningMore
than just Trainingwww.SolidQualityLearning.com"G. Benson"
<anonymous@.discussions.microsoft.com> wrote in message
news:E7453061-6084-4E62-9759-C06341C28CAC@.microsoft.com...
> Looking for the basic procedure to join an Excel spreadsheet with a table
from a database running on MSDE, and then updating the MSDE table with
select columns of info from the Excel spreadsheet. I believe the first set
of syntax will join the file with MSDE table, and the second set of syntax
will run the import/update. If you can assist, please advise. Thank you in
advance.|||Dejan, thank you for your expertise. So I fully understand your solution, a
few questions for you:
? am I running each section of SQL code from within Enterprise Manager
? it has been previously suggested that I should be using DTS for these ty
pes of updates -any thoughts
? if id# exists in both the Excel file and in the SQL database (and is use
d as the common relation) -my only objective is to update a blank column of
names in the SQL database with the values of names from the Excel file -are
all of these steps necessar
y for this type of simple update
I am also a novice when it comes to SQL -could you offer a brief explainatio
n of the function behind each section of SQL statements.
Thanks again.|||Dejan, thank you for sharing your expertise. I apologize, but I am a novice
in this area and would like to ask for your clarification on the following:
? my only objective is to use the id field as the link between the Excel f
ile and the SQL database, and simply update a column of blank names in the S
QL database to the column of names located in the Excel file -are all of the
se commands you provided re
quired/complete to accomplish this task
? would it be possible for you to explain what each section of your SQL co
mmands actually performs
? are each of these commands executed exclusively in the Enterprise Manage
r
? I have been told that if I were going to perform this task repeatedly, I
should be using DTS -could you advise (is this part of Enterprise Manager),
or maybe there is another tool you might suggest which simplifies this link
/update procedure for novic
es (ie Adobe GoLive writes HTML code for non-programmers)
Thank you again for your time and knowledge.|||Dejan, thank you for sharing your expertise. I apologize, but I am a novice
in this area and would like to ask for your clarification on the following:
? my only objective is to use the id field as the link between the Excel f
ile and the SQL database, and simply update a column of blank names in the S
QL database to the column of names located in the Excel file -are all of the
se commands you provided re
quired/complete to accomplish this task
? would it be possible for you to explain what each section of your SQL co
mmands actually performs
? are each of these commands executed exclusively in the Enterprise Manage
r
? I have been told that if I were going to perform this task repeatedly, I
should be using DTS -could you advise (is this part of Enterprise Manager),
or maybe there is another tool you might suggest which simplifies this link
/update procedure for novic
es (ie Adobe GoLive writes HTML code for non-programmers)
Thank you again for your time and knowledge.
-- G. Benson wrote: --
Looking for the basic procedure to join an Excel spreadsheet with a table fr
om a database running on MSDE, and then updating the MSDE table with select
columns of info from the Excel spreadsheet. I believe the first set of synt
ax will join the file
with MSDE table, and the second set of syntax will run the import/update. I
f you can assist, please advise. Thank you in advance.|||Dejan, thank you very much for taking the time and offering your assistance.
I apologize in advance for my lack of knowledge in this area, and would li
ke to request further clarification regarding the following:
* my primary objective is to use the ID field in the Excel file and ID field
in the SQL database as the common link between the two sources; once linked
, I simply need to update a currently blank column of names in the SQL datab
ase with the values of name
s from the Excel file -are the SQL commands you provided all that is require
d to completely link and update this column of data
* are all of these commands executed in steps (one after the other) from wit
hin Enterprise Manager
* could you briefly state what each section of your SQL commands actually pe
rforms
* it has been previously suggested that if this may occur on a regular basis
, I should use DTS - is DTS part of Enterprise Manager; is it a standalone p
roduct or does it come as part of MSDE
* does a standalone software product exist which novices can use to perform
these link/updates (ie similar to Adobe GoLive or MS FrontPage generating al
l HTML code for non-programmers)
Thank you again for sharing your advice and expertise.|||> . am I running each section of SQL code from within Enterprise Manager
This is supposed to run in Query Analyzer. You can use the same code in your
application as well.
> . it has been previously suggested that I should be using DTS for these
types of updates -any thoughts
DTS is another possibility, of course.
> . if id# exists in both the Excel file and in the SQL database (and is
used as the common relation) -my only objective is to update a blank column
of names in the SQL database with the values of names from the Excel
file -are all of these steps necessary for this type of simple update
>
For an occasional connection, you could use the Openrowset function as well,
but is is quite complicated to write the correct connection string for the
function.
> I am also a novice when it comes to SQL -could you offer a brief
explaination of the function behind each section of SQL statements.
>
1. sp_addlinkedserver system SP stores connection string as a server alias
in the master.dbo.sysservers table. So you don't have to type the connection
string every time you need the data from Excel.
2. sp_addlinkedsrvlogin szstem SP establishes securitz context for the
linked server. If linked server is Oracle, for example, zou have to login
there. As Excel does not have some special securitz, we use 'false' as the
2nd parameter.
3. sp_tables_ex and sp_columns_ex szstem SP give you info about tables and
columns on the remote source.
4. Select, Create, Insert are standard T-SQL commands.
5. In Excel, you can name a range of cells with a custom name, so you don't
have to refer to them a Sheet1$.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||> . I have been told that if I were going to perform this task repeatedly, I
should be using DTS -could you advise (is this part of Enterprise Manager),
or maybe there is another tool you might suggest which simplifies this
link/update procedure for novices (ie Adobe GoLive writes HTML code for
non-programmers)
>
In addition:
DTS is a general tool for transferring the data between OLE DB sourcers. You
can read basic of DTS at
http://msdn.microsoft.com/library/d...r />
71v7.asp.
You can check an advance usage of DTS for transferring the data from Excel
at http://www.sqldts.com/default.aspx?243.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com|||Here is a brief description of my actual data:
Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current worksheet sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03
SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114
The primary objective is to update the LOG_SOURCE column of the SQL database
with the values from the Excel worksheet (using ITEMNO as the link). Pleas
e advise as to the following:
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'
* What should I change Excel version name to (using Microsoft Office Excel 2
003)
* could I avoid defining table ranges to save a step or two and simply use c
olumn ranges -in other words, what commands could we eliminate by using Shee
t1 A1:A5 & Sheet1 B1:B5
* Does DTS come as part of MSDE & if so, how do I access it? Would I save a
n time by using DTS rather than your suggested procedure?
* What part of your code triggers the actual update of the SQL server's LOG_
SOURCE column with the Excel values?
Thank you again for your time and expertise.|||Dejan,
Thank you for your reply. Here is a brief description of my actual data:
Excel File name = inventory.xls
File location = C:\Documents and Settings\POS\Desktop\inventory.xls
Excel current worksheet sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
A1 13110 B1 White 3 Ring
A2 13111 B2 White 3 Ring
A3 13112 B3 09/09/03
A4 13113 B4 09/09/03
A5 13114 B5 09/09/03
SQL Table Name = ITEMS
SQL current table sample (both columns are 16 character strings):
ITEMNO LOG_SOURCE
13110
13111
13112
13113
13114
My primary objective is to update the LOG_SOURCE column of the SQL table wit
h the values from the Excel worksheet (using ITEMNO as the link).
I have to apologize in advance for my limited SQL experience, but here is co
de that I've collected thus far to perform the link:
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\POS\Desktop\inventory.xls',
NULL,
'Excel 5.0'
(not sure what I should change Excel version name to if I'm using Microsoft
Office Excel 2003)
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
(I wonder if I could avoid defining table ranges to save a step or two and s
imply use column ranges -in other words, what commands could we eliminate by
using Sheet1 A1:A5 & Sheet1 B1:B5 ?)
(what part of your code triggers the actual update of the SQL server's LOG_S
OURCE column with the Excel values?)
Here is the code I've collected thus far to perform the update:
update Items set log_source = t1.log_source
update items
set
items.LOG_SOURCE = t1.LOG_SOURCE
from t1
inner join Items
on items.ItemNo = t1.ItemNo
I'd like to consolidate this code or simplify this process, if possible. Al
so, does DTS come as part of MSDE & if so, how do I access it? Would I save
any time by using DTS rather than your suggested procedure?
Thank you again for your time and expertise.
pete_m:
Caddre: