October 20, 2009 at 9:34 am
I have a very simple script which imports multiple excel files into a SQl table:
USE [Landcatch]
DECLARE @archivePath VARCHAR(500), @templateFolder VARCHAR(100), @cmd NVARCHAR(4000);
SELECT @archivePath = 'C:\', -- Excel file path comes here
@templateFolder = 'FolderName\'; -- Folder name which contains all the files with similar format but different data
DECLARE @cmdOutput TABLE(line VARCHAR(4000))
SELECT @cmd = 'dir ' + @archivePath + @templateFolder;-- + ' > ' + @archivePath + @templateFolder + 'dir.txt';
INSERT INTO @cmdOutput
EXEC xp_cmdshell @cmd
DELETE FROM @cmdOutput WHERE RIGHT(line, 4) <> '.xls' OR line IS NULL
SELECT CAST(LEFT(line, 20) AS DATETIME) AS created, CAST(REPLACE(SUBSTRING(line, 21, 18), ',', '') AS INT) AS size, SUBSTRING(line, 40, LEN(line)-39) AS name
INTO #dir
FROM @cmdOutput
IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_ImportedDataFromFolder_')
DROP TABLE dbo._ImportedDataFromFolder_
CREATE TABLE [dbo].[_ImportedDataFromFolder_](
[fileName] [varchar](100) NULL,
[rownumber] [int] NOT NULL,
[Programme] [varchar](50) NULL,
[Site] [varchar](50) NULL,
[YearGroup] [int] NULL,
[PedigreeType] [varchar](50) NULL,
[BlupID] [varchar](50) NULL,
[ImportedDataFromFolder_] [varchar](50) NULL,
[AnimalType] [varchar](50) NULL,
[ReadDate] [datetime] NULL,
[Family] [varchar](50) NULL,
[FUFamily] [varchar](50) NULL,
[CurrentTank] [varchar](50) NULL,
[MaleParentFounder] [varchar](50) NULL,
[FemaleParentFounder] [varchar](50) NULL,
[Operator] [char](50) NULL
) ON [PRIMARY]
DECLARE @fileName VARCHAR(100), @openDataSourcePath VARCHAR(4000), @sql NVARCHAR(4000), @columnCount INT
WHILE (SELECT COUNT(*) FROM #dir) > 0
BEGIN
SELECT TOP 1 @fileName = [name] FROM #dir;
IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_ImportedDataFromFolder_Temp')
DROP TABLE dbo._ImportedDataFromFolder_Temp
SELECT @openDataSourcePath = 'Data Source=' + @archivePath + @templateFolder + LTRIM(RTRIM(@fileName)) + ';Extended Properties=Excel 8.0';
PRINT @openDataSourcePath
SELECT @sql = '
SELECT ''' + @fileName + ''' AS fileName, identity(int, 1, 1) as rownumber, *
INTO _ImportedDataFromFolder_Temp
FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ''' + @openDataSourcePath + ''')...[Sheet1$]'
BEGIN TRY
EXEC sp_executesql @sql
SELECT @columnCount = COUNT(Column_Name) FROM Information_Schema.Columns WHERE Table_Name = '_ImportedDataFromFolder_Temp';
IF (@columnCount = 16) INSERT INTO _ImportedDataFromFolder_ SELECT * FROM _ImportedDataFromFolder_Temp
END TRY
BEGIN CATCH
PRINT 'There was an error: ' + ERROR_MESSAGE()
END CATCH
DELETE FROM #dir WHERE [name] = @fileName;
END
DROP TABLE #dir
DELETE FROM _ImportedDataFromFolder_ WHERE rownumber < 10;
August 11, 2011 at 1:21 am
i have one excel file that file contain 5 sheets.how to load all sheets at a timeto my destination in one data flow task
hi guys give solution to my question
August 11, 2011 at 2:13 am
You could put all the sheet names in a system table and pick up all the names one by one from there, in a WHILE loop.
August 11, 2011 at 2:22 am
sudhakar.siram (8/11/2011)
i have one excel file that file contain 5 sheets.how to load all sheets at a timeto my destination in one data flow taskhi guys give solution to my question
You would do better to start a new thread - your request is related, but different, from what the rest of the thread is discussing.
When you say 'all sheets at a time' are you suggesting parallelism? Or is one after the other sufficient?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 11, 2011 at 2:53 am
ok one after the another.I want to get the all rows to my destination in all sheets
August 23, 2011 at 12:09 pm
First create a connection with the Excel file that you want to pull data from (just create a simple connection) then copy the connection string generated into the expression builder of the XML source task. Choose connection string property and in the expressions builder paste the connection string and replace the filename with the variable that you have created i.e."+@[User::FileName]+" and walaah your task is done.
September 19, 2011 at 12:36 pm
umertahir (10/20/2009)
I have a very simple script which imports multiple excel files into a SQl table:
USE [Landcatch]
DECLARE @archivePath VARCHAR(500), @templateFolder VARCHAR(100), @cmd NVARCHAR(4000);
SELECT @archivePath = 'C:\', -- Excel file path comes here
@templateFolder = 'FolderName\'; -- Folder name which contains all the files with similar format but different data
DECLARE @cmdOutput TABLE(line VARCHAR(4000))
SELECT @cmd = 'dir ' + @archivePath + @templateFolder;-- + ' > ' + @archivePath + @templateFolder + 'dir.txt';
INSERT INTO @cmdOutput
EXEC xp_cmdshell @cmd
DELETE FROM @cmdOutput WHERE RIGHT(line, 4) <> '.xls' OR line IS NULL
SELECT CAST(LEFT(line, 20) AS DATETIME) AS created, CAST(REPLACE(SUBSTRING(line, 21, 18), ',', '') AS INT) AS size, SUBSTRING(line, 40, LEN(line)-39) AS name
INTO #dir
FROM @cmdOutput
IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_ImportedDataFromFolder_')
DROP TABLE dbo._ImportedDataFromFolder_
CREATE TABLE [dbo].[_ImportedDataFromFolder_](
[fileName] [varchar](100) NULL,
[rownumber] [int] NOT NULL,
[Programme] [varchar](50) NULL,
[Site] [varchar](50) NULL,
[YearGroup] [int] NULL,
[PedigreeType] [varchar](50) NULL,
[BlupID] [varchar](50) NULL,
[ImportedDataFromFolder_] [varchar](50) NULL,
[AnimalType] [varchar](50) NULL,
[ReadDate] [datetime] NULL,
[Family] [varchar](50) NULL,
[FUFamily] [varchar](50) NULL,
[CurrentTank] [varchar](50) NULL,
[MaleParentFounder] [varchar](50) NULL,
[FemaleParentFounder] [varchar](50) NULL,
[Operator] [char](50) NULL
) ON [PRIMARY]
DECLARE @fileName VARCHAR(100), @openDataSourcePath VARCHAR(4000), @sql NVARCHAR(4000), @columnCount INT
WHILE (SELECT COUNT(*) FROM #dir) > 0
BEGIN
SELECT TOP 1 @fileName = [name] FROM #dir;
IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_ImportedDataFromFolder_Temp')
DROP TABLE dbo._ImportedDataFromFolder_Temp
SELECT @openDataSourcePath = 'Data Source=' + @archivePath + @templateFolder + LTRIM(RTRIM(@fileName)) + ';Extended Properties=Excel 8.0';
PRINT @openDataSourcePath
SELECT @sql = '
SELECT ''' + @fileName + ''' AS fileName, identity(int, 1, 1) as rownumber, *
INTO _ImportedDataFromFolder_Temp
FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ''' + @openDataSourcePath + ''')...[Sheet1$]'
BEGIN TRY
EXEC sp_executesql @sql
SELECT @columnCount = COUNT(Column_Name) FROM Information_Schema.Columns WHERE Table_Name = '_ImportedDataFromFolder_Temp';
IF (@columnCount = 16) INSERT INTO _ImportedDataFromFolder_ SELECT * FROM _ImportedDataFromFolder_Temp
END TRY
BEGIN CATCH
PRINT 'There was an error: ' + ERROR_MESSAGE()
END CATCH
DELETE FROM #dir WHERE [name] = @fileName;
END
DROP TABLE #dir
DELETE FROM _ImportedDataFromFolder_ WHERE rownumber < 10;
Nice script. It works great in a 32-bit environment. If you are working in a 64-Bit SQL Server environment you need to download and use the Microsoft.ACE.OLEDB.12.0 driver and use that in your OPENDATASOURCE call,
Sample code snippet:
SELECT @openDataSourcePath = 'Data Source=' + @archivePath + LTRIM(RTRIM(@fileName)) + ';Extended Properties=''''Excel 12.0''''';
PRINT @openDataSourcePath;
SELECT @sql = '
SELECT ''' + @fileName + ''' AS fileName, identity(int, 1, 1) as rownumber, *
INTO _ImportedDataFromFolder_Temp
FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.12.0'', ''' + @openDataSourcePath + ''')...[' + REPLACE(@fileName,'.xls','$') + ']';
September 20, 2011 at 4:40 am
The above trick works well if all files have same set of columns, if not.. you need to do some programming 🙂
September 20, 2011 at 4:44 am
it can be sorted by keeping the required column in a view.
January 24, 2012 at 12:14 am
Hey, I am trying to get import directory information for my new business which I want to establish in USA. Directory list contains all the documentation , address, phone number information by which I can contact them and talk about my business, but I am looking for genuine updates USA import directory list not one which haven’t updated in recent years. Thanks in advance.
January 28, 2012 at 2:27 am
Hi, I really need to know about a directory service.
January 30, 2012 at 11:30 am
Your Question has nothing to do with the thread to which it is attached. If you want to get a response that is pertinent to your question, I would suggest posting it as a new thread with its own topic.
August 9, 2018 at 4:23 pm
DaPainKiller - Tuesday, October 20, 2009 9:34 AMI have a very simple script which imports multiple excel files into a SQl table:USE [Landcatch] DECLARE @archivePath VARCHAR(500), @templateFolder VARCHAR(100), @cmd NVARCHAR(4000); SELECT @archivePath = 'C:\', -- Excel file path comes here @templateFolder = 'FolderName\'; -- Folder name which contains all the files with similar format but different data DECLARE @cmdOutput TABLE(line VARCHAR(4000)) SELECT @cmd = 'dir ' + @archivePath + @templateFolder;-- + ' > ' + @archivePath + @templateFolder + 'dir.txt'; INSERT INTO @cmdOutput EXEC xp_cmdshell @cmd DELETE FROM @cmdOutput WHERE RIGHT(line, 4) <> '.xls' OR line IS NULL SELECT CAST(LEFT(line, 20) AS DATETIME) AS created, CAST(REPLACE(SUBSTRING(line, 21, 18), ',', '') AS INT) AS size, SUBSTRING(line, 40, LEN(line)-39) AS name INTO #dir FROM @cmdOutput IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_ImportedDataFromFolder_') DROP TABLE dbo._ImportedDataFromFolder_ CREATE TABLE [dbo].[_ImportedDataFromFolder_]( [fileName] [varchar](100) NULL, [rownumber] [int] NOT NULL, [Programme] [varchar](50) NULL, [Site] [varchar](50) NULL, [YearGroup] [int] NULL, [PedigreeType] [varchar](50) NULL, [BlupID] [varchar](50) NULL, [ImportedDataFromFolder_] [varchar](50) NULL, [AnimalType] [varchar](50) NULL, [ReadDate] [datetime] NULL, [Family] [varchar](50) NULL, [FUFamily] [varchar](50) NULL, [CurrentTank] [varchar](50) NULL, [MaleParentFounder] [varchar](50) NULL, [FemaleParentFounder] [varchar](50) NULL, [Operator] [char](50) NULL ) ON [PRIMARY] DECLARE @fileName VARCHAR(100), @openDataSourcePath VARCHAR(4000), @sql NVARCHAR(4000), @columnCount INT WHILE (SELECT COUNT(*) FROM #dir) > 0 BEGIN SELECT TOP 1 @fileName = [name] FROM #dir; IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_ImportedDataFromFolder_Temp') DROP TABLE dbo._ImportedDataFromFolder_Temp SELECT @openDataSourcePath = 'Data Source=' + @archivePath + @templateFolder + LTRIM(RTRIM(@fileName)) + ';Extended Properties=Excel 8.0'; PRINT @openDataSourcePath SELECT @sql = ' SELECT ''' + @fileName + ''' AS fileName, identity(int, 1, 1) as rownumber, * INTO _ImportedDataFromFolder_Temp FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'', ''' + @openDataSourcePath + ''')...[Sheet1$]' BEGIN TRY EXEC sp_executesql @sql SELECT @columnCount = COUNT(Column_Name) FROM Information_Schema.Columns WHERE Table_Name = '_ImportedDataFromFolder_Temp'; IF (@columnCount = 16) INSERT INTO _ImportedDataFromFolder_ SELECT * FROM _ImportedDataFromFolder_Temp END TRY BEGIN CATCH PRINT 'There was an error: ' + ERROR_MESSAGE() END CATCH DELETE FROM #dir WHERE [name] = @fileName; END DROP TABLE #dir DELETE FROM _ImportedDataFromFolder_ WHERE rownumber < 10;
Hi,
Can you help me out, I have file names called as an example:
ABC Company - #1234 +2018-01-02.xls
DEF Company - #1323 +2018-01-02.xls
GHI Company - #5678 +2018-01-02.xls
etc.
Where you have comments in the script - its very clear to switch out the name. However, I'm not clear what to do here:
IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_ImportedDataFromFolder_')
DROP TABLE dbo._ImportedDataFromFolder_
CREATE TABLE [dbo].[_ImportedDataFromFolder_]
specifically, here, ImportedDataFromFolder_
If the folder where I have saved these xls files is named: MyFilestoImport, do I simply rewrite your script as:
IF EXISTS (SELECT * FROM sys.tables JOIN sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id WHERE sys.schemas.name = N'dbo' AND sys.tables.name = N'_MyFilestoImport_')
DROP TABLE dbo._MyFilestoImport_
CREATE TABLE [dbo].[_MyFilestoImport_]
Basically replace ImportedDataFromFolder with foldername where xls files are stored, yes?
Thats all thats required, a side from ensuring the column names in matches to whats in the xls columns, correct?
Or how do I name within this script those seperate xls file names?
Thanks
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply