August 9, 2018 at 9:51 am
Hello,
Is it possible to import multiple xls files in bulk concurrently, and if so how?
I would like to somehow automate / create tables via imported data in bulk.
Current Process: DB> Tasks > Import Data > SSIEW > {Choose XLS) > next > next> etc.
Basically I have large number of .xls files I import to create tables, and can't do one by one.
Thanks
August 9, 2018 at 10:46 am
are they all the same format? You could setup an SSIS package to process all xls file in the folder. That import data function creates an SSIS package. At the end of it, you could save it, then modify it to use dynamic file names and db connections
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 10, 2018 at 7:06 am
Hi Mike,
Yes all columns are of same format. I'm a total newb to SSMS, so let alone SSIS. Not sure if i can reference another thread that may help solve problem, but there is a script someone posted here, post #117410
I'm not sure of, how to modify script, I think it may be a simply changing file name, but then, also not sure how to run. i.e.
Would be appreciated if can put in plain english, i.e. Step 1# first run this script, Step 2# run this portion of script, etc.
Run first:
Declare
Select
Declare
Select
Insert Into
..Any help would be appreciated
August 10, 2018 at 9:03 am
I looked at the link. It is a 9 year old solution that does need modifying. I would use SSIS and as opposed to this script, but here's a quick explanation of what I saw
This uses xp_cmdshell which is disabled by default and with good reason. That allows you to shell out to the operating system to perform functions as necessary. You inherit the permissions of the account that started the SQL Server Service, so it's usually elevated. This allows anyone with knowledge of it and bad intentions to do bad things
-- Setting up variables to folder where excel files are
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
-- getting directory of all files in folder
DECLARE @cmdOutput TABLE(line VARCHAR(4000))
SELECT @cmd = 'dir ' + @archivePath + @templateFolder;-- + ' > ' + @archivePath + @templateFolder + 'dir.txt';
-- puts all files into a table variable
INSERT INTO @cmdOutput
EXEC xp_cmdshell @cmd
-- deletes everything in temp table that does not have xls extension
-- this script is 9 years old, so it would need to be changes to handle .xlsx extensions too
DELETE FROM @cmdOutput WHERE RIGHT(line, 4) <> '.xls' OR line IS NULL
-- FileNaming convention has the CreatedDate, Size and name in it
-- so they are getting all the information into another temp table
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
-- then they loop through table and load each file into a temp table.
-- Note they drop and recreate the temp table with each iteration of the loop
-- so you will need to load, process and then repeat for each file
-- Also, they are using Microsoft.Jet.OLEDB.4.0 which may or may not be installed on the server you are running on
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 10, 2018 at 3:27 pm
Hello,
I'm trying to follow you here's where I am at:
Step 1:
DECLARE @archivePath VARCHAR(500), @templateFolder VARCHAR(100), @cmd NVARCHAR(4000);
SELECT @archivePath = 'C:\Users\MyName\Desktop\CT\MyFoldertoImport', -- Excel file path comes here
@templateFolder = 'MyFoldertoImport\'; -- 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';
Commands completed successfully.
Then I ran the following (Step 2)
INSERT INTO @cmdOutput
EXEC xp_cmdshell @cmd
Msg 1087, Level 15, State 2, Line 20
Must declare the table variable "@cmdOutput".
Msg 137, Level 15, State 2, Line 21
Must declare the scalar variable "@cmd".
I also tried running after step 1
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
but no luck. I am using .XLSX
August 12, 2018 at 4:58 pm
Ok..still trying..to understand..& learn..
STEP 1DECLARE @archivePath VARCHAR(500), @templateFolder VARCHAR(100), @cmd NVARCHAR(4000);
SELECT @archivePath = 'C:\Users\MyName\Desktop\CT\MyFoldertoImport', -- Excel file path comes here
@templateFolder = 'MyFoldertoImport\'; -- Folder name which contains all the files with similar format but different data
This worked.
Then I did the following...totally guessing..while it didn't give me an error...nothing happened...
-- STEP 2 --
DECLARE @cmdOutput TABLE(line VARCHAR(4000)) declare @archivePath VARCHAR(500) declare @templateFolder VARCHAR(100) declare @cmd nvarchar(4000);
SELECT @cmd = 'dir ' + @archivePath + @templateFolder;-- + ' > ' + @archivePath + @templateFolder + 'dir.txt';
INSERT INTO @cmdOutput
EXEC xp_cmdshell @cmd
Step 2 I modified from original because I got "Must declare the scalar variable "@archivePath"', so I add all the declare statements..
-- STEP 3 --
DECLARE @cmdOutput TABLE(line VARCHAR(4000))
DELETE FROM @cmdOutput WHERE RIGHT(line, 4) <> '.xlsx' OR line IS NULL
-- STEP 4 --
DECLARE @cmdOutput TABLE(line VARCHAR(4000)) declare @archivePath VARCHAR(500) declare @templateFolder VARCHAR(100) declare @cmd nvarchar(4000);
SELECT @cmd = 'dir ' + @archivePath + @templateFolder;-- + ' > ' + @archivePath + @templateFolder + 'dir.txt';
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
...Steps 2-4 I basically ran this query in chunks..any help would be appreciated..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply