Import Text to Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Import Text to Table

Author

Topic

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-17 : 12:55:29

Hi,
I am trying to import text file to table in sql server. But I keep on getting error msg. Could anyone figure out why this is happening ? Thanks !

---------- Error Messsages --------------
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
----------------------------------------------------------

Create table [dbo].[TY]
(
--TradeSeqNumber int not null,
Instrument Char(20) not null,
TradeDate varchar(10) not null,
TradeTime varchar(10) not null,
TradePrice float not null,
Vol int default 0,
TickCode char(2) default '',
Condition_Code Char(10)
)

Select * from [dbo].[TY];

BULK
INSERT [dbo].[TY]
FROM 'C:\oneSec.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

---------------Data in my text file ---------------------------

EY omy|12/12|17:00:00|124.34375|||SETT|
EY omy|12/12|16:59:58|124.484375|1|||
EY omy|12/12|16:59:58|124.46875|1|||
EY omy|12/12|16:59:57|124.453125|6|||
EY omy|12/12|16:59:29|124.453125|1|||
EY omy|12/12|16:59:26|124.453125|12|||
EY omy|12/12|16:59:22|124.46875|1|||
EY omy|12/12|16:59:19|124.46875|1|||
EY omy|12/12|16:59:10|124.46875|1|||
EY omy|12/12|16:59:08|124.484375|1|||
EY omy|12/12|16:59:03|124.484375|1|||
EY omy|10/23|22:02:28|113.328125|||LB|
EY omy|10/23|22:02:00|113.3125|||LB|
EY omy|10/23|22:01:58|113.296875|||LB|
EY omy|10/23|22:00:34|113.28125|||LB|
EY omy|10/23|21:58:41|113.265625|||LB|
EY omy|10/23|20:25:38|113.25|||LA|
EY omy|10/23|20:23:48|113.265625|||LA|
EY omy|10/23|20:22:36|113.359375|||LA|
EY omy|10/23|18:33:15|113.59375|||LA|
RY omy|12/12|17:00:00|125.34375|||SETT|
RY omy|12/12|16:59:58|125.484375|1|||
RY omy|12/12|16:59:58|125.46875|1|||
RY omy|12/12|16:59:57|125.453125|6|||
RY omy|12/12|16:59:29|125.453125|1|||
RY omy|12/12|16:59:26|125.453125|12|||
RY omy|12/12|16:59:22|125.46875|1|||
RY omy|12/12|16:59:19|125.46875|1|||
RY omy|12/12|16:59:10|125.46875|1|||
RY omy|12/12|16:59:08|125.484375|1|||
RY omy|12/12|16:59:03|125.484375|1|||
RY omy|10/23|22:02:28|112.328125|||LB|
RY omy|10/23|22:02:00|112.3125|||LB|
RY omy|10/23|22:01:58|112.296875|||LB|
RY omy|10/23|22:00:34|112.28125|||LB|
RY omy|10/23|21:58:41|112.265625|||LB|
RY omy|10/23|20:25:38|112.25|||LA|
RY omy|10/23|20:23:48|112.265625|||LA|
RY omy|10/23|20:22:36|113.359375|||LA|
RY omy|10/23|18:33:15|113.59375|||LA|
AE omy|12/12|17:00:00|125.34375|||SETT|
AE omy|12/12|16:59:58|125.484375|1|||
AE omy|12/12|16:59:58|125.46875|1|||
AE omy|12/12|16:59:57|125.453125|6|||
AE omy|12/12|16:59:29|125.453125|1|||
AE omy|12/12|16:59:26|125.453125|12|||
AE omy|12/12|16:59:22|125.46875|1|||
AE omy|12/12|16:59:19|125.46875|1|||
AE omy|12/12|16:59:10|125.46875|1|||
AE omy|12/12|16:59:08|125.484375|1|||
AE omy|12/12|16:59:03|125.484375|1|||
AE omy|10/23|22:02:28|112.328125|||LB|
AE omy|10/23|22:02:00|112.3125|||LB|
AE omy|10/23|22:01:58|112.296875|||LB|
AE omy|10/23|22:00:34|112.28125|||LB|
AE omy|10/23|21:58:41|112.265625|||LB|
AE omy|10/23|20:25:38|112.25|||LA|
AE omy|10/23|20:23:48|112.265625|||LA|
AE omy|10/23|20:22:36|113.359375|||LA|

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 12:57:15

whats the error?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-17 : 12:57:35

Don't you think the error message text could help us?

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-17 : 12:59:25

quote:
Originally posted by bjoerns

Don't you think the error message text could help us?

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 13:01:25

i think thats because of | symbol at end. modify like this and see if it works


BULK
INSERT [dbo].[TY] 
FROM 'C:\oneSec.txt'
WITH
(
FIELDTERMINATOR = '|', 
ROWTERMINATOR = '|\n'
)

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-17 : 13:06:01

quote:
Originally posted by visakh16

i think thats because of | symbol at end. modify like this and see if it works

BULK
INSERT [dbo].[TY]
FROM 'C:\oneSec.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

Hi, I just added |symbol at the end. but it still gives me same error messages....

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 13:08:25

then try specifying a format file

http://msdn.microsoft.com/en-us/library/ms178129.aspx

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-12-17 : 13:14:27

rowterminator = '\n' never works (at least not for me). Try

declare @sql nvarchar(4000)
set @sql =
	'bulk insert [dbo].[TY] ' +
	'from ''C:\oneSec.txt'' ' +
	'with(fieldterminator = ''|'', ' +
	'rowterminator = ''' + nchar(10) + ''')'
--print @sql
exec sp_executesql @sql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 13:35:05

it will when specified inside format file

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-17 : 14:06:20

quote:
Originally posted by bjoerns

rowterminator = '\n' never works (at least not for me). Try
declare @sql nvarchar(4000)
set @sql =
'bulk insert [dbo].[TY] ' +
'from ''C:\oneSec.txt'' ' +
'with(fieldterminator = ''|'', ' +
'rowterminator = ''' + nchar(10) + ''')'
--print @sql
exec sp_executesql @sql

Hi, I just tried the logic you posted. But I am still getting error. I just copied and pasted on my editor. Do I need to change anything on your logic ? Thanks !

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 23:21:13

did you try specifying a format file as i suggested?

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-17 : 23:56:41

quote:
Originally posted by visakh16

did you try specifying a format file as i suggested?

No, not yet... It seems too difficult to go over from beginning since I am still new to sql server. I was trying to fix the logic I posted earlier. And I found one thing that I always get error on very last row because my logic reads the end of '|' as character in the last row . So if I take out the end of pipeline in last row, it works fine. Do you have any idea How to fix this problem ?

Thanks !

EA |10/26|18:00:46|860.|1|||
EA |10/26|18:00:46|864.25|||LA|
EA |10/26|18:00:37|864.5|||LA|
EA |10/26|18:00:29|864.75|||LA

--------------------------------------
drop table [dbo].[TY];

Create table [dbo].[TY]
(

Instrument nvarchar(10) not NULL,
TradeDate nvarchar (10) not NULL,
TradeTime nvarchar(10) not NULL,
TradePrice float not NULL,
Vol int default '0',
TickCode nvarchar(10) default '',
Condition_Code char(10) default '',
)

BULK
INSERT [dbo].[TY]
FROM 'C:\test2.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)

select * from dbo.TY_GetAllTicks;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 00:14:52

thats because it identifies the | as start of next field and expects data for it

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-18 : 00:17:08

quote:
Originally posted by visakh16

thats because it identifies the | as start of next field and expects data for it

Then what would be the solution for that ? Thanks !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 00:18:15

try putting /n after last |

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-18 : 00:54:06

quote:
Originally posted by visakh16

try putting /n after last |

No... it is not working... it reads /n as characters.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 01:07:20

quote:
Originally posted by dbonneau

quote:
Originally posted by visakh16

try putting /n after last |

No... it is not working... it reads /n as characters.

Thanks!

ah...i messed with it... it should have been \n not /n

dbonneau
Yak Posting Veteran

50 Posts

Posted - 2008-12-18 : 01:15:37

quote:
Originally posted by visakh16

quote:
Originally posted by dbonneau

quote:
Originally posted by visakh16

try putting /n after last |

No... it is not working... it reads /n as characters.

Thanks!

ah...i messed with it... it should have been \n not /n

I already tried in that way also.... It still reads it as a character. Thanks.

LarryC74
Yak Posting Veteran

94 Posts

Posted - 2012-03-06 : 20:26:11

dbonneau did you ever get this working? I have the EXACT same issue

Larry

Everyday life brings me back to reality

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources