RE: Bulk insert from file having varying number of columns

SSC Guru

Points: 323506

November 12, 2013 at 6:35 am

Dorthy Jeff Moden has a couple of posts on this, which i'd saved in the past:

here's the specific threads in question:

http://www.sqlservercentral.com/search/?q=%22Headerless+Ragged+Right%22

basically, he uses some dos commands to make sure the file has headers if it didn't already, and then uses a text-file linked server which automatically treats the files as having NULLS for missing columns(which in turn make the columns for the import somewhere else.)

those old posts refer to 32 bit linked servers usign the JET driver, so if you need a 64 bit text based linked server, it's like this example:

select * from OpenRowset('MSDASQL',

'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Data\;'

,'select top 10 * from C:\Data\MailItems.txt')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!