Sveučilište u Zagrebu  |  Filozofski fakultet  |  Služba za informatiku  |  Kutak za korisnike  |  Teme


Import podataka iz Excela u MS SQL Server

 

-- How to import Excel data into MS SQL database from within MS SQL using T-SQL?
-- Predrag Gjuro Kladaric, 2007-07-04, gjuro@ffzg.hr
-- information contained here is collected from MS SQL online books, MSDN and various internet sources
-- this script is known to work in MS SQL 2000 and 2005 versions (including Express edition)

-- procedure to import Excel data from within MS SQL server
-- the idea is simple: open the excel spreadsheet as s 'linked server' and then
-- select data from its sheets as if they were tables
-- yes, sheet names that contain blanks DO BOTH require brackets and quotes :-)
-- other only require that dollar sign in the end

-- variable names that I have chosen to use for this example (and you can change it to whatever you want):
-- linked server name:
--      Kadrovska
-- .xls file name:
--      'C:\Razredbenik\scripts\podaci o osoblju 2007-07.XLS'
-- .xls sheet names:
--      osoblje, ustroj, radna mjesta, vrste zvanja, zvanje
-- table names within the linked server:
--      osoblje$, ustroj$, ['radna mjesta$'], ['vrste zvanja$'], zvanje$
-- tables generated by SELECT...INTO statements in this example:
--      ImportOsoblje, ImportUstroj, ImportRadnaMjesta, ImportVrsteZvanja, ImportZvanje

EXEC sp_helpserver     --  just to show existing servers

-- to remove existing linked server specifications and existing tables, if needed
-- EXEC sp_dropserver 'Kadrovska'
-- DROP TABLE dbo.ImportOsoblje
-- DROP TABLE dbo.ImportUstroj
-- DROP TABLE dbo.ImportRadnaMjesta
-- DROP TABLE dbo.ImportVrsteZvanja
-- DROP TABLE dbo.ImportZvanje

EXEC sp_addlinkedserver 'Kadrovska',
   'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
   'C:\Razredbenik\scripts\podaci o osoblju 2007-07.XLS',
   NULL,
   'Excel 8.0'
GO

EXEC sp_tables_ex Kadrovska -- just to show existing tables

SELECT *
INTO dbo.ImportOsoblje
FROM Kadrovska...osoblje$

SELECT *
INTO dbo.ImportRadnaMjesta
FROM Kadrovska...['radna mjesta$']

SELECT *
INTO dbo.ImportUstroj
FROM Kadrovska...ustroj$

SELECT *
INTO dbo.ImportZvanje
FROM Kadrovska...zvanje$

SELECT *
INTO dbo.ImportVrsteZvanja
FROM Kadrovska...['vrste zvanja$']

EXEC sp_dropserver 'Kadrovska'  -- not needed any more after SELECT...INTO has been executed

komentare molim ovdje