How to import one column using OPENROWSET ?

Question

Post reply

How to import one column using OPENROWSET ?

SQL Kidu

Ten Centuries

Points: 1296
More actions
September 24, 2010 at 4:14 am

#224304

I have a text file with no column headers in a network location. filename is clients.txt.
it has 3 columns. I want to import the first column to a table in sql server ( Instance name is ATLANTIS ) . How can I do this ?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 1

Use BCP/BULK INSET using "FORMAT FILE" or as u said, using OPENROWSET..

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 2

Thanks...

But how can one column handled using OPENROWSET ? Could you please give the command ?

Vinod Suthar Old Hand Points: 322 More actions · Answer 3

I think It can be done via small trick.

Just add Column heading in your text file dynamically using DOS Copy file command and then mention column name you need to import in OpenRowSet command.

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 4

1. Create a format file using the command mentioned in the following step:

bcp Scratch.dbo.Names format nul -T -c -f Client.Fmt

Things to note here is, i have used -c switch, which makes all the columns into CHAR data-type.

I have attached a format files, both CHAR and NATIVE data-types for your convenience in this thread.

2. Now use the below query to get all the columns from the Client.txt file ; u can change the a.* into the column names u specified in the format file to control the columns u want to BULK INSERT

SELECT a.*

FROM OPENROWSET

(

BULK 'E:\Pras\SQL\Client.txt',

FORMATFILE = 'E:\Pras\SQL\Client_c.fmt'

) AS a;

U can type-cast the columns to match your destination data-type.

For BCP-out of your Client.txt, you can utilize the same Client.fmt format file..

Hope this helps you.

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 5

Hi ...

Thanks... 🙂

The first line worked. But when I try the OPENROWSET command, it is giving an error

Msg 4832, Level 16, State 1, Line 1

Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

What could be the problem ?

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 6

Are u using the correct path of the txt file? is your txt file well-formed? can u attach your input file?

SQL Kidu Ten Centuries Points: 1296 More actions · Answer 7

Hi ,

Thanks for the reply. I am attaching the zip file.

Regards,

Sanuj.S.S

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 8

Look, your City.txt file is a comma-separated file. Thus a delimiter of "\t" ( = Tab) will not do any good for you.. so i edited the format file to match your data, now i am able to extract information.. i am attaching the latest format file which will work with your City.txt file.. Note that your third column is a BIT flag i suppose, but i have used SQLCHAR for that as well..so use CAST/CONVERT in your SELECT query...