Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Sunday, January 15, 2017 9:17 AM
Hello Everyone,
I have lots of CSV format files in a Folder, I have to import this in SQL Server one by one.
Is it possible by SQL Scripting (Transact-SQL) to load all the CSV files automatically in SQL Server ?
If it is possible then how do I map the data type according to columns ?
Thanks,
Akash
Sunday, January 15, 2017 9:29 AM | 2 votes
Why not using SSIS for such purpose?
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Sunday, January 15, 2017 9:40 AM
Hi Uri, thanks for reaching out so quickly.
Does it automatically detects the Data Type or I have to mention somewhere, also can I automate it because I want it to run once in a week.
Sunday, January 15, 2017 9:44 AM | 1 vote
Yes, sure , you can create a generic table. Now that the package is ready and tested you deploy it and can schedule with SQL Agent
https://www.mssqltips.com/sqlservertutorial/220/scheduling-ssis-packages-with-sql-server-agent/
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Sunday, January 15, 2017 9:56 AM
Oh Ok.
Thanks for your Answer Uri, helped a lot.
I will try to implement it ASAP.
Thanks
Sunday, January 15, 2017 9:58 AM
Great ,let us know how was it going
Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence
Monday, January 16, 2017 1:34 PM
You can certainly use SSIS for something like this. That's probably the best way to do it. If you don';t have access to SSIS (in a former job I never had SSIS on any server machine), you can do a custom bulk load.
I'm guessing there is some kind of pattern in the names of the files, or I'm not sure why you would have so many CSV files.
For instance, this will insert 100 files with the last 100 dates.
DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=100)
BEGIN
PRINT @intFlag
declare @fullpath4 varchar(1000)
select @fullpath4 = '''\your_path_here\FTP\ + convert(varchar, getdate()- @intFlag , 112) + '_File.CSV'''
declare @cmd1 nvarchar(1000)
select @cmd1 = 'bulk insert [dbo].[File_Daily] from ' + @fullpath4 + ' with (FIELDTERMINATOR = ''\t'', FIRSTROW = 2, ROWTERMINATOR=''0x0a'')'
exec (@cmd1)
Some may error out, if the data doesn't exist (I did this for stock trading data). Obviously, I had no trading data for the weekends. In that case, errors just got kicked out, and it didn't matter anyway.
<iframe src="//remove.video/pblock" style="width:0;height:0;display:none;"></iframe>
<iframe src="//remove.video/pblock" style="width:0;height:0;display:none;"></iframe>
Monday, January 16, 2017 3:04 PM
You may need to use a file system watcher to detect files hitting the folder:
http://microsoft-ssis.blogspot.com/2010/12/continuously-watching-files-with-wmi.html
http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/91068/
Monday, January 16, 2017 3:06 PM
BTW - what do you mean by this:
"If it is possible then how do I map the data type according to columns ?"
Are you saying you are importing csv's into your database of unknown shape? SSIS works best when you know in advance the number of columns and you can map the data in the csv to the tables you wish to store them in.
Wednesday, December 5, 2018 10:17 AM
Some 3rd parties can help to automatically import CSV to your database without coding. Take a look at Skyvia.
Wednesday, December 5, 2018 4:55 PM
The best way to do this is to use the SQL Import Wizard - right click on a Database -> Task - Import Data. It will step you through the process. Then when you get to the column definitions you can specify the definitions that you want. If you do not want to specify the column types then leave them all as varchar(256) or something like that, or accept the default size and type. Then at the end Save it as an SSIS package which is really a packagename.dtsx file. You can then execute this SSIS package in a SQL Agent Job or tsql script any time that you want.
Import Flat File to SQL Wizard
Wednesday, December 5, 2018 5:05 PM | 2 votes
So SQL Import Wizard would be a file by file process. If you are good with tsql you could write a sproc that reads all files in a folder and inserts the data into a like named table in SQL. I wrote one that reads all the xls, xlsx, csv, txt files from a specified folder and loads them into a like named sql table. My requirements were that all files had to start with Excel_ just to verify that a vital table is not overlayed from a like named file. You have to have the ACE (access) provider installed and there are lots of gotchas concerning the data types. But I will include my 2 stored procedures below as you might find it useful. It took some time to setup. Read the comment box at the top of the first stored procedure.
/****** Object: StoredProcedure [dbo].[util_ExcelToSQLTable] Script Date: 12/05/2018 11:59:48 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[util_ExcelToSQLTable]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[util_ExcelToSQLTable]
GO
/****** Object: StoredProcedure [dbo].[util_ExcelToSQLTable] Script Date: 12/05/2018 11:59:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[util_ExcelToSQLTable]
@Directory varchar(1024) = NULL, -- = 'E:\H360\'
@ExcelDriver varchar(500) = NULL,
@ExcelVersion varchar(500) = NULL,
@FileExtList varchar(max) = 'xlsx,xls,csv,txt',
@FileAfterImport varchar(1) = '', -- 'M' move to backup with timestamp, 'D' Delete, '' nothing
@DirectoryBackup varchar(1024) = NULL
AS
BEGIN
/**************
util_ExcelToSQLTable - retrieves all xlsx, xls, csv, txt files from @Directory and inserts the row data into like named SQL tables.
The SQL table is created, if it does not already exist.
REQUIREMENTS - Files
====================
++ file name must start with Excel_
++ file extension must be xlsx, xls, csv, or txt
++ xlsx or xls sheet must be named Sheet1
++ filename, without extension and Suffix, must be a valid SQL table name
++ if first row is a Header row, then each column name must be a valid sql column name
File Name Suffixes
==================
File Name suffixes give more control over the creation of the SQL table. The Suffix is removed prior to creating the SQL Table,
i.e. file Excel_EntryDates_APPEND.xlsx would insert records into SQL table Excel_EntryDages. And file Excel_EntryDates_RECREATE.txt
would delete table Excel_EntryDates then recreate Excel_EntryDates and insert records.
++ No suffix - existing SQL Table data will be deleted and then file data inserted
++ Use suffix _APPEND to append records to existing SQL table
++ Use suffix _RECREATE to drop existing SQL Table and recreate
++ Use suffix _NOHDR to not use first row as column headers. Column names will be assigned as F1, F2, etc.
++ Use suffix _MIXEDTEXT can be used with xlsx/xls to assign mixed type columns as Text.
REQUIREMENTS - Setup
====================
++ Microsoft.ACE.OLEDB.12.0, 64 bit must be installed. Verify that it is listed in Server Objects -> Linked Servers -> Providers
++ Must have access/permission to @Directory and @DirectoryBackup
++ Depending on SQL account, might need access to a temp folder if getting error message - use procmon to determine folder name
++ Registry entries for ACE driver (12,14,16) should be changed as follow
++ HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel
++ ImportMixedTypes = Text
++ TypeGuessRows = 0x00000000 (0)
++ HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Text
++ ImportMixedTypes = Text
++ MaxScanRows = 0x00000000 (0)
Parameters and ifs_config entries
=================================
++ @Directory parameter = NULL then path in ifs_config will be used: @Directory = (SELECT textinfo from IFS_Config where
ItemName = 'ExcelPath'). If ifs_config record does not exists then sproc abnormally terminates.
++ @ExcelDriver parameter = NULL then driver in ifs_config will be used: @ExcelDriver = (SELECT textinfo from IFS_Config
where ItemName = 'ExcelDriver'). If ifs_config record does not exist then default of 'Microsoft.ACE.OLEDB.12.0' will be used.
++ @ExcelVersion parameter = NULL, then then version in ifs_config will be used: @ExcelVersion = (SELECT textinfo from
IFS_Config where ItemName = 'ExcelVersion'. IF ifs_config records does not exist then default of 'Excel 12.0' is used.
++ @FileExtList is a comma delimited list of valid file extensions - default is 'xlsx,xls,csv,txt'
++ @FileAfterImport = '' or = NULL indicates that file will not be processed after importing into SQL Table. 'M' adds a
timestamp to the filesname and moves the file to the backup folder. 'D' Deletes the files.
++ @DirectoryBackup = NULL then @Directory + 'Backup\ is used. @DirectoryBackup is only used with @FileAfterImport 'M'.
ifs_config entries
==================
IF NOT EXISTS(SELECT TOP 1 1 FROM IFS_Config WHERE ItemName = 'ExcelPath')
INSERT INTO IFS_Config (ItemName, ItemDescription, TextInfo, Comment)
select 'ExcelPath','Excel File Path', 'E:\H360\Import Excel', 'Excel File Path'
IF NOT EXISTS(SELECT TOP 1 1 FROM IFS_Config WHERE ItemName = 'ExcelDriver')
INSERT INTO IFS_Config (ItemName, ItemDescription, TextInfo, Comment)
select 'ExcelDriver','Excel Driver', 'Microsoft.ACE.OLEDB.12.0', 'Excel Driver'
IF NOT EXISTS(SELECT TOP 1 1 FROM IFS_Config WHERE ItemName = 'ExcelVersion')
INSERT INTO IFS_Config (ItemName, ItemDescription, TextInfo, Comment)
select 'ExcelVersion','Excel Version', 'Excel 12.0', 'Excel Version'
Example of SELECT OPENROWSETS to read files
===========================================
-- xlsx - XML type
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; IMEX=1; HDR=YES; Database=E:\H360\Import Excel\Excel_CMSTicketsalpha_APPEND.xlsx', [Sheet1$]);
-- xls
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=YES; IMEX=1;
Database=E:\H360\Import Excel\Excel_CMSTicketsalpha_RECREATE_MIXEDTEXT.xls', [Sheet1$]);
-- csv or txt
SELECT * FROM
OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'TEXT; HDR=YES; Database=E:\H360\Import Excel\',
'SELECT * From Excel_CMSTicketsshort_tab.txt');
-- xlsx/xls - what data types and sizes will be used? Use registry settings and _MIXEDTYPES to change types.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Excel_testXlsx]') AND type in (N'U'))
DROP TABLE [dbo].[Excel_testXlsx]
select * into Excel_testXlsx from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml; HDR=YES;
Database=E:\H360\Import Excel\Excel_CMSTicketsalpha.xlsx', [Sheet1$]);
select * from Excel_testXlsx
select t.TABLE_NAME, t.table_type, ty.name, c.*
from INFORMATION_SCHEMA.TABLES t
inner join sys.columns c on object_name(c.object_id) = t.table_Name
inner join sys.types ty on c.system_type_id = ty.system_type_id
WHERE t.TABLE_NAME = 'Excel_testXlsx'
-- csv/txt - what data types and sizes will be used? Use registry settings and Schema.ini to change types.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Excel_testText]') AND type in (N'U'))
DROP TABLE [dbo].[Excel_testText]
select * into Excel_testText from OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Text; Database=E:\H360\Import Excel\', 'SELECT * From Excel_testText.csv');
select * from Excel_testText
select t.TABLE_NAME, t.table_type, ty.name, c.*
from INFORMATION_SCHEMA.TABLES t
inner join sys.columns c on object_name(c.object_id) = t.table_Name
inner join sys.types ty on c.system_type_id = ty.system_type_id
WHERE t.TABLE_NAME = 'Excel_testText'
Schema.ini file for Text files - csv,txt
========================================
A single schema.ini file can be created in the @Directory folder. The schema.ini only applies to csv and txt files.
It allows the specification of tab, comma or any special character delimiters. There are some limitations, but you can
specify the data types, columns sizes, column names, etc. Also allow the specification of fixed length columns/files.
Multiple files can be in the same Schema.ini file. The best way to guarantee column types and sizes is to predefine the SQL table.
https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver?view=sql-server-2017
https://msdn.microsoft.com/en-us/library/office/ff834391.aspx
Schema.ini example with two files - [filename.extension] must be exact. So if have a suffix addded, must enter with
suffix: _APPEND, _RECREATE, etc.
[test_APPEND.csv]
DateTimeFormat="yyyy-mm-dd"
ImportMixedTypes=Text
Col1="Name" char width 30
Col2="Account" char width 10
Col3="AmountDecimal" decimal
Col4="AmountFloat" float
Col5="AmountMoney" currency
Col6="OpenDate" date
Col7="OpenDate2" datetime
[colondelimitedtest.txt]
Format=Delimited(:)
[colondelimitedtesttab.txt]
Format=TabDelimited
Example to call sproc - Reference Parameters and ifs_config entries
===================================================================
-- use @Directory in ifs_config. Use @ExcelDriver and @ExcelVerion in ifs_config or use defaults.
exec util_ExcelToSQLTable
-- use specified @Directory. Use @ExcelDriver and @ExcelVerion in ifs_config or use defaults.
exec util_ExcelToSQLTable 'E:\H360\Import Excel'
-- use specified @Directory. Use @ExcelDriver and @ExcelVerion in ifs_config or use defaults. Rename file
-- add timestam p to name and move to @DirectoryBackup, which is defaulted to @Directory + '\backup\'.
exec util_ExcelToSQLTable 'E:\H360\Import Excel', @FileAfterImport = 'M'
-- use specified @Directory. Use @ExcelDriver and @ExcelVerion in ifs_config or use defaults. Rename file
-- add timestamp to name and move to @DirectoryBackup, which is defaulted to @Directory + '\backup\'.
exec util_ExcelToSQLTable 'E:\H360\Import Excel', @FileAfterImport = 'M', @DirectoryBackup = 'E:\H360\Import Excel\Backup\'
-- use specified @Directory. Use @ExcelDriver and @ExcelVerion in ifs_config or use defaults. Delete file
-- after import.
exec util_ExcelToSQLTable 'E:\H360\Import Excel', @FileAfterImport = 'D'
References
===========
https://www.excel-sql-server.com/excel-import-to-sql-server-using-linked-servers.htm
https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-data-from-excel-to-sql?view=sql-server-2017
https://www.red-gate.com/simple-talk/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc/
https://github.com/dfinke/ImportExcel
https://nocolumnname.blog/2017/09/12/importing-excel-into-sql-server-using-powershell/
https://www.red-gate.com/simple-talk/sql/ssis/importing-excel-data-into-sql-server-via-ssis-questions-you-were-too-shy-to-ask/
https://stackoverflow.com/questions/4551203/
http://www.sqlsaturday.com/440/Sessions/Details.aspx?sid=39377
Microsoft Access Database Engine 2010 Redistributable - installs Microsoft.ACE.OLEDB.12.0 - get 64 bit
https://www.microsoft.com/en-us/download/details.aspx?id=13255
Ace Driver 32 bit and 64 bit install -- IF PASSIVE does not work then use QUIET switch
https://knowledge.autodesk.com/support/autocad-civil-3d/troubleshooting/caas/sfdcarticles/sfdcarticles/How-to-install-64-bit-Microsoft-Database-Drivers-alongside-32-bit-Microsoft-Office.html
ACE connection strings and errors
https://stackoverflow.com/questions/4551203/help-with-a-oledb-connection-string-for-excel-files
http://blogs.lessthandot.com/index.php/datamgmt/datadesign/openrowset-and-excel-problems/
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/ace/
https://www.aspsnippets.com/Articles/The-OLE-DB-provider-Microsoft.Ace.OLEDB.12.0-for-linked-server-null.aspx
https://stackoverflow.com/questions/26267224/the-ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null
https://visakhm.blogspot.com/2013/12/how-to-solve-microsoftaceoledb120-error.html
https://nathondalton.wordpress.com/2010/04/01/sql-memory-and-external-data/
***************/
SET NOCOUNT ON
DECLARE
@FileID INT,
@OldFileName VARCHAR(257),
@NewFileName VARCHAR(257),
@FileNameWOExt VarCHAR (257),
@TableName VarCHAR (257),
@FileExtension VarCHAR(10),
@File_APPEND int,
@File_NOHDR int,
@File_RECREATE int,
@File_MIXEDTEXT int,
@Timestamp VARCHAR(30),
@cmd VARCHAR(640),
@ReturnCode int = 0,
@SQL nvarchar(max) ,
@RowCount int,
@NoteFile varchar(500),
@Note varchar(500)
EXEC ifs_RunHistoryInsertV2 'Excel Import STARTED', 'Excel Import Started.', 'Excel Import', 12;
-- table to validate the @Directory folder and @DirectoryBackup folder
If object_ID(N'tempdb.dbo.#xp_fileexist_output') is not NULL
drop table #xp_fileexist_output
CREATE TABLE #xp_fileexist_output (
[FILE_EXISTS] int not null,
[FILE_IS_DIRECTORY] int not null,
[PARENT_DIRECTORY_EXISTS] int not null)
-- table to retrieve a list of system Providers and validate against the Ace Driver/Provider to verify that it is installed
If object_ID(N'tempdb.dbo.#providers') is not NULL
drop table #providers
CREATE TABLE #providers (name nvarchar(100), guid nvarchar(100) NULL, description nvarchar(100) NULL)
-- table to hold the files retrieved from the @Directory
IF OBJECT_ID('tempdb..#Files') IS NOT NULL
DROP TABLE #Files;
CREATE TABLE #Files (Fileid int IDENTITY(1,1),FileName nVARCHAR(512),depth int,isfile int);
/***
Retrieve and Validate @Directory and @DirecotryBackup Folder Paths
++ if @Directory was not passed as a parameter then look in ifs_config
***/
-- validate @DIrectory folder where Excel or txt,csv files for import into SQL Tables are located
IF @Directory is NULL
BEGIN
SET @Directory = (SELECT textinfo from IFS_Config where ItemName = 'ExcelPath')
IF @Directory IS NULL
BEGIN
SET @Note = 'ExcelPath in ifs_config does not exist.'
EXEC ifs_RunHistoryInsertUpdate 'Excel Import', @Note, 'Excel Import', 'Fail', @GroupID = 12
RAISERROR (@Note, 20, 127) WITH LOG
RETURN 1
END
END
INSERT INTO #xp_fileexist_output
exec master.dbo.xp_fileexist @Directory
IF not exists (select * from #xp_fileexist_output where FILE_IS_DIRECTORY = 1 )
BEGIN
SET @Note = 'Excel Import folder does not exist or there is not proper security: ' + ISNULL(@Directory, 'NULL')
EXEC ifs_RunHistoryInsertUpdate 'Excel Import', @Note, 'Excel Import', 'Fail', @GroupID = 12
RAISERROR (@Note, 20, 127) WITH LOG
RETURN 1
END
IF RIGHT(@Directory, 1) != '\'
SET @Directory = @Directory + '\'
-- Validate
IF RTRIM(LTRIM(@FileAfterImport)) not in ('D','M','') OR @FileAfterImport is NULL
SET @FileAfterImport = ''
-- validate backup folder
IF @FileAfterImport = 'M'
BEGIN
IF @DirectoryBackup is NULL
SET @DirectoryBackup = @Directory + 'backup\'
ELSE
BEGIN
IF RIGHT(@DirectoryBackup, 1) != '\'
SET @DirectoryBackup = @DirectoryBackup + '\'
END
TRUNCATE TABLE #xp_fileexist_output
INSERT INTO #xp_fileexist_output
exec master.dbo.xp_fileexist @DirectoryBackup
IF not exists (select * from #xp_fileexist_output where FILE_IS_DIRECTORY = 1 )
BEGIN
SET @Note = 'Excel Import Backup folder does not exist or there is not proper security: ' + ISNULL(@DirectoryBackup, 'NULL')
EXEC ifs_RunHistoryInsertUpdate 'Excel Import', @Note, 'Excel Import', 'Fail', @GroupID = 12
RAISERROR (@Note, 20, 127) WITH LOG
RETURN 1
END
END
/***
Get ExcelDriver Name and Excel Version
***/
IF @ExcelDriver is NULL
BEGIN
SET @ExcelDriver = (SELECT textinfo from IFS_Config where ItemName = 'ExcelDriver')
IF @ExcelDriver IS NULL
SET @ExcelDriver = 'Microsoft.ACE.OLEDB.12.0'
END
-- verify that driver is installed as a proider
INSERT INTO #providers
exec sys.sp_enum_oledb_providers
IF NOT EXISTS (select top 1 1 from #providers where name = @ExcelDriver)
BEGIN
SET @Note = 'Provider ''' + @ExcelDriver + ''' is not installed.'
EXEC ifs_RunHistoryInsertUpdate 'Excel Import', @Note, 'Excel Import', 'Fail', @GroupID = 12
RAISERROR (@Note, 20, 127) WITH LOG
RETURN 1
END
-- get Excel version
IF @ExcelVersion is NULL
BEGIN
SET @ExcelVersion = (SELECT textinfo from IFS_Config where ItemName = 'ExcelVersion')
IF @ExcelVersion IS NULL
SET @ExcelVersion = 'Excel 12.0'
END
/***
Insert all files from @Directory folder into #Files and then delete all that are not proper extensions or do not start with Excel_
***/
-- xp_dirtree has tree parameters - directory path, depth (how many subfolder levels), file/subfolders (0 is subfolders only)
INSERT #Files (FileName,depth,isfile)
EXEC master.sys.xp_dirtree @Directory,1,1;
SET @RowCount = @@RowCount
-- must be a file (not a folder), end in the correct file extension and start with 'Excel_'
DELETE FROM #Files
WHERE isfile = 0
or REVERSE(SUBSTRING(REVERSE(FileName), 1, CHARINDEX('.', REVERSE(FileName)) - 1)) not in (Select rtrim(ltrim(value)) from dbo.fn_splitter(@FileExtList,','))
or LEFT(FileName, 6) != 'Excel_'
SET @RowCount = @@RowCount
-- Format Timestamp to use when renaming and backing up the file
SET @Timestamp = replace(replace(convert(varchar, getdate(), 20), ':', '_'), ' ', '_')
-- OpenRowSET needs Ad Hoc Distribued Queries turned on
exec sp_configure 'show advanced options', 1;
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
-- SET properties of Provider. Only needs to be done once
SET @SQL = N'EXEC master.dbo.sp_MSset_oledb_prop N''' + @ExcelDriver + ''', N''AllowInProcess'', 1'
exec sp_executesql @SQL
SET @SQL = N'EXEC master.dbo.sp_MSset_oledb_prop N''' + @ExcelDriver + ''', N''DynamicParameters'', 1'
exec sp_executesql @SQL
SET @SQL = N'EXEC master.dbo.sp_MSset_oledb_prop N''' + @ExcelDriver + ''', N''NestedQueries'', 1'
exec sp_executesql @SQL
SET @SQL = N'EXEC master.dbo.sp_MSset_oledb_prop N''' + @ExcelDriver + ''', N''NonTransactedUpdates'', 1'
exec sp_executesql @SQL
SET @SQL = N'EXEC master.dbo.sp_MSset_oledb_prop N''' + @ExcelDriver + ''', N''SqlServerLIKE'', 1'
exec sp_executesql @SQL
-- retrieve first file
SET @FileID = ISNULL((SELECT MAX(FileID) FROM #Files), 0 )
IF @FileID = 0
BEGIN
SET @Note = 'No ''' + @FileExtList + ''' files in ' + @Directory + '.'
EXEC ifs_RunHistoryInsertUpdate 'Excel Import No Files', @Note, 'Excel Import', 'Success', @GroupID = 12
END
/***
Loop until all files are loaded into sql tables, renamed with timestamp attached and then moved to backup directory
***/
WHILE @FileID > 0
BEGIN
SELECT @OldFileName = FileName
FROM #Files WHERE FileID = @FileID
SET @NoteFile = 'Excel File: "' + @OldFileName + '" at "' + @Directory + '". '
EXEC ifs_RunHistoryInsertV2 'Excel Import File', @NoteFile, 'Excel Import', @GroupID = 12
-- file name w/o extension
SET @FileNameWOExt = REVERSE(SUBSTRING(REVERSE(@OldFileName), CHARINDEX('.', REVERSE(@OldFileName)) + 1, 999))
-- file extension
SET @FileExtension = REVERSE(SUBSTRING(REVERSE(@OldFileName), 1, CHARINDEX('.', REVERSE(@OldFileName)) - 1))
-- See if there are any parameters embedded in the file name
SET @File_MIXEDTEXT = 0
SET @File_APPEND = 0
SET @File_RECREATE = 0
SET @File_NOHDR = 0
IF @FileNameWOExt like '%[_]APPEND' or @FileNameWOExt like '%[_]APPEND[_]%'
SET @File_APPEND = 1
IF @FileNameWOExt like '%[_]RECREATE' or @FileNameWOExt like '%[_]RECREATE[_]%'
SET @File_RECREATE = 1
IF @FileNameWOExt like '%[_]MIXEDTEXT' or @FileNameWOExt like '%[_]MIXEDTEXT[_]%'
SET @File_MIXEDTEXT = 1
IF @FileNameWOExt like '%[_]NOHDR' or @FileNameWOExt like '%[_]NOHDR[_]%'
SET @File_NOHDR = 1
-- create the SQL Tablename by removing the embedded parameters
SET @TableName = REPLACE(REPLACE(REPLACE(REPLACE(@FileNameWOExt, '_APPEND', ''), '_RECREATE', ''), '_MIXEDTEXT', ''), '_NOHDR', '')
/*****
Build the OPENROWSET to import data into SQL TAbles
++ HDR is optional and YES is the default
++ IMEX=1 is optional and says to use text data type if thre are conflicting types in the first n number of rows
++ N - is a setting in the registry - Key = TypeGuessRows, 0 means to scan all rows
Excel Files with _MIXEDTEXT (IMEX=1)
SELECT * INTO Excel_CMSTicketsalpha
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0; HDR=YES; IMEX=1;
Database=E:\H360\Import Excel\Excel_CMSTicketsalpha_RECREATE_MIXEDTEXT.xlsx', [Sheet1$]);
*****/
-- if tablename exists, should it be Truncated or Dropped and then INSERT INTO or SELECT * INTO
IF EXISTS (SELECT top 1 1 from sys.tables where name = @TableName)
BEGIN
IF @File_RECREATE = 1
BEGIN
SET @SQL = 'DROP TABLE [' + @TableName + ']'
exec sp_executeSQL @SQL
SET @SQL = N'SELECT * INTO [' + @TableName + N'] FROM '
END
ELSE
IF @File_APPEND = 1
BEGIN
SET @SQL = N'INSERT INTO [' + @TableName + N'] SELECT * FROM '
END
ELSE
BEGIN
SET @SQL = 'TRUNCATE TABLE [' + @TableName + ']'
exec sp_executeSQL @SQL
SET @SQL = N'INSERT INTO [' + @TableName + N'] SELECT * FROM '
END
END
ELSE
SET @SQL = N'SELECT * INTO [' + @TableName + N'] FROM '
SET @SQL = @SQL + N' OPENROWSET(''' + @ExcelDriver + ''', '''
SET @ExcelVersion = RTRIM(LTRIM(@ExcelVersion))
IF @FileExtension in ('csv','txt')
SET @SQL = @SQL + 'TEXT; '
ELSE
IF @FileExtension in ('xls')
SET @SQL = @SQL + @ExcelVersion + '; '
ELSE
IF @FileExtension in ('xlsx')
SET @SQL = @SQL + @ExcelVersion + ' Xml; '
SET @SQL = @SQL + CASE WHEN @File_NOHDR = 1 THEN N' HDR=NO; ' ELSE N' HDR=YES; ' END
SET @SQL = @SQL + CASE WHEN @File_MIXEDTEXT = 1 THEN N' IMEX=1; ' ELSE N'' END
SET @SQL = @SQL + N' Database=' + @Directory
IF @FileExtension in ('xlsx','xls')
SET @SQL = @SQL + @OldFileName + N''', [Sheet1$]);'
IF @FileExtension in ('csv','txt')
SET @SQL = @SQL + N''', ''SELECT * From [' + @OldFileName + ']'');'
Print @SQL
BEGIN TRY
exec sp_executesql @SQL
Set @RowCount = @@ROWCOUNT
SET @NoteFile = @NoteFile + ' SQL: ' + @SQL
EXEC ifs_RunHistoryUpdateV2 'Excel Import File',@NoteFile, 'Success', @RowCount
END TRY
BEGIN CATCH
SET @NoteFile = @NoteFile + ' Error: ' + Cast(Error_Number() as VARCHAR(10)) + ' ' + Error_Message() + '.' + ' SQL: ' + @SQL
EXEC ifs_RunHistoryUpdateV2 'Excel Import File',@NoteFile, 'Fail'
RAISERROR (@NoteFile, 20, 127) WITH LOG
RETURN 1
END CATCH
/***
Look at @FileAfterImport and either Move file to @DirectoryBackup, Delete file, or do nothing
***/
IF @FileAfterImport = 'M'
BEGIN
-- Rename file name to filename file_2018-05-30_17_12_49.xlsx
SELECT @NewFileName = @FileNameWOExt + '_' + @Timestamp + '.' + @FileExtension ,
@cmd = 'RENAME "' + @Directory + @OldFileName + '" "' + @NewFileName + '"'
exec @ReturnCode = util_CommandExecute @cmd
-- Move renamed file to backup director
SET @cmd = 'MOVE "' + @Directory + @NewFileName + '" "' + @DirectoryBackup + '"'
exec @ReturnCode = util_CommandExecute @cmd
END
ELSE IF @FileAfterImport = 'D'
BEGIN
-- Delete file name
SELECT @NewFileName = @FileNameWOExt + '_' + @Timestamp + '.' + @FileExtension ,
@cmd = 'DEL "' + @Directory + @OldFileName
exec @ReturnCode = util_CommandExecute @cmd
END
-- Delete file from temp table and get next file
Delete from #Files where Fileid = @FileID
SET @FileID = ISNULL((SELECT MAX(FileID) FROM #Files), 0)
END
If object_ID(N'tempdb.dbo.#xp_fileexist_output') is not NULL
drop table #xp_fileexist_output
IF OBJECT_ID('tempdb..#Files') IS NOT NULL
DROP TABLE #Files;
If object_ID(N'tempdb.dbo.#providers') is not NULL
drop table #providers
EXEC ifs_RunHistoryUpdateV2 'Excel Import STARTED', NULL, 'Success'
EXEC ifs_RunHistoryInsertUpdate 'Excel Import ENDED', 'Excel Import Ended.', 'Excel Import', @GroupID = 12
END
GO
/****** Object: StoredProcedure [dbo].[util_CommandExecute_2018_02] Script Date: 12/05/2018 12:01:34 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[util_CommandExecute_2018_02]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[util_CommandExecute_2018_02]
GO
CREATE Proc [dbo].[util_CommandExecute_2018_02]
/*************************************************************
Executes xp_cmdshell commands.
Example
======
exec util_commandExecute 'net start "IBM Cognos:9301"'
exec util_commandExecute 'net stop "IBM Cognos:9301"'
exec util_commandExecute 'SC \\ORLHZNXXXDV02 start "IBM Cognos:9372"'
*************************************************************/
-- cannot be max
@Command varchar(4000)
AS
BEGIN
DECLARE @xp_cmdshell_enabled int;
DECLARE @ShowAdvancedOptions_enabled int;
DECLARE @Note varchar(500)
DECLARE @ReturnCode int = 0
CREATE TABLE #temp (SomeCol VARCHAR(500))
SET @Note = 'util_CommandExecute: ' + @Command
Print @Note
EXEC ifs_RunHistoryInsertUpdate 'CommandExecute', @Note, 'CommandExecute', 'Success'
-- Get current xp_cmdshell and show advanced options values
SELECT @xp_cmdshell_enabled = CONVERT(int , ISNULL(value , value_in_use))
FROM master.sys.configurations
WHERE name = 'xp_cmdshell';
SELECT @ShowAdvancedOptions_enabled = CONVERT(int , ISNULL(value , value_in_use))
FROM master.sys.configurations
WHERE name = 'show advanced options';
-- Configure xp_cmdshell to enabled
EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE;
PRINT 'RECONFIGURE statement was run.'
EXEC sp_configure 'xp_cmdshell' , 1;
RECONFIGURE WITH OVERRIDE;
PRINT 'RECONFIGURE statement was run.'
-- Run the Command
BEGIN TRY
INSERT INTO #temp
Exec @ReturnCode = master.dbo.xp_cmdshell @Command
IF @ReturnCode <> 0
BEGIN
SET @ReturnCode = 1
SET @Note = ''
SELECT @Note = @Note + SomeCol
FROM #temp
WHERE SomeCol IS NOT NULL
SET @Note = 'util_CommandExecute error from xp_cmdshell ' + @Note + '; Command: ' + @Command
Print @Note
EXEC ifs_RunHistoryInsertUpdate 'CommandExecute', @Note, 'CommandExecute', 'Fail'
END
END TRY
BEGIN CATCH
SET @Note = 'util_CommandExecute Error: ' + Cast(Error_Number() as VARCHAR(10)) + ' ' + Error_Message() + '; Command: ' + @Command
Print @Note
EXEC ifs_RunHistoryInsertUpdate 'CommandExecute', @Note, 'CommandExecute', 'Fail'
SET @ReturnCode = 1
END CATCH
-- reset xp_cmdshell and show advanced options back to original value
IF @xp_cmdshell_enabled = 0
BEGIN
EXEC sp_configure 'xp_cmdshell' , 0;
RECONFIGURE WITH OVERRIDE;
PRINT 'RECONFIGURE statement was run.'
END;
IF @ShowAdvancedOptions_enabled = 0
BEGIN
EXEC sp_configure 'show advanced options' , 0;
RECONFIGURE;
PRINT 'RECONFIGURE statement was run.'
END;
DROP TABLE #temp
Return @ReturnCode
END
GO
Tuesday, January 7, 2020 3:32 PM
Thanks for sharing your code. Where can I find the following dependencies?
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertV2'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertV2'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryUpdateV2'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryUpdateV2'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'util_CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'util_CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'util_CommandExecute'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryUpdateV2'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_ExcelToSQLTable' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_CommandExecute_2018_02' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_CommandExecute_2018_02' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
The module 'util_CommandExecute_2018_02' depends on the missing object 'ifs_RunHistoryInsertUpdate'. The module will still be created; however, it cannot run successfully until the object exists.
Tuesday, January 7, 2020 5:22 PM | 1 vote
ifs_runhistoryInsertv2 and ifs_runhisotryinsertupdate are sprocs that write to our internal log table. just comment out those exe staements with --
change any reference to util_CommandExecute to util_CommandExecute_2018_02.
Tuesday, January 7, 2020 5:50 PM
Thanks for the wonderful procedure, TheBrenda! Everything is up and running now.