How can we from SQL Server read Excel / CSV / TXT and insert the records into a table
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
SQL Server enables us to read and import files at will without the use of SSIS (Integration Services) and Wizard packages. This process is done using the function OPENROWSET and his BULK INSERT. We can call with a direct T-SQL query or insert the statement into an agent job.
Thanks to these functions we can even insert the records directly into an existing table.
The pre-requisites for using the ACE OLEDB engine
We should install ACE Engine (available here for download). If a compatible provider is already installed, e.g. Microsoft.ACE.OLEDB.12.0 then we don't need to install another one.
To see the installed providers, run the following:
exec master.dbo.sp_MSset_oledb_prop
We also need to configure the use of providers in the SQL Server instance by running the following script:
/* sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ad Hoc Distributed Queries', 1; GO RECONFIGURE; GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0′, N'AllowInProcess', 1 GO EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0′, N'DynamicParameters', 1 GO --Προσοχή το script ανάλογος ποιο ACE engine διαλέξουμε βάζουμε το αντίστοιχο π.χ. Microsoft.ACE.OLEDB.12.0, Microsoft.ACE.OLEDB.16.0 κλπ. */
How we read through an Excel file
All we need to do is to run the following query after first putting the version corresponding to the one we have installed, e.g. 'Microsoft.ACE.OLEDB.12.0', select as Database the direct path for the file we want, e.g. 'C:\Users\smatzouranis\Desktop\test.xlsx' and finally define the name of the Sheet we will read:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Users\smatzouranis\Desktop\test.xlsx','select * from [Sheet1$]')
When we run it, it will show us the result directly in the grid:
How do we insert the records from an Excel file into a table
But we can not only see the result but also save it, so let's make a table that fits the data we have:
We make a table with two fields for text:
CREATE table test( onoma varchar(50), epitheto varchar(50))
In this example we will make it a little more complicated as we will read from a folder that will have today's date, so the path of the folder that will be defined in a parameter with OPENROWSET it will change dynamically per day.
To achieve this we will use the function getdate() in his query OPENROWSET, which we'll pass in a parameter, then we'll do insert into in the table we made before by running this parameter:
set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000);
print @dbfile
set @dbfile = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Excel 12.0;Database=C:\Users\smatzouranis\outlook_files\'+convert(varchar,getdate(),112)+'\test_'+convert(varchar,getdate(),112)+'.xlsx'''+','+'''select * from [Sheet1$]'''+')'
print @dbfile
insert into dbo.test
exec(@dbfile)
go
(3 rows affected)
So now in the test table there are the entries:
Let's see what we do in case we don't have an Excel file but a flatfile CSV / TXT.
How we read through a CSV / TXT file
The process is similar with the difference that we declare the name of the file in the path but at the end instead of the name of the Sheet. As a delimiter character to separate each column must to be the comma ',' . There is the parameter HDR which states that in the first line we have the title of the field so whether or not to ignore the first line, in our case it has a title so we put HDR=YES:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Text;Database=C:\Users\smatzouranis\Desktop\;HDR=YES','select * from [test.csv]')
How to insert records from a CSV / TXT file into a table
Accordingly, we can insert the entries into a table as we saw in the previous example:
set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000);
print @dbfile
set @dbfile = 'SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Text;Database=C:\Users\smatzouranis\outlook_files\'+convert(varchar,getdate(),112)+'\;HDR=YES'''+','+'''select * from [test.csv]'''+')'
print @dbfile
insert into dbo.test
exec(@dbfile)
go
(3 rows affected)
What do we do in the event that the CSV / TXT file is not comma delimited but has a custom delimiter or encoding
In this case we do not change anything in our query but in the folder containing the files we should create a file named schema.ini, in this file we define the exact name of the file, if in the first line it has the title of each field and the custom delimiter it has as in the following example with pipe delimiter:
[test.csv]
ColNameHeader=False
Format=Delimited(|)
MaxScanRows=0
CharacterSet=65001
Col1="Col1" text
Col2="Col2" textHow to insert records from CSV / TXT with BULK INSERT
In case the file we want to import is a file that contains over 255 columns or in case we don't want to use the configuration file schema.ini then the only way to enter it is with BULK INSERT.
A common error in files with more than 255 columns is the following:
This operation will fail because the text file you are about to import contain more than 255 columnsIn this case as opposed to using it Microsoft.ACE.OLEDB we avoid this limitation, we do not need a configuration file but inside the SQL statement we declare the delimiter and the codepage:
BULK INSERT dbo.test FROM 'C:\Users\smatzouranis\Desktop\test.csv' WITH (FIRSTROW=1,FIELDTERMINATOR = '|',ROWTERMINATOR = '\n', CODEPAGE = 65001);
How can we keep the current date when inserting the table
Let's see one more example, we can when we enter the records have today's date and time.
We delete the table and rebuild it with one more datetime field:
drop table test; CREATE table test( onoma varchar(50), epitheto varchar(50), imerominia datetime)
The only difference is that we have added to select after * the function getdate():
set ANSI_NULLS ON
GO
set QUOTED_IDENTIFIER ON
GO
declare @dbfile varchar(2000);
print @dbfile
set @dbfile = 'SELECT *,getdate() FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'''+','+'''Text;Database=C:\Users\smatzouranis\outlook_files\'+convert(varchar,getdate(),112)+'\;HDR=YES'''+','+'''select * from [test.csv]'''+')'
print @dbfile
insert into dbo.test
exec(@dbfile)
go
Bonus automatic creation of commands for OPENROWSET / BULK INSERT
If we make and fill an auxiliary table that will contain the connection between the tables we want to fill and the files, we can generate all the commands we will need such as:
- The selection of files
- The insert in tables with openrowset
- The insert in tables with bulk insert
- The creation of schema.ini
- The truncate of tables
- Deleting tables
- But even update a column in our helper table with the count of records after the insert
To do this we create the auxiliary table as below and insert the tables with the files we will load:
CREATE TABLE [dbo].[filenames](
[name] [varchar](100) NULL,
[table_name] [varchar](100) NULL,
[count] [numeric](20, 0) NULL,
CONSTRAINT [ak_name] UNIQUE NONCLUSTERED
(
[name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
--insert into filenames values ('customers_file','customers_table',null);
--insert into filenames values ('countries_file','countries_table',null);
And then by running the following query the commands will be generated:
select
(name+'.csv') as real_fname
,('SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=C:\demo\;'',''select * from [' +name+'.csv]'');') as select_st
,('insert into dbo.' +fn.table_name +' SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Text;Database=C:\demo\;'',''select * from [' +name+'.csv]'');') as insert_st
,('BULK INSERT dbo.' +fn.table_name +' FROM ''C:\demo\' +name+'.csv'' WITH (FIELDTERMINATOR = ''|'',ROWTERMINATOR = ''\n'', CODEPAGE = 65001);') as bulk_insert_st
,('update dbo.filenames set count=(select count(*) from '+fn.table_name+ ') where table_name='''+fn.table_name+''';') as count_st
,(REPLACE('['+name+'.csv]'+char(13)+char(10)+'ColNameHeader=false Format=Delimited(|) MaxScanRows=0 TextDelimiter=none CharacterSet=65001 '+CL.COL_TYPE+char(13)+char(10)+char(13),' ',char(13)+char(10))) as schema_ini
,('truncate table dbo.'+fn.table_name+';') as truncate_st
,('drop table dbo.'+fn.table_name+';') as drop_st
from filenames fn
OUTER APPLY
(select STRING_AGG(CONVERT(NVARCHAR(MAX),('Col'+cast(ORDINAL_POSITION as varchar(3))+'="Col'+cast(ORDINAL_POSITION as varchar(3))+'" text')),CHAR(13)) col_type from INFORMATION_SCHEMA.COLUMNS sc WHERE sc.table_name=fn.table_name ) cl
where 1=1
