|
|
|
Author |
Message |
Gymstone
|
Posted: Thu Jun 17 16:02:46 CDT 2004 |
Top |
SQL Server Developer >> Bulk insert
We are trying to run bulk insert from a mapped network drive on a unix
machine. The file has the row terminator as '\n' and not '\r\n' like the the
windows. When i run the bulk insert command as below,
CREATE TABLE STAGING(
[LINE] [NVARCHAR] (1024)
)
BULK INSERT STAGING FROM '\\unix-machine\DOWNLOAD.TXT' WITH (FIRSTROW = 2,
ROWTERMINATOR = '\n')
I get 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.
When we ftp this file in ascii mode to windows then the bulk insert works.
Has anybody run into this issue and what is the fix.
Thanks,
David
SQL Server262
|
|
|
|
|
David
|
Posted: Thu Jun 17 16:02:46 CDT 2004 |
Top |
SQL Server Developer >> Bulk insert
bcp does not report any error but does not copy either. This is the command
i used for bcp.
bcp master..staging in c:\download.txt -c -T
Is this right.
> check out BCP (bulk copy program) in the BOL
>
>
> > We are trying to run bulk insert from a mapped network drive on a unix
> > machine. The file has the row terminator as '\n' and not '\r\n' like the
the
> > windows. When i run the bulk insert command as below,
> >
> > CREATE TABLE STAGING(
> > [LINE] [NVARCHAR] (1024)
> > )
> >
> > BULK INSERT STAGING FROM '\\unix-machine\DOWNLOAD.TXT' WITH (FIRSTROW =
2,
> > ROWTERMINATOR = '\n')
> >
> > I get 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.
> >
> >
> > When we ftp this file in ascii mode to windows then the bulk insert
works.
> > Has anybody run into this issue and what is the fix.
> >
> > Thanks,
> > David
> >
> >
> >
|
|
|
|
|
Bradley
|
Posted: Fri Jun 18 12:40:17 CDT 2004 |
Top |
SQL Server Developer >> Bulk insert
When I use BULK INSERT
BULK INSERT MyTable FROM 'MyImportFile.txt' WITH (FORMATFILE='MyFormat.FMT')
and I create a format file that matches up with the table and text file.
--
7.0
2
1 SQLCHAR 0 16 "" 1 MyFirstField
2 SQLCHAR 0 1 "\r\n" 2 MySecondField
--
Notice the \r\n as they are in the file, if I only had \n or \r or neither
then I would have them blank. The documentation for the format is in BOL.
Initially I tried to just use the syntax that you posted but ran in to
problems. So now I make a format file for everything.
-- B
> We are trying to run bulk insert from a mapped network drive on a unix
> machine. The file has the row terminator as '\n' and not '\r\n' like the
the
> windows. When i run the bulk insert command as below,
>
> CREATE TABLE STAGING(
> [LINE] [NVARCHAR] (1024)
> )
>
> BULK INSERT STAGING FROM '\\unix-machine\DOWNLOAD.TXT' WITH (FIRSTROW = 2,
> ROWTERMINATOR = '\n')
>
> I get 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.
>
>
> When we ftp this file in ascii mode to windows then the bulk insert works.
> Has anybody run into this issue and what is the fix.
>
> Thanks,
> David
>
>
|
|
|
|
|
|
|