473,726 Members | 2,162 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ROWTERMINATOR in bulk insert query.

We have created CSV files on HPUX 11.0 and transferred them via ASCII ftp
to our SQL Server machine file store to load large amounts for data using
the BULK INSERT command. This is the command:

BULK INSERT db..table FROM 'S:\path\filena me.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Now these files are written on Linux and there seems to be a linefeed
problem when loading
the data. This is the 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
31. Make sure the field terminator and row terminator are specified
correctly.
/
Column 31 is our last column in the CSV file.
I've tried '\r', '\r\n' for ROWTERMINATOR and cannot get it execute past
the 1st line.
If one opens the CSV file in Wordpad and saves it, then executes the
above statement,
the ROWTERMINATOR = '\n' suffices but this is an unreasonable solution.

Any help on this is greatly appreciated.

Michael Husler
Aug 17 '05 #1
2 31765
Mike Husler (Mi************ **@noaa.gov) writes:
We have created CSV files on HPUX 11.0 and transferred them via ASCII ftp
to our SQL Server machine file store to load large amounts for data using
the BULK INSERT command. This is the command:

BULK INSERT db..table FROM 'S:\path\filena me.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Now these files are written on Linux and there seems to be a linefeed
problem when loading
the data. This is the error:


A classic problem, that I don't think I ever found a solution to.
Until now. This is ugly, but it works:

CREATE TABLE nisse (a varchar(22) NOT NULL,
b varchar(23) NOT NULL,
c varchar(23) NOT NULL)
go
DECLARE @sql nvarchar(4000)
SELECT @sql =
'BULK INSERT nisse FROM ''E:\temp\slask .csv''
WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '';'',
ROWTERMINATOR = ''' + nchar(10) + ''')'
EXEC(@sql)
go
SELECT * FROM nisse
go
DROP TABLE nisse
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp


Aug 17 '05 #2
Erland Sommarskog wrote:
Mike Husler (Mi************ **@noaa.gov) writes:

We have created CSV files on HPUX 11.0 and transferred them via ASCII ftp
to our SQL Server machine file store to load large amounts for data using
the BULK INSERT command. This is the command:

BULK INSERT db..table FROM 'S:\path\filena me.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Now these files are written on Linux and there seems to be a linefeed
problem when loading
the data. This is the error:


A classic problem, that I don't think I ever found a solution to.
Until now. This is ugly, but it works:

CREATE TABLE nisse (a varchar(22) NOT NULL,
b varchar(23) NOT NULL,
c varchar(23) NOT NULL)
go
DECLARE @sql nvarchar(4000)
SELECT @sql =
'BULK INSERT nisse FROM ''E:\temp\slask .csv''
WITH (
DATAFILETYPE = ''char'',
FIELDTERMINATOR = '';'',
ROWTERMINATOR = ''' + nchar(10) + ''')'
EXEC(@sql)
go
SELECT * FROM nisse
go
DROP TABLE nisse

Thanks. We also discovered the unix2dos command on linux and running
all the CSV files
through that solved it. Thanks for the solution.
Aug 17 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
4898
by: me | last post by:
I'm also having problems getting the bulk insert to work. I don't know anything about it except what I've gleened from BOL but I'm not seeming to get anywhere...Hopefully there is some little (or big) problem with my code that someone can point out that may save me some time. TIA CBL
1
12830
by: Mike | last post by:
I am trying to use the 'Bulk Insert' command to load a data file into a MS-SQL db. The line I am using is: Bulk Insert SVC_Details From "C:\XFILE.TXT" With (FieldTerminator = ',') I have tried the file name with " around it and with ' around it and both the " and ', but every time the Query Analyzer comes back with the following error: ---Begin Error Msg---
10
31703
by: Daniel P. | last post by:
How can I use ADO.NET to insert lots of records in a very fast way? Thanks!
11
28343
by: Ted | last post by:
OK, I tried this: USE Alert_db; BULK INSERT funds FROM 'C:\\data\\myData.dat' WITH (FIELDTERMINATOR='\t', KEEPNULLS, ROWTERMINATOR='\r\n');
1
3097
by: teddymeu | last post by:
Hi Guys, trying to bulk insert a csv file into my SQL database from an asp.net vb web app/form page that the user uploads, my problem is that im new to all this and although the SQL statement inserts the CSV within my SQL Query analyser it comes up with this error in the actual app when trying to run it, ive probably done this wrong so can someone look at the error and my code and point me in the right direction Server Error in...
7
33042
by: girl | last post by:
Hi i have a problem with MS SQL server 2000 and hope to seek for some advise. i have the following samples aa|0|abcdefg| b|0|abcdefg| i used the bulk insert in the query analyser.. BULK INSERT adl_ntid from 'C:/abc.unl with
0
2091
by: rshivaraman | last post by:
BULK INSERT bill_tbl FROM 'd:\ftp_Data\in\baddress.dat' WITH ( FIELDTERMINATOR = ';', ROWTERMINATOR = '\n' ) --------------------------------- This is the query used to populate bill_tbl. Actually this baddress.dat contain rowdelimiter of \r\n.
2
4524
by: jthep | last post by:
How can I use bulk insert to insert a text file where the columns in the text file is in different order than the columns in the table? I have a ZIP table with Zip_Code, Zip_City, Zip_State and the text file has the fields in Zip_City, Zip_State, Zip_Code. The instructions were to keep the order as defined in the Entity Definition which would be the first order. My code for the bulk insert is usually BULK INSERT DB2914.dbo. FROM...
2
5891
by: zswanson | last post by:
Hi everyone, I receive a variety of text based files that seperates each row by a single delimiter, usually '~'. I use BULK INSERT to transfer the file from a text file to a temporary table. It works fine for most of the files (which use '~' as the delimiter for the rows), but for some reason I can't get it to work for a particular file. The file looks like it uses a new line character as the row delimiter, so I put in '\n' as the row...
0
8752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9402
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9259
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9182
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8101
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6702
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4521
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4785
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2157
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.