Data import from textfile to the SQL database tabl

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Data import from textfile to the SQL database tabl

Author

Topic

Looktroniks
Starting Member

2 Posts

Posted - 2010-10-28 : 04:50:06

Hi guys I'm having a serious trouble trying to import data in a textfile into an SQL database table: I tried the following:

USE MyDatabase
GO
BULK INSERT register1 FROM 'C:\myfile.txt'
WITH
(
FIELDTERMINATOR = ','
)
GO

And I get the following 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 2. 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.
The statement has been terminated.

I also tried the DTS/Import Wizard and I got the following error:

Error: Source: Microsoft Data Transformation Services Flat File Rowset Provider
Error Description: Too many columns found in the current row; non-whitespace characters were found after the last defined column's data
Context: Error calling GetNextRows. Your provider does not support all the interfaces/methods required by DTS

He's my sample textfile and the original file is 97.4MB

M1999028576, SUPERIOR TRUCK HIRE AND DISTRIBUTION SERVICE
M1999028577, BATHOMI FINANCE COMPANY
M2009011815, CAPRALINX
M1999028578, QUANTUM LEAP INVESTMENTS 176
M1999028581, BENBROW INVESTMENTS
M1999028586, LAW VLEIS
M2000000004, KNOWLES HUSAIN LINDSAY
B2000001178, LOSU VERVOER
M1999028597, FINIAL HOLDINGS
M1999028598, EQUISTOCK PROPERTIES 47
M1999028599, PROMINENT FLUID CONTROLS (SOUTH AFRICA)
M1999028600, LARITZA INVESTMENTS NO 71
M1999028601, JM BUSHA ASSET MANAGERS
M1999028604, VECTO TRADE 226
M2001012384, MAYET
M1999028606, IQUAD INVESTMENT INCENTIVES
B2001038348, SEMANSHU GARDEN SERVICES
B2000001179, CAV2 INVESTMENT
M1999028609, DR JANNIE BARNARD

Please help!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-28 : 05:50:49

Maybe the ROWTERMINATOR?
Try:
USE MyDatabase
GO
BULK INSERT register1 FROM 'C:\myfile.txt'
WITH
(
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n'
)
GO

or try
USE MyDatabase
GO
BULK INSERT register1 FROM 'C:\myfile.txt'
WITH
(
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\r\n'
)
GO

If that doesn't help you should examine the file using a HexEditor to see the used rowterminator...

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources