March 29, 2012 at 9:35 am
HI ALL,
I am having a little trouble trying to import data from Excel 2007 into SQL server 2008 R2. I am running windows Enterprize N 64 bit with Office 2007 Professional 32 bit.
SQL to import data
SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',',
'Data Source=C:\test\TEST.xlsx;Extended Properties=Excel 8.0')...[Sheet1$]
I have also tried to use 'Microsoft.Jet.OLEDB.4.0'
when i try to run these queries i get a couple of differnt errors
1. Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
2. OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I have looked around the internet and according to this thread http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace it should work however it doesn't 🙁
Any one got any suggestions?
Does anyone know if i can call a DTS package and pass it parameter with a file name and sheet name?
My task is to monitor a folder and when a XLSX file appears import the data into an SQL table, a few hours later a new file will appear with a different name
***The first step is always the hardest *******
March 29, 2012 at 9:52 am
Yes, you should be able to use an SSIS package to import your spreadsheet data. You'll need to call the 32-bit DTS runtime from within your stored procedure to run the package. Example:
c:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe /F c:\mypath\mypackage.dtsx
March 29, 2012 at 10:19 am
My task is to monitor a folder and when a XLSX file appears import the data into an SQL table, a few hours later a new file will appear with a different name
I would create a job that runs at whatever frequency is appropriate. First step looks for the .xlsx. When found, copy to an archive folder, then rename it to a static name. Next step run an SSIS package to import from static name. Next step delete the static name.xlsx. Next step, maintain the archive folder, keeping x days of files around.
March 29, 2012 at 12:42 pm
HI Randy,
Thank you for your help, identifying a file name is the easy bit and that bit of my script works fine, renaming the file i can do too however, the sheet name to be imported is the same name as the file name like for example DataFIle20120329_2031.xlsx sheet name DataFIle20120329_2031, files do not come in any logical order so the next one could be DataFIle20120329_2999 so i was hoping that i could execute a DTS package and or SSIS package and pass the file name and sheet name as a parameter.
Unless i can fix the issue i have with the installed 32 bit office, i think that i may just uninstall office from the PC and try the access redistributable in the other forum i posted earlier will update this thread if that works 🙂
:w00t:
***The first step is always the hardest *******
March 29, 2012 at 1:06 pm
I don't know the answer. Maybe ask in the Integration Services forum. I used to do that with DTS, set up a variable in the package and pass a parameter to populate the variable. But I've never done it with SSIS.
March 29, 2012 at 1:21 pm
If I have read your requirement correctly:
You can have a for each loop container in SSIS and specify a folder and wildcard extension, *.xlsx for example. You can then assign a variable in the variable mapping tab that is effectively each file name the loop encounters.
You would then use this variable as a connection string for a excel object, and then you can manipulate the spreadsheet that way in a data flow task. It may be best to use a script task if you want to get hold of the worksheet name.
'Only he who wanders finds new paths'
March 29, 2012 at 1:56 pm
I believe you can do this directly within SQL but first you'll need to make sure you have the "Data Connectivity Components" update, available at http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
See this article for more information - http://social.microsoft.com/Forums/is/communicationsserversqldatabase/thread/079907db-5ded-4766-ac95-3c1020fbcfcb
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
April 11, 2012 at 1:53 pm
Well i never managed to get the issue fixed with 64 bit pc having a 32 bit office but it did work on windows XP :), here is script 1.1 just incase its of use..
Create proc IMPORTXLSX (
@path varchar (255) ='C:\temp\'
)
AS
Begin
/*
OUR REF: #943
NAME: IMPORT_XLS.sql
VERSION: 1.1
AUTHOR: Glen Wass
DATE CREATED: 29/03/2012
DESCRIPTION: Script to identify xls files in a folder and process
IMPACT:
EXAMPLE EXEC: EXEC IMPORTXLSX
VERSION HISTORY:
DATEVERSIONAUTHORREFCOMMENTS
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
29/03/2012|1.0| GW |#943| INITIAL RELEASE
30/-3/2012|1.1| GW| #943| Added Movements
*/
Set nocount on
if object_id('tempdb.dbo.#file')is not null
begin
drop table #file
end
if exists (select 1 from sysobjects (nolock)
where name='IMPORTXLSX'
and xtype='U')
Begin
Drop table IMPORTXLSX
end
create table #file (Name varchar(255))
declare
@filename varchar(255),
@find varchar(255),
@insert varchar(1000),
@move varchar(1000),
@MKDIR varchar(1000)
--Set @path='C:\'
Set @find='insert into #file exec master..xp_cmdshell '+ CHAR(39) +' dir /B '+ @path + '*.xlsx'+ CHAR(39)
Set @MKDIR='exec master..xp_cmdshell '+ CHAR(39) +' IF NOT EXIST '+ @path +'Archive MKDIR '+@path +'Archive'+ CHAR(39)
exec (@MKDIR)
exec (@find)
set @filename=( select Name from #file
where Name is not null)
if @filename='File Not Found'
begin
select 'error!!!!!'+@filename
return
end
else
begin
Set @move='exec master..xp_cmdshell '+ CHAR(39) +' move '+ @path +@filename +' '+@path +'Archive\'+@filename+ CHAR(39)
Set @insert='SELECT * '
Set @insert=@insert+'INTO [dbo].[IMPORTXLSX] '
Set @insert=@insert+'FROM OPENROWSET('+char(39)+'Microsoft.ACE.OLEDB.12.0'+ char(39)+','+ char(39)
Set @insert=@insert+'Excel 12.0;Database='+@path+@filename+ char(39)+','+ char(39)
Set @insert=@insert+'SELECT * FROM ['+replace(@filename,'.xlsx','$]')+CHAR(39)+')'
exec (@insert)
exec (@move)
end
END
***The first step is always the hardest *******
April 16, 2012 at 9:07 pm
Hi all,
How to import data from ".txt" file to SQL SERVER with out duplicates Using SSIS package.
Please give me suggestion
means I have a records in the text file like thise
id,name
1,aa
1,aa
2,bb
3,cc
I want to data without duplicates Like this
id,name
1,aa
2,bb
3,cc
"Please note i need without using Execute SQL task"
Regards
Naresh Reddy
April 17, 2012 at 12:50 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply