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

Newbie here. Problems on bulk loading.

Hi guys. I am kinda new in XML bulk loading section. Several tutorials had been done by myself and I get some difficulties when having an attribute in my XML. Here are my table, XML and XML schema for bulk loading.

Tables :

CREATE TABLE [dbo].[Game](
[ID] [int] IDENTITY(1,1) NOT NULL,
[code] [int] NOT NULL,
CONSTRAINT [PK_Game] PRIMARY KEY CLUSTERED
(
[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Match](
[ID] [int] NOT NULL,
[date] [datetime] NULL,
[gamecode] [int] NOT NULL,
CONSTRAINT [PK_Match] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[Match] WITH CHECK ADD CONSTRAINT [FK_Match_Game] FOREIGN KEY([gamecode])
REFERENCES [dbo].[Game] ([code])
GO
ALTER TABLE [dbo].[Match] CHECK CONSTRAINT [FK_Match_Game]

XML :

<?xml version='1.0' encoding='utf-8'?>

<ROOT>

<game code="1"/>

<match id = "1001" date="2007/08/01" />

<match id = "1002" date="2007/08/02" />
</game>

<game code="2"/>

<match id = "1003" date="2007/08/03" />

<match id = "1004" date="2007/08/04" />
</game>

<game code="3"/>

<match id = "1005" date="2007/08/05" />

<match id = "1006" date="2007/08/06" />
</game>

</ROOT>

XML Schema :

<xsdchema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlnsql="urnchemas-microsoft-com:mapping-schema">

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="gameMatch"
parent="Game"
parent-key="code"
child="Deal"
child-key="gamecode" />
</xsd:appinfo>
</xsd:annotation>

<xsd:element name="game" sql:relation="Game" >
<xsd:complexType>
<xsdequence>
<xsd:element name="Match"
sql:relation="Match"
sql:relationship="gameMatch" >
<xsd:complexType>
<xsd:attribute name="id" type="xsd:integer" /> -- error occurs on this attribute
</xsd:complexType>
</xsd:element>
</xsdequence>
<xsd:attribute name="date" type="xsdate" />
</xsd:complexType>
</xsd:element>

</xsdchema>

Hope able to get any assistance here. Thx.

My problem solved. Just the element mistake.

newbee - Where can I find sqlxmlbulkload

I want to import large XML files into my SQL 200 server. Several articles I
read all refer to the XML Bulk Load tool with references to the
msdn.microsoft.com/xml site
Here I find more articles on how to use this tool, but not the download
itsselt.
Where can I find this?It's included in SQLXML - the latest version is at
http://www.microsoft.com/downloads/...DisplayLang=en.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Nils" <Nils@.discussions.microsoft.com> wrote in message
news:25D3844C-B7E7-47FE-8A4D-644073B6E9CB@.microsoft.com...
I want to import large XML files into my SQL 200 server. Several articles I
read all refer to the XML Bulk Load tool with references to the
msdn.microsoft.com/xml site
Here I find more articles on how to use this tool, but not the download
itsselt.
Where can I find this?

newbee - Where can I find sqlxmlbulkload

I want to import large XML files into my SQL 200 server. Several articles I
read all refer to the XML Bulk Load tool with references to the
msdn.microsoft.com/xml site
Here I find more articles on how to use this tool, but not the download
itsselt.
Where can I find this?
It's included in SQLXML - the latest version is at
http://www.microsoft.com/downloads/d...isplayLang=en.
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Nils" <Nils@.discussions.microsoft.com> wrote in message
news:25D3844C-B7E7-47FE-8A4D-644073B6E9CB@.microsoft.com...
I want to import large XML files into my SQL 200 server. Several articles I
read all refer to the XML Bulk Load tool with references to the
msdn.microsoft.com/xml site
Here I find more articles on how to use this tool, but not the download
itsselt.
Where can I find this?