Import Multiple Excel files into a Database

Question

Post reply

Import Multiple Excel files into a Database

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

DaPainKiller SSChasing Mays Points: 621 More actions · Answer 1

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;

sudhakar.siram SSC Enthusiast Points: 193 More actions · Answer 2

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

DaPainKiller SSChasing Mays Points: 621 More actions · Answer 3

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.

Phil Parkin SSC Guru Points: 246695 More actions · Answer 4

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 task
hi 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?

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

sudhakar.siram SSC Enthusiast Points: 193 More actions · Answer 5

ok one after the another.I want to get the all rows to my destination in all sheets

DYNAMICISHWAR SSC Journeyman Points: 92 More actions · Answer 6

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.

SQL_Elvis SSC-Addicted Points: 421 More actions · Answer 7

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','$') + ']';

qutub SSC Veteran Points: 224 More actions · Answer 8

The above trick works well if all files have same set of columns, if not.. you need to do some programming 🙂

http://letslearnssis.blogspot.com/

DaPainKiller SSChasing Mays Points: 621 More actions · Answer 9

it can be sorted by keeping the required column in a view.

johnnyfreud786 SSC Enthusiast Points: 112 More actions · Answer 10

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.

http://www.importersads.com/

johnnyfreud786 SSC Enthusiast Points: 112 More actions · Answer 11

johnnyfreud786

SSC Enthusiast

Points: 112

January 28, 2012 at 2:27 am

#1439659

Hi, I really need to know about a directory service.

SQL_Elvis SSC-Addicted Points: 421 More actions · Answer 12

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.

VegasL Hall of Fame Points: 3006 More actions · Answer 13

DaPainKiller - Tuesday, October 20, 2009 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;

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