The bulk insert statement at the end of the line, anxious! Acute!! Acute!!! For

I want to import the database in the TXT file
The contents of the aa.txt file as:
A;1008000138;The 299 ship; 20027834; money the Great Wall;20130614;99991231;1008000003;
A;1008000138;The 299 ship; 20027834; money the Great Wall;20130614;99991231;1008000005;

Table structure created for:
CREATE TABLE [dbo].[people](
[s_type] [varchar](50) NOT NULL,
[c_id] [int] NOT NULL,
[c_name] [varchar](50) NOT NULL,
[p_id] [int] NOT NULL,
[p_name] [varchar](50) NOT NULL,
[b_date] [date] NOT NULL,
[e_date] [date] NOT NULL,
[l_id] [nvarchar](max) NOT NULL
)
Execute a SQL statement
bulk insert people from 'F:\aa.txt' with(fieldterminator=';',rowterminator=';\n')
Message 4832, level 16, state 1, line first
Bulk load: unexpected end of file in the data file.
Message 7399, level 16, state 1, line first
The linked server "(null)" OLE DB access interface "BULK" error. The provider did not give any information about the error.
Message 7330, level 16, state 2, line first
Not from the linked server "(null)" OLE DB "BULK" extraction line access interface.
But if the SQL statement is changed into
bulk insert people from 'F:\aa.txt' with(fieldterminator=';',rowterminator='\n')
Can be inserted into a plurality of data, but the last field more than a ";" for God to answer, how to get rid of the semicolon

Started by Sampson at February 02, 2016 - 10:33 PM

Is your main file, then each row are a semicolon;

Lead to

Posted by Fred at February 17, 2016 - 11:04 PM

Can put in each row a semicolon to remove it?

If not removed, it can only be in the bulk insert, through the update to replace the:

CREATE TABLE [dbo].[people](
[s_type] [varchar](50) NOT NULL,
[c_id] [int] NOT NULL,
[c_name] [varchar](50) NOT NULL,
[p_id] [int] NOT NULL,
[p_name] [varchar](50) NOT NULL,
[b_date] [date] NOT NULL,
[e_date] [date] NOT NULL,
[l_id] [nvarchar](max) NOT NULL
)
  
--truncate table people

bulk insert people from 'c:\aa.txt' with(fieldterminator=';',rowterminator='\n')

select * from people

update people
set l_id = left(l_id,len(l_id)-1)
/*
s_type	c_id	c_name	p_id	p_name	b_date	e_date	l_id
A 1008000138299 2013-06-14 9999-12-31 1008000003 ship 20027834 money the Great Wall
A 1008000138299 2013-06-14 9999-12-31 1008000005 ship 20027834 money the Great Wall
*/

Posted by Fred at February 24, 2016 - 11:52 PM

This file for me, is also a fixed format software export, each data have, this can not go. Or I can only put semicolons in the database, and then modify the data

Posted by Sampson at March 05, 2016 - 12:14 AM

Well yes, leading into it, and then use the above code, update just fine:

CREATE TABLE [dbo].[people](
[s_type] [varchar](50) NOT NULL,
[c_id] [int] NOT NULL,
[c_name] [varchar](50) NOT NULL,
[p_id] [int] NOT NULL,
[p_name] [varchar](50) NOT NULL,
[b_date] [date] NOT NULL,
[e_date] [date] NOT NULL,
[l_id] [nvarchar](max) NOT NULL
)
  
--truncate table people

bulk insert people from 'c:\aa.txt' with(fieldterminator=';',rowterminator='\n')

select * from people

- the semicolon removed
update people
set l_id = left(l_id,len(l_id)-1)
/*
s_type	c_id	c_name	p_id	p_name	b_date	e_date	l_id
A 1008000138299 2013-06-14 9999-12-31 1008000003 ship 20027834 money the Great Wall
A 1008000138299 2013-06-14 9999-12-31 1008000005 ship 20027834 money the Great Wall
*/

Posted by Fred at March 06, 2016 - 12:19 AM

Well, then, I have dealt with, but not like you.
The bulk insert statement is a semicolon and enter together as the function, but I don't know what to do not go on because of a semicolon. I think the semicolon to go.

Posted by Sampson at March 19, 2016 - 12:39 AM

I tried again, the semicolon as if the field delimiter is no problem, but if not supported as the row delimiter.

I put the aa.txt modify it, or not.:
A/1008000138/299 /20027834/ ship money the Great Wall/20130614/99991231/1008000003;
A/1008000138/299 /20027834/ ship money the Great Wall/20130614/99991231/1008000005;

Posted by Fred at March 23, 2016 - 1:02 AM

Test normal in SQL2008R2.,
bulk insert people from 'F:\aa.txt' with(fieldterminator=';',rowterminator=';\n')

Posted by Z at April 02, 2016 - 1:52 AM

Uh is normal, but as a guide in a semicolon

Posted by Fred at April 06, 2016 - 2:01 AM

The last field with no semicolon Oh, is normal.

Posted by Z at November 23, 2016 - 7:06 PM



My is 2008R2, the output is, finally, a semicolon:
s_type	c_id	c_name	p_id	p_name	b_date	e_date	l_id
A 1008000138299 2013-06-14 9999-12-31 1008000003 ship 20027834 money the Great Wall
A 1008000138299 2013-06-14 9999-12-31 1008000005 ship 20027834 money the Great Wall;

Posted by Fred at December 07, 2016 - 7:40 PM

I know what the problem is, your data, should be in the final, which is 1008000005 second line; later return, on the line:

A/1008000138/299 /20027834/ ship money the Great Wall/20130614/99991231/1008000003;
A/1008000138/299 /20027834/ ship money the Great Wall/20130614/99991231/1008000005;

Effect:
truncate table people
 
bulk insert people from 'c:\aa.txt' with(fieldterminator='/',rowterminator='\;\n')
 
select * from people
/*
s_type	c_id	c_name	p_id	p_name	b_date	e_date	l_id
A 1008000138299 2013-06-14 9999-12-31 1008000003 ship 20027834 money the Great Wall
A 1008000138299 2013-06-14 9999-12-31 1008000005 ship 20027834 money the Great Wall
*/

Posted by Fred at December 11, 2016 - 7:46 PM

My is 2008R2, is more a semicolon.

Posted by Sampson at December 19, 2016 - 8:10 PM

The last second lines of a field value of 1008000005; the back return, save again on the line.

Posted by Z at January 01, 2017 - 8:55 PM

This file can not be changed, can not be manipulated, only through the intelligent, is another file software export.
Just now I enter it, try a bit or not. Also with a semicolon

Posted by Sampson at January 10, 2017 - 9:29 PM

Method not found.
Bulk insert, manual correction about the last field problems.
bulk insert people from 'F:\aa.txt' 
with(fieldterminator=';',
     rowterminator=';\r',
     kilobytes_per_batch=5000)

update people 
 set l_id=left(l_id,len(l_id)-1)
 where right(l_id,1)=N';'

select * from people

Posted by Z at January 10, 2017 - 9:39 PM