{"id":1175,"date":"2023-09-18T07:00:00","date_gmt":"2023-09-18T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1175"},"modified":"2024-02-04T21:47:57","modified_gmt":"2024-02-04T18:47:57","slug":"pos-mporoyme-mesa-apo-ton-microsoft-sql-server-na-diava","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-mesa-apo-ton-microsoft-sql-server-na-diava\/","title":{"rendered":"How can we from SQL Server read Excel \/ CSV \/ TXT and insert the records into a table"},"content":{"rendered":"

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<\/strong> and his BULK INSERT<\/strong>. We can call with a direct T-SQL query or insert the statement into an agent job.<\/p>\n\n\n\n

Thanks to these functions we can even insert the records directly into an existing table. <\/p>\n\n\n\n

The pre-requisites for using the ACE OLEDB engine<\/h2>\n\n\n\n

We should install ACE Engine (available here for download<\/a>). If a compatible provider is already installed, e.g. Microsoft.ACE.OLEDB.12.0<\/em> then we don't need to install another one.<\/p>\n\n\n\n

To see the installed providers, run the following:<\/p>\n\n\n\n

exec master.dbo.sp_MSset_oledb_prop<\/pre>\n\n\n\n
We also need to configure the use of providers in the SQL Server instance by running the following script:<\/p>\n\n\n\n
\/*\nsp_configure 'show advanced options', 1;\nGO\nRECONFIGURE;\nGO\nsp_configure 'Ad Hoc Distributed Queries', 1;\nGO\nRECONFIGURE;\nGO\nEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0\u2032, N'AllowInProcess', 1\nGO\nEXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0\u2032, N'DynamicParameters', 1\nGO\n\n--\u03a0\u03c1\u03bf\u03c3\u03bf\u03c7\u03ae \u03c4\u03bf script \u03b1\u03bd\u03ac\u03bb\u03bf\u03b3\u03bf\u03c2 \u03c0\u03bf\u03b9\u03bf ACE engine \u03b4\u03b9\u03b1\u03bb\u03ad\u03be\u03bf\u03c5\u03bc\u03b5 \u03b2\u03ac\u03b6\u03bf\u03c5\u03bc\u03b5 \u03c4\u03bf \u03b1\u03bd\u03c4\u03af\u03c3\u03c4\u03bf\u03b9\u03c7\u03bf \u03c0.\u03c7. Microsoft.ACE.OLEDB.12.0, Microsoft.ACE.OLEDB.16.0 \u03ba\u03bb\u03c0.\n*\/<\/pre>\n\n\n\n
<\/p>\n\n\n\n
How we read through an Excel file<\/h2>\n\n\n\n
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'<\/em>, select as Database the direct path for the file we want, e.g. 'C:\\Users\\smatzouranis\\Desktop\\test.xlsx'<\/em> and finally define the name of the Sheet we will read:<\/p>\n\n\n\n
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\\Users\\smatzouranis\\Desktop\\test.xlsx','select * from [Sheet1$]')<\/pre>\n\n\n\n
When we run it, it will show us the result directly in the grid:<\/p>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
How do we insert the records from an Excel file into a table<\/h2>\n\n\n\n
But we can not only see the result but also save it, so let's make a table that fits the data we have:<\/p>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
We make a table with two fields for text:<\/p>\n\n\n\n
CREATE table test(\nonoma varchar(50),\nepitheto varchar(50))<\/pre>\n\n\n\n
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<\/strong> it will change dynamically per day.<\/p>\n\n\n\n
To achieve this we will use the function getdate()<\/strong> in his query OPENROWSET<\/strong>, which we'll pass in a parameter, then we'll do insert into<\/strong> in the table we made before by running this parameter:<\/p>\n\n\n\n
set ANSI_NULLS ON\nGO\nset QUOTED_IDENTIFIER ON\nGO\ndeclare @dbfile varchar(2000);\nprint @dbfile\nset @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$]'''+')'\n\nprint @dbfile\n\ninsert into dbo.test\nexec(@dbfile)\n\ngo<\/pre>\n\n\n\n
(3 rows affected)<\/p>\n\n\n\n
So now in the test table there are the entries:<\/p>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
Let's see what we do in case we don't have an Excel file but a flatfile CSV<\/strong> \/ TXT<\/strong>.<\/p>\n\n\n\n
How we read through a CSV \/ TXT file<\/h2>\n\n\n\n
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 <\/strong> to be the comma<\/strong> ',' . There is the parameter HDR <\/strong>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:<\/p>\n\n\n\n
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Text;Database=C:\\Users\\smatzouranis\\Desktop\\;HDR=YES','select * from [test.csv]')<\/pre>\n\n\n\n
How to insert records from a CSV \/ TXT file into a table<\/h2>\n\n\n\n
Accordingly, we can insert the entries into a table as we saw in the previous example:<\/p>\n\n\n\n
set ANSI_NULLS ON\nGO\nset QUOTED_IDENTIFIER ON\nGO\ndeclare @dbfile varchar(2000);\nprint @dbfile\n\nset @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]'''+')'\n\nprint @dbfile\n\ninsert into dbo.test\nexec(@dbfile)\ngo<\/pre>\n\n\n\n
(3 rows affected)<\/p>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
What do we do in the case that the CSV \/ TXT file is not comma delimited but has a custom delimiter or encoding<\/h2>\n\n\n\n
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<\/strong>, 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:<\/p>\n\n\n\n
[test.csv]\nColNameHeader=False\nFormat=Delimited(|)\nMaxScanRows=0\nCharacterSet=65001\nCol1=\"Col1\" text\nCol2=\"Col2\" text<\/code><\/pre>\n\n\n\n
How to insert records from CSV \/ TXT with BULK INSERT<\/h2>\n\n\n\n
In case the file we want to import is a file that contains over 255 columns<\/strong> 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<\/strong>.<\/p>\n\n\n\n
A common error in files with more than 255 columns is the following:<\/em><\/p>\n\n\n\n
This operation will fail because the text file you are about to import contain more than 255 columns<\/code><\/pre>\n\n\n\n
In this case as opposed to using it Microsoft.ACE.OLEDB<\/em> we avoid this limitation, we do not need a configuration file but inside the SQL statement we declare the delimiter and the codepage:<\/p>\n\n\n\n
BULK INSERT dbo.test FROM 'C:\\Users\\smatzouranis\\Desktop\\test.csv' WITH (FIRSTROW=1,FIELDTERMINATOR = '|',ROWTERMINATOR = '\\n', CODEPAGE = 65001);\n<\/pre>\n\n\n\n
How can we keep the current date when inserting the table<\/h2>\n\n\n\n
Let's see one more example, we can when we enter the records have today's date and time.<\/p>\n\n\n\n
We delete the table and rebuild it with one more datetime field:<\/p>\n\n\n\n
drop table test;\n\nCREATE table test(\nonoma varchar(50),\nepitheto varchar(50),\nimerominia datetime)<\/pre>\n\n\n\n
The only difference is that we have added to select after * the function getdate()<\/strong>:<\/p>\n\n\n\n
set ANSI_NULLS ON\nGO\nset QUOTED_IDENTIFIER ON\nGO\ndeclare @dbfile varchar(2000);\nprint @dbfile\n\nset @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]'''+')'\n\nprint @dbfile\n\ninsert into dbo.test\n\nexec(@dbfile)\n\ngo<\/pre>\n\n\n\n
\"\"
05<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
Bonus automatic creation of commands for OPENROWSET \/ BULK INSERT<\/h2>\n\n\n\n
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:<\/p>\n\n\n\n