Newbie question on BULK INSERT of text file

A non-SQL application appends messages to a plain text file.
I want to read the rows of this text file into a table.
The text is in a general format and should be considered one column.
The lines are terminated with a typical CR/LF.
I'd like to do more analysis on the text after getting it into a table.
My Problem: BULK INSERT will skip every other line
with the code below. It seems to take the CR/LF as a column
terminator and then skip over the next row to the new CR/LF
to consider the row to be complete.
How do I get rows of plain text from a text file to a table?
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'MyTextFile.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
FIELDTERMINATOR = '\r',
ROWTERMINATOR = '\n'
)try taking out the field terminator parameter and make the row teminator the
combined "\r\n" instead.
If that fails, It's possible you might have to use "\n\r".
"Don Anthony" wrote:

> A non-SQL application appends messages to a plain text file.
> I want to read the rows of this text file into a table.
> The text is in a general format and should be considered one column.
> The lines are terminated with a typical CR/LF.
> I'd like to do more analysis on the text after getting it into a table.
> My Problem: BULK INSERT will skip every other line
> with the code below. It seems to take the CR/LF as a column
> terminator and then skip over the next row to the new CR/LF
> to consider the row to be complete.
> How do I get rows of plain text from a text file to a table?
> Create Table #MyTempFile ( FileLine varchar(250) )
> BULK INSERT #MyTempFile
> FROM 'MyTextFile.txt'
> WITH
> (
> BATCHSIZE = 50,
> DATAFILETYPE = 'char',
> FIELDTERMINATOR = '\r',
> ROWTERMINATOR = '\n'
> )
>|||When I take out the FIELDTERMINATOR line I get the error show below
(tried various combinations of ROWTERMINATOR but get the same 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.|||You're right. My mistake. But I did get two different versions of bulk
insert ot work
including your original code. Are you sure there are no other stray
caharacters at the end of the lines other than Cr/LF? If you have a text
editor, check the hex display to make sure. Also, is it possible there are
data lines that are more than 250 bytes? Your table definition allows for
varchar(250).
This worked for me:
Create Table #MyTempFile ( FileLine varchar(250) )
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r',
ROWTERMINATOR = '\n'
)
Textest.txt contains for records each with cr/lf line terminator:
1234567890
0987654321
abcdefghij
wxyzabcdef
This version worked too:
BULK INSERT #MyTempFile
FROM 'e:\state_calls\texttest.txt'
WITH
(
BATCHSIZE = 50,
DATAFILETYPE = 'char',
fieldterminator = '\r\n'
)
"Don Anthony" wrote:

> When I take out the FIELDTERMINATOR line I get the error show below
> (tried various combinations of ROWTERMINATOR but get the same 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.
>|||Your code works perfectly.
My code wasn't working because it wasn't quite what I originally indicated
(I did say I was a newbie...)
The real table definition was
CREATE TABLE #MyTempTable ( FileLine varchar(250), RowID int IDENTITY(1, 1)
)
not
CREATE TABLE #MyTempTable ( FileLine varchar(250) )
The bulk insert apparently threw away every other line after
failing to fit it into the identity column.
Everything works fine after I take out the extra column.
Thanks for your help.
"tthrone" wrote:sql