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
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! 🙂