August 21, 2012 at 4:14 am
I have Unicode Data file which is Fixed Length
Following Table Structure with Create Statement.
sample Data file and format file i used attached in DataImport.rar
I tried to change format file with other possibilities But non of it inserts data perfactly.
So Please can any one tell me that what i am doing wrong?
is it wrong with format file or where?
CREATE TABLE [dbo].[Tab_Import]
(
[COLUMN1] [nvarchar] (4) COLLATE Danish_Norwegian_CI_AS NULL,
[COLUMN2] [nvarchar] (3) COLLATE Danish_Norwegian_CI_AS NULL,
[COLUMN3] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NULL
) ON [PRIMARY]
GO
BULK INSERT dbo.Tab_Import
FROM 'D:\DataImport\Tab_Import.Dat'
WITH (FORMATFILE = 'D:\DataImport\Tab_Import.Fmt') ;
August 21, 2012 at 5:18 am
This was removed by the editor as SPAM
August 21, 2012 at 5:21 am
This was removed by the editor as SPAM
August 21, 2012 at 5:24 am
Yes,
I tried that but did not work.
August 21, 2012 at 9:22 am
1. Your format file specifies #bytes, not characters. Since it is nvarchar, you need to double them in your format file.
2. Your row terminator is not "\r", it is just ""
Like this:
10.0
3
1 SQLNCHAR 0 8 "" 1 COLUMN1 Danish_Norwegian_CI_AS
2 SQLNCHAR 0 6 "" 2 COLUMN2 Danish_Norwegian_CI_AS
3 SQLNCHAR 0 100 "\" 3 COLUMN3 Danish_Norwegian_CI_AS
EDIT: of course, it will not show a backslash n, so make sure you add the n to the backslash.
Jared
CE - Microsoft
August 21, 2012 at 10:12 am
SQLKnowItAll
i have done now same as you said but i am getting error.
i used \ n as row terminator as specified for last row.
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 1, column 3 (COLUMN3).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 3 (COLUMN3).
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)".
August 21, 2012 at 10:24 am
I got Correct File how it is just i have to add \r\0\0
10.0
3
1SQLNCHAR 08""1 Column1 Danish_Norwegian_CI_AS
2SQLNCHAR 06""2 Column2 Danish_Norwegian_CI_AS
3SQLNCHAR 0100"\r\0\0"3Column3 Danish_Norwegian_CI_AS
EDIT: in 3rd Line it is \r\0\ n\0
SQLKnowItAll and
Stewart "Arturius" Campbell Thanks for help
August 22, 2012 at 2:20 am
I got Correct File how it is just i have to add \r\0\0
10.0
3
1SQLNCHAR 08""1 Column1 Danish_Norwegian_CI_AS
2SQLNCHAR 06""2 Column2 Danish_Norwegian_CI_AS
3SQLNCHAR 0100"\r\0\0"3Column3 Danish_Norwegian_CI_AS
August 23, 2012 at 1:25 am
Hi,
I have worked some with translation from Unix to PC encoding.
The main problem to BULK INSERT is that it has problems reading Unicode text documents as it does not have "CR" (Carrige Return) at the end of a row, only "LF".
By adding (there are some apps doing this, mainly on the UNIX side) a "CR" to the end of every line will solve the problem.
Also open the txt file in TextPad or NotePad++ and saving it in PC-format will do the trick.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply