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
Like this:
Like Loading...
About Prashanth Jayaram
DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek
My Profile:
https://social.technet.microsoft.com/profile/prashanth jayaram/
http://www.sqlshack.com/author/prashanth/
http://codingsight.com/author/prashanthjayaram/
https://www.red-gate.com/simple-talk/author/prashanthjayaram/
http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/
Connect Me:
Twitter @prashantjayaram
GMAIL powershellsql@gmail.com
The articles are published in:
http://www.ssas-info.com/analysis-services-articles/
http://db-pub.com/
http://www.sswug.org/sswugresearch/community/
How to add Microsoft access driver if it not there in your machine. Please tell.
Thanks in advance
Anu
LikeLike
Hi Anu,
Try downloading Microsoft Access Database Engine 2010 redistributable package and install it
–Prashanth
LikeLike
Hi Prashanth,
Thanks for the response. I have tried running the query but I am getting the following error.
The OLE DB provider “MSDASQL” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 6
Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “(null)”.
Thanks in advance
Anu
LikeLike
I had no MSDASQL but it’s not possible to install MSOffice 64 bit when there’s a 32 bit already on the system.
There’s a good method of CSV import here which works very nicely (note the updated version) –
https://sqlmate.wordpress.com/2012/08/09/use-your-text-csv-files-in-your-queries-via-openrowset/
LikeLiked by 1 person