OpenRowSet uses first row as field name

[gmmastros]

I am trying to import a flat file (fixed width fields) in to a SQL Server table. Since I need to import many files, and each file can be quite large, I want a fast way to do this.

My plan was this... I'll create an SP that imports the data in to a temp table. Then parse the individual data elements using the SubString function. Then, finally put the data where it belongs.

When I issue the following command...

Select *
From OPENROWSET('MSDASQL.1',
'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\MyFolder;',
'Select * from MyFile.txt')

I can see all my data, but the column header (field name) is the data that is in the first row of the data file.

My question is this.... How can I get the first row of the data file to import as data, not a field name?

 

[SQLSister]

Try using DTS instead.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Glasgow]

Or use BULK INSERT - it would require a single line of SQL. Use FIRSTROW=2.

 

[gmmastros]

My gut tells me that DTS would somehow be better than the Bulk Insert method. Unfortunately, I've never used DTS. I did spend a couple minutes with it, but was unable to get it to work for me.

It wouldn't be fair of me to ask SQLSister to walk me through the process of using DTS, so I won't. I will research this later and probably find about a million uses for it.

The Bulk Insert method will work.

Are there advantages to DTS that I am unaware of?
Should I make it a priority to look in to it sooner?

 

[SQLSister]

Bulk insert is fast but not as flexible as DTS. Of course there is a bulk insert task in DTS, so you can combine the speed of bulk insert with the ability to manipulate data in DTS. DTS is a complax subject and I suggest getting a book if you are going to do any complex data conversions.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Glasgow]

Well I use BULK INSERT a lot and without problems but I guess it would depend upon the reliability of the text file content and what sort of validation or error handling you may wish to implement. You have no delimiters and I'm not sure therefore if you can get BULK INSERT to parse for you - perhaps using a format file but you can still use your SUBSTRING solution after BULK INSERTing into a one field per row table.

I'm not aware of any disadvantages of using BULK INSERT for this kind of problem nor how it performs in comparison with DTS but I'd expect BULK INSERT to be faster.