T-SQL – Read CSV files using OpenRowSet

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

Input file

ReadInput

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

driver

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:-

outpuDriver

 

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

 

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL, Uncategorized and tagged , , , , , , . Bookmark the permalink.

4 Responses to T-SQL – Read CSV files using OpenRowSet

  1. Anu says:

    How to add Microsoft access driver if it not there in your machine. Please tell.
    Thanks in advance
    Anu

  2. Anu says:

    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

  3. meteorquake says:

    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/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s