build sql script: unix

Showing posts with label unix. Show all posts

Tuesday, March 20, 2012

BULK INSERT PROBLEM

I am trying to BULK INSERT a unix data file (.csv) into temperary table, then replace all CHAR(13) into CHAR(10) or '\n'. In Classic ASP, I was using Replace(text,char(13),vbcrlf), but now in MS SQL, so I use Replace(@.text,char(13),'\n').
After finished converting, I will then BULK INSERT into a load_table with rowterminator, firstrow and fieldterminator. But, I got this 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.

==============CODE========================

DECLARE @.sLoadFileLayout AS NVARCHAR(1000)
DECLARE @.SQL AS NVARCHAR(1000)
DECLARE @.sFieldName AS NVARCHAR(1000)
DECLARE @.iRow AS INT
DECLARE @.TextLine AS NVARCHAR(4000)
DECLARE @.fieldTerminator AS char(3)

SET @.sLoadFileLayout = 'C:/folder/file.CSV'

CREATE TABLE #textfile (line varchar(8000))
SET @.SQL = 'BULK INSERT #textfile FROM ''' + @.sLoadFileLayout + ''''
EXEC sp_executesql @.SQL

DECLARE TableMap CURSOR FOR SELECT * FROM #textfile
OPEN TableMap
FETCH NEXT FROM TableMap INTO @.TextLine

SET @.iRow = 0
SET @.SQL = ''
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.iRow = @.iRow + 1
IF @.iRow >= 1
BEGIN
IF @.SQL <> '' SET @.SQL = @.SQL + ','
SET @.sFieldName = REPLACE(@.TextLine, char(10),'\n')
SET @.SQL = @.SQL + ' ' + @.sFieldName
END
FETCH NEXT FROM TableMap INTO @.TextLine
END

CLOSE TableMap
DEALLOCATE TableMap

DROP TABLE #textfile

BULK INSERT MARKETING.DBO.LOAD_Table FROM #textfile WITH (DATAFILETYPE='char',FIELDTERMINATOR=',',FIRSTROW=4,ROWTERMINATOR='\n')

Strange. I tested your script and got the same rows in table #textfile and Load_Table. Seems your code works fine Smile I'm using SQL2000, here is the content of the csv file:

One user can only have one user instance.
Replication will be disabled.
User Instance does not support SQL Server Authentication. Only Windows Authentication is supported.
The network protocol support for user instances is local named pipes only.
The user instance shares the registry entries of the parent instance.
There is no support for user instance with native code. This feature is only supported with ADO .NET.
WMI Provider for Server Events will not be supported on the dynamically spawned user instances. This should still work on the parent SQL Server Express instance. For more information on WMI provider see WMI Provider for Server Events in SQL Server 2005 Books Online.

BTW, I think you do not need to replace char(10) or char(13) with '\n', because SQL does accept carriage return.

Thursday, March 8, 2012

Bulk Insert fails to import data files created on Unix

It seems to me that files created on Unix machines with line terminator \n, or chr(10), cannot be imported using the Bulk Insert statement. Is this a bug, or an oversight by Microsoft? Does this mean that unless one replaces all \n with \r\n, there is no way to use Bulk Insert to import Unix files? This is a very strange behavior by MSSQL. Even lessor programs such as Excel and Word automatically recognize chr(10) as a line termination character. Am I missing something, or is this just the way MSSQL is?

You will need to use a format file, in this you can specify the terminator for the last column in a row.

Have a look in BOL. This page shows an example of a file using /r/n which you can obviously change

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ecfc546d-f708-45f4-878d-fb71b5fd1a0a.htm

|||

Of if you are using the BULK INSERT TSQL statement look at this page

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/be3984e1-5ab3-4226-a539-a9f58e1e01e2.htm

|||I figured out that the problem has to do with MSSQL's native behavior. It turns out that whenever it sees \n, it automatically converts it to \r\n, without notifiying the user. There are at least three ways to work around this strange behavior: 1) replace every \n in your file with \r\n before using Bulk Insert, 2) build your sql statement dynamically either in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +, instead of '\n' as the line terminator in your statement, and 3) load your file into a Datatable via ADO.net, and then insert the entire Datatable into MSSQL.|||

Note sure what figuring out was required, as BOL has an example that just works.

|||

I have never succeeded to convince BULK INSERT to read Unix

files, and I find one of these solutions usually works:

1. See if the process that moved the files from the Unix

machine can correct the line ends (for example, ftp can do

this)

2. Run the Unix utility unix2dos on the files before they

leave the Unix machine, or afterwards, under the Cygwin

Unix shell for Windows. (Or write a tiny command-line

Windows program to do this.)

Steve Kass

Drew University

ktto@.discussions.microsoft.com wrote:

> I figured out that the problem has to do with MSSQL's native behavior.

> It turns out that whenever it sees \n, it automatically converts it to

> \r\n, without notifiying the user. There are at least three ways to work

> around this strange behavior: 1) replace every \n in your file with \r\n

> before using Bulk Insert, 2) build your sql statement dynamically either

> in a stored procedure or VB.net, i.e., use & chr(10) & ,or + chr(10) +,

> instead of '\n' as the line terminator in your statement, and 3) load

> your file into a Datatable via ADO.net, and then insert the entire

> Datatable into MSSQL.