SQLTeam.com | Weblogs | Forums

Import txt file error

sql2008

#1

Hi,
I Have an txt file as below :
date trans_id
4/21/2015 0:00,ANI.017.SC14111019.2D
4/20/2015 0:00,ANI.059.DC15011102.4B
4/20/2015 0:00,ANI.065.DC15011104.1B
4/21/2015 0:00,ANN.004.SL15022404.2B
4/21/2015 0:00,ANN.005.DC15040401.1B

from the data I want to import into my table with same field name (date, trans_id).
I try import the data with the task menu, import data and it is work.
Next way I want to import it by sql syntax such as :

SET IDENTITY_INSERT bcode ON
BULK INSERT bcode FROM 'd:\bcode\barcodewhr2.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR ='\r\n') ;
SET IDENTITY_INSERT bcode OFF

but there's an error said " Table 'bcode' does not have the identity property. Cannot perform SET operation."
the table destination don't have a primary key it just temporary table.

Please help..

Thx


#2

If your target able does not have a column with the IDENTITY property then you don't need (and cannot use) SET IDENTITY_INSERT.

Either add an IDENTITY column to [bcode] (if you need one), or just leave out both the SET IDENTITY_INSERT commands


#3

if the identity set were remove there is an error message :
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

so what is teh best solution to import txt file with sql syntax?

thx


#4

if the columns (both the number of columns, and the ordering of them) do not EXACTLY match your table then you need to use a FORMATFILE (i.e. to list the columns, in the order that they appear in your Import File)

You might be able to make a VIEW which matches the column order, and import into the View instead of the Table, if that is easier (although a FORMATFILE lets you do other things, e.g. specifying the Collation)


#5

I am assuming that this is because the number of columns in the import file is not the same as the table, but it might be that there is an extra line-break at the end of the file, or there is no line break there (although I don't think that matters)


#6

it's work...thanks a lot