T-SQL – Read CSV files using OpenRowSet

We can directly access a CSV file using T-SQL.

Input file

Configure server to run Ad Hoc Distributed Queries

sp_configure 'Ad Hoc Distributed Queries',1
GO
reconfigure
GO

Check for Microsoft Access Driver on your system

SQL

select 
[hour],
UserID,
[ReportLaunch]
from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
,'select * from C:\blog\input.CSV')

OR

select
[hour],
UserID,
[ReportLaunch]
from openrowset('MSDASQL'
,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
DBQ=C:\blog\' 
,'select * from "input.CSV"') T

OR

select
[hour],
UserID,
[ReportLaunch]
from openrowset('MSDASQL'
 ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; 
 DefaultDir=C:\blog\' 
 ,'select * from "input.CSV"') T

Output:-

 

Reference Link

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ded961b1-f386-4780-91e7-0527271032ec/convert-data-into-xml-format?forum=transactsql#c8d8a2a6-42a7-496a-abdd-31442e4bb598