CSV or TXT file as a database !

 

Do you have a large CSV ( or delimited txt ) file which you cannot open in XL sheet, because of XL's 65K row limit ? Because you are unable to load you are unable to data analysis - here is what I did.

 

As a SQL Server DBA/Developer I am assuming you have SQL Server installed on your PC or at least a instance is available for you with Sys. Admin  previlages on it. Connect to the instance using query analyzer and create a linked server. Use following command..

 

EXEC master.dbo.sp_addlinkedserver @server = N'txtsrv', @srvproduct=N'Jet 4.0', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'C:\My_CSV_Files\JetDatabase', @provstr=N'Text'

 

Lets look at each of the parameters specified in above command. @server is pointing to txtsrv i.e. "use text files as database", then use Jet 4.0 as a provider, @datasrv - is the data source name. In this case it is a folder path where my text/CSV files are located, that I want to access or query. Once you add the linked server successfully, try the following command.

 

EXEC sp_tables_ex txtsrv

The output will show you the list of table(s) available for querying. These tables are nothing but the files that are located in the folder "C:\My_CSV_Files\JetDatabase". In my case the output shows following tables Server_A01#txt, Server_A02#txt. Now lets try querying the data in those files. Try the following command..

 

select TOP 10 * from txtsrv...Server_A01#txt

 

Wow, I see the output as if its a real table from my SQL Server Engine. You can run all the sorts of queries on this data(file data) like you do with real table. The performance looks somewhat slow but for adhoc data analysis and processing purpose this is just great.