BULK INSERT unix file
-
Hallo,
I have the fallowing problem:
I have UNIX .dat expression file how I inserted to table in sql server 2000 with a DTS pack.
Well.. I want now to do the same but with a store procedure. In the store procedure I use BULK INSERT as follow:
BULK INSERT
test.dbo.tbunix
FROM 'C:\test\unix.dat'
WITH
(
CODEPAGE = '52',
DATAFILETYPE = 'widenative',
FIELDTERMINATOR = '',
ROWTERMINATOR = '\r'
)
But I get the following error:
Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.
De situation in de DTS is as follow:
Txt file properties:
File Type: ANSI
Row Delimiter: {LF}
Column Delimiter: others: ''
How can help my with this problem? are there any equals to ''
Thx..
-
I'm guessing that your column delimiter isn't actually ''. Open your dat file with a hex editor and find out the actual column delimiter's value.
-
Dear David,
I used two Hex editor tools: (xvi32 and HxDBetafi) both telling my that '' is the actual column delimiter's. do you recommending any another tools?
Thx..
-
Cees, you need to use format file.
The way you did it you specified rowterminator as CR+LF - Windows "End of Line".
Unix files use only LF to end lines.
That's why for BULK INSERT whole file is one row, and this row is too long.
But if you use format files "\r" indicates only LF and "\n" indicates CR.
So, you can process UNIX files without any problems.
Read in BOL how to create and use format files with BULK INSERT.
_____________
Code for TallyGenerator -
What was the hex value that you found, Cees?
-
David,
Iam not sure what je mean but I get by each ' ' 07 as value.
-
Dear Sergiy,
it look like it work.. i mean i don't get any error anymore but i get "(0 row(s) affected)".
see my change:
BULK INSERT
test.dbo.tbunix
FROM 'C:\test\unix.dat'
WITH
(
CODEPAGE = '52',
DATAFILETYPE = 'widenative',
FIELDTERMINATOR = '',
ROWTERMINATOR = '\n\r'
)
I wondering why not any row has been affected?
-
Cees,
stop messing with direct declarations.
It's not gonna work.
You gonna need to create format file anyway. No matter how hard you'll try to avoid this.
Yes, it's painful task, but without it you cannot import UNIX files.
_____________
Code for TallyGenerator -
If it really has Char(07) (which is the Bell non-printable character) as the delimiter, I'm not sure if even a format file is going to work. It's definitely worth a shot, as BOL has been wrong before, but according to BOL, only the \t, \n, \r, \\, and \0 control characters are acceptable. If it doesn't work with that, there are possibilities with pre-processing of the .dat file, such as Search/Replace on the Bell control character. Just in case, the control character used in C on the Unix side is an \a for that.
Let us know how it pans out.
-
Because you said the file comes from unix I would try changing:
ROWTERMINATOR = '\n\r'
)
to
ROWTERMINATOR = '\n'
)
* Noel -
Noel, check BOL.
"\n" means different things when used in BULK INSERT statement and in Format File.
And only interpretation of Format File works for UNIX files.
_____________
Code for TallyGenerator -
Dear Noeld,
Yes, I did. But I get the same result '(0 row(s) affected)'
-
I tread to made a file format by using the fallowing.. Command:
bcp c:\or\test.dat format nul –T –n –f c:\ds\test.fmt
I get de falowing error:
SQLState = 37000, NativeEror = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login 'c:\or\test.dat'. Login fails.
After searching for the reason it seems the bcp utility can’t be used to create a format file of a .dat file, it most use table of view from the database. En that is not possible in my case. Because I get de .dat files throw an ftp. Anyway are there any possibilities or another way’s to create a format file of the .dat file?
-
Why don't just type the format file in Notepad?
BOL describes it quite explicitly, and your file should not be too complex.
_____________
Code for TallyGenerator -
How are you getting the file frm the Unix server to your SQL Server ? The reason I ask is that may be the solution to your issue. I have seen processes modified such as you are doing in order to make things work when in fact no modification but the data transfer type may be needed. As an example if your file was ftp'd using as binary instead of text this could be the cause of all of your woes. It is worth a look.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply