Import tab delimited data files directly into SQL Server via SSMS

Recently, I wanted to bring data into a SQL Server table, and I wanted to do so w/o SSIS. I know that I had done this before, but, of course, I had forgotten the details. I found the best answer here:

The solution requires putting a “Schema.ini” file in the same directory as the file you are attempting to upload, and the file HAS TO BE NAMED “schema.ini” (case insensitive). The contents of the simple text file should be as follows:

[PutYourFileNameHere.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
The “PutYourFileNameHere.txt” is the name of the file that has your data. If you have done the above…then a command like the following will bring in your data very nicely:

SELECT * FROM OPENROWSET (‘msdasql’, ‘Driver={Microsoft Access Text Driver (*.txt, *.csv)}’,
‘Select * from d:\YourDataDirectory\PutYourFileNameHere.txt order by Date’)

If you have multiple files to upload from the same directory… you can put multiple file names in the schema.ini file, as shown below:

[PutYourFileNameHere.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
[PutYour2ndFileNameHere.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0
There is another way as well, that involves changing a registry key…change the registry key Format under HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Text from CSVDelimited to TabDelimited on the SQL Server. But, I have not implemented that, nor tested it. I mention it incase you find the above solution not to you liking. Always be VERY careful changing the registry!

Happy SQLing! 🙂

 

About Steve Schneider

I was born (again) in June of 1983 when I came to know Jesus Christ as my saviour. You too, can know Him, and know Peace. We all have sinned, and fallen short of the grace of God. But, God demonstrates His love for us in this, that while we were still sinners, He died for us. Email me, and I can show you how to know Jesus. He is highly worth knowing!
This entry was posted in SQL 2000, SQL 2005, SQL 2008, SQL 2012, SQL Administration, SSIS and tagged , , . Bookmark the permalink.

Leave a Reply...Really! I'll listen!