bulk insert question

Hi,
I'm getting file (from mainfraim) which has to be imported
into sql server 2000 empty table using bulk insert:
bulk insert database..table
from 'f:\data.dat'
with (
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n',
CODEPAGE = 'raw'
)
Problem is that file itself has 6GB, and data in the table
after bulk insert is around 10GB. Why is this difference?
Can it be because of the code page?
Thanks,
OJHow are you discerning the size of the table? Are there non-clustered
indexes present?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
> Hi,
> I'm getting file (from mainfraim) which has to be imported
> into sql server 2000 empty table using bulk insert:
> bulk insert database..table
> from 'f:\data.dat'
> with (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> CODEPAGE = 'raw'
> )
> Problem is that file itself has 6GB, and data in the table
> after bulk insert is around 10GB. Why is this difference?
> Can it be because of the code page?
> Thanks,
> OJ|||For data size in the table, I'm using sp_spaceused (data
value). It returns accurate values because table is empty
before bulk insert. There aren't any indexes on the table.
>--Original Message--
>How are you discerning the size of the table? Are there
non-clustered
>indexes present?
>
>"OJ" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
>> Hi,
>> I'm getting file (from mainfraim) which has to be
imported
>> into sql server 2000 empty table using bulk insert:
>> bulk insert database..table
>> from 'f:\data.dat'
>> with (
>> FIELDTERMINATOR = '|',
>> ROWTERMINATOR = '\n',
>> CODEPAGE = 'raw'
>> )
>> Problem is that file itself has 6GB, and data in the
table
>> after bulk insert is around 10GB. Why is this
difference?
>> Can it be because of the code page?
>> Thanks,
>> OJ
>
>.
>|||Can you post DDL for the table? Also, how many rows of data are in the
table after the BULK INSERT?
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bd1501c45193$d05d14e0$a501280a@.phx.gbl...
> For data size in the table, I'm using sp_spaceused (data
> value). It returns accurate values because table is empty
> before bulk insert. There aren't any indexes on the table.
> >--Original Message--
> >How are you discerning the size of the table? Are there
> non-clustered
> >indexes present?
> >
> >
> >"OJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
> >> Hi,
> >> I'm getting file (from mainfraim) which has to be
> imported
> >> into sql server 2000 empty table using bulk insert:
> >>
> >> bulk insert database..table
> >> from 'f:\data.dat'
> >> with (
> >> FIELDTERMINATOR = '|',
> >> ROWTERMINATOR = '\n',
> >> CODEPAGE = 'raw'
> >> )
> >>
> >> Problem is that file itself has 6GB, and data in the
> table
> >> after bulk insert is around 10GB. Why is this
> difference?
> >> Can it be because of the code page?
> >> Thanks,
> >> OJ
> >
> >
> >.
> >|||I suggest you read up on page layout, extents and also row layout in SQL Server. It is possible that not all
pages are full. Also, each row has some overhead as does each page.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"OJ" <anonymous@.discussions.microsoft.com> wrote in message news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
> Hi,
> I'm getting file (from mainfraim) which has to be imported
> into sql server 2000 empty table using bulk insert:
> bulk insert database..table
> from 'f:\data.dat'
> with (
> FIELDTERMINATOR = '|',
> ROWTERMINATOR = '\n',
> CODEPAGE = 'raw'
> )
> Problem is that file itself has 6GB, and data in the table
> after bulk insert is around 10GB. Why is this difference?
> Can it be because of the code page?
> Thanks,
> OJ|||Hi OJ,
Have you run a dbcc showcontig on the table after the bulk insert (see BOL
for details)? I noticed you said that the table has no clustered indexes.
In my experience with bulk insert without a clustered index on the table it
can end up with a lot of unused space after the bulk insert. This might be
what is happening.
Wayne Antinore
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:1bd1501c45193$d05d14e0$a501280a@.phx.gbl...
> For data size in the table, I'm using sp_spaceused (data
> value). It returns accurate values because table is empty
> before bulk insert. There aren't any indexes on the table.
> >--Original Message--
> >How are you discerning the size of the table? Are there
> non-clustered
> >indexes present?
> >
> >
> >"OJ" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:1bf9201c45166$b423b6d0$a001280a@.phx.gbl...
> >> Hi,
> >> I'm getting file (from mainfraim) which has to be
> imported
> >> into sql server 2000 empty table using bulk insert:
> >>
> >> bulk insert database..table
> >> from 'f:\data.dat'
> >> with (
> >> FIELDTERMINATOR = '|',
> >> ROWTERMINATOR = '\n',
> >> CODEPAGE = 'raw'
> >> )
> >>
> >> Problem is that file itself has 6GB, and data in the
> table
> >> after bulk insert is around 10GB. Why is this
> difference?
> >> Can it be because of the code page?
> >> Thanks,
> >> OJ
> >
> >
> >.
> >

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.

>

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.

>