bulk insert[urgent]

Earlier rowterminator for the text file was '\r\n', I was albe to bulk inser
t
with rowterminator
Now it is changed to '\n', When I use the following syntax
create table #tmp1 ([rows] varchar(5000) )
BULK INSERT #tmp1 from 'c:\test_file.txt'
with
(rowterminator = '\n')
I am getting 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.
any help is appreciated.
thanksif u have cygwin installed
try this at command line
c:> type c:\test_file.txt' | wc -l
See what the longest line is. Although you are inserting one reow, see
if a field delimiter might help...\t or something like that or maybe '
' (five spaces)...dirty, but quick...|||> 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.
>
It sounds like your field terminator has changed as well. Double check.|||thanksforhelp (thanksforhelp@.discussions.microsoft.com) writes:
> Earlier rowterminator for the text file was '\r\n', I was albe to bulk
> insert with rowterminator
> Now it is changed to '\n', When I use the following syntax
> create table #tmp1 ([rows] varchar(5000) )
> BULK INSERT #tmp1 from 'c:\test_file.txt'
> with
> (rowterminator = '\n')
> I am getting following error
Only newline as a terminator is somewhat problematic. If memory
serves, I think this works:
EXEC('BULK INSERT #tmp1 from ''c:\test_file.txt''
with (rowterminator = ''' + char(13) + '''')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Another option also, if it wont create problems later is to run a batch
file/shell script which converts all your files to dos format...ending
with CHAR(13)+CHAR(10) i.e. ("\r\n")
this is another cygwin option
vim +"argdo set ff=dos" +wqa "Filename"
I use dir c:\directory\*.txt /s/b/ > fileconvert.bat
to create a list of all text files,
then use a macro in a text editor to "quote" the filenames, and add the
vim +"argdo set ff=dos" +wqa command at the front.
I then run fileconvert.bat
and voila... all files in dos format...|||no, this didn't work for me.
thanks for Ur time
"Erland Sommarskog" wrote:

> thanksforhelp (thanksforhelp@.discussions.microsoft.com) writes:
> Only newline as a terminator is somewhat problematic. If memory
> serves, I think this works:
> EXEC('BULK INSERT #tmp1 from ''c:\test_file.txt''
> with (rowterminator = ''' + char(13) + '''')
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||thanksforhelp (thanksforhelp@.discussions.microsoft.com) writes:
> no, this didn't work for me.
> thanks for Ur time
Sorry, I was a bit too quick, and I mixed up the numeric codes for
CR and LF. Here is a version that I've actually tested:
create table #tmp1 ([rows] varchar(200) )
declare @.sql varchar(2000)
SELECT @.sql = 'BULK INSERT #tmp1 from ''c:\test_file.txt'''
with (rowterminator = ''' + char(10) + ''')'
EXEC(@.sql)
go
select * from #tmp1
go
drop table #tmp1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks Erland
but I tried with your code even then I am getting the same error, I am able
load this file with format file with rowterminator \n. But I don't want hard
code the format file in my SP.
Thanks for your time.
"Erland Sommarskog" wrote:

> thanksforhelp (thanksforhelp@.discussions.microsoft.com) writes:
> Sorry, I was a bit too quick, and I mixed up the numeric codes for
> CR and LF. Here is a version that I've actually tested:
> create table #tmp1 ([rows] varchar(200) )
> declare @.sql varchar(2000)
> SELECT @.sql = 'BULK INSERT #tmp1 from ''c:\test_file.txt'''
> with (rowterminator = ''' + char(10) + ''')'
> EXEC(@.sql)
> go
> select * from #tmp1
> go
> drop table #tmp1
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||thanksforhelp (thanksforhelp@.discussions.microsoft.com) writes:
> but I tried with your code even then I am getting the same error, I am
> able load this file with format file with rowterminator \n. But I don't
> want hard code the format file in my SP.
Too bad. It was not because I had changed the size of the temp table?
(I cut it down to varchar(200) to make sure that I would get an error
with my test file, if the rowterminator was never found and BULK INSERT
read it as one row.)
Else would it be possible for you to attach a sample file, or put a
sample at a web site in a zip file?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanks Erland.
I am able to bulk insert it with a format file
with '\n' rowterminator. now I want to know why it is possible with format
file and not with rowterminator and single column.
Since it is company policy I can't post the file in the web
Thank you very much for the time.
"Erland Sommarskog" wrote:

> thanksforhelp (thanksforhelp@.discussions.microsoft.com) writes:
> Too bad. It was not because I had changed the size of the temp table?
> (I cut it down to varchar(200) to make sure that I would get an error
> with my test file, if the rowterminator was never found and BULK INSERT
> read it as one row.)
> Else would it be possible for you to attach a sample file, or put a
> sample at a web site in a zip file?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>