September 3, 2015 at 7:50 am
Hello, I am struggling on using bcp to import data, hopefully I can get some help from you guys. Here is my steps:
1. I created a Test database on my localhost
2. In the Test database, I created a Test table, the query is here for your convenience:
CREATE TABLE [dbo].[Test](
[id] [int] IDENTITY(1,1) NOT NULL,
[network_group_name] [varchar](128) NULL,
127.0.0.1 [varchar](15) NULL,
[OS] [varchar](128) NULL,
[App_name] [varchar](512) NULL,
[vuln_name] [varchar](512) NULL,
[host_score] [int] NULL,
[recordsWritten] [datetime] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I then create the format file used in bcp:
bcp Test.dbo.Test format nul -c -t, -f C:\RXie\SQL\Test.fmt –T
Here is the format file:
9.0
8
1 SQLCHAR 0 12 "," 1 id ""
2 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 128 "," 4 OS SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 512 "," 5 App_name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 512 "," 6 vuln_name SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 12 "," 7 host_score ""
8 SQLCHAR 0 24 "\r" 8 recordsWritten ""
The data file is called 20150902FullTest.rpt and the first couple lines (first line is the header and followed by two rows) are posted here:
network_group_name,IP,OS,App_Name,vuln_name,host_score,recordswritten
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0537 Vulnerability,4350,2015-09-01 09:55:07.720
Domestic,10.216.56.88,Windows XP SP3,Adobe / Macromedia Flash Player,APSB14-17: Adobe Flash Player CVE-2014-0539 Vulnerability,4350,2015-09-01 09:55:07.720
With the format file and the data file, I use the following bcp command:
bcp Test.dbo.Test in C:\Rxie\SQL\20150902FullTest.rpt -f C:\Rxie\SQL\Test.fmt -T
I got the following error messages:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
BCP copy in failed
I do want to mention here is the rpt file contains three BOM characters EF BB BF at the beginning of the file.
Thank you very much in advance.
September 3, 2015 at 8:25 am
Your format file is still wrong, and this is because you create it from the table instead of creating it from the file. The file doesn't have the id column, so that would leave it with 7 columns instead of 8.
You also need to define the first row to "skip" the header.
This is the format file. Note that the table column number skips the 1 which is the id.
9.0
9.0
7
1 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 128 "," 4 OS SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 512 "," 5 App_name SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 512 "," 6 vuln_name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 12 "," 7 host_score ""
7 SQLCHAR 0 24 "\r" 8 recordsWritten ""
----- ------- ------ ------ ------------ ----- ------------------- ---------
File Data Prefix Data End Of Field Table Table Collation
Order Type Length Length Delimiter Col # Col Name Name
And for the bcp command, you should add the first row option:
bcp Test.dbo.Test in C:\Rxie\SQL\20150902FullTest.rpt -f C:\Rxie\SQL\Test.fmt -T -F2
September 3, 2015 at 9:26 am
Thanks Luis, here is the new result:
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Native Client]Incorrect host-column number found in BCP format-file
September 3, 2015 at 9:35 am
The version is repeated in the format file. Delete one of them.
9.0
7
1 SQLCHAR 0 128 "," 2 network_group_name SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 15 "," 3 IP SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 128 "," 4 OS SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 512 "," 5 App_name SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 512 "," 6 vuln_name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 12 "," 7 host_score ""
7 SQLCHAR 0 24 "\r" 8 recordsWritten ""
----- ------- ------ ------ ------------ ----- ------------------- ---------
File Data Prefix Data End Of Field Table Table Collation
Order Type Length Length Delimiter Col # Col Name Name
September 3, 2015 at 9:48 am
Thank you Luis,
I am to start bcp all the data now and see how long it takes to complete all the 39977139 rows.
September 3, 2015 at 9:57 am
How can I ignore any error during the lengthy process? I indicate -e option but still got terminated,
Here is the bcp:
bcp Test.dbo.Test in C:\Rxie\SQL\20150902FullTest.rpt -f C:\Rxie\SQL\Test.fmt -T -F2 -e error.txt
here is the error message:
Starting copy...
1000 rows sent to SQL Server. Total sent: 1000
1000 rows sent to SQL Server. Total sent: 2000
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
1000 rows sent to SQL Server. Total sent: 3000
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][SQL Native Client]Invalid character value for cast specification
3072 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 296 Average : (10378.38 rows per sec.)
Here is the error.txt:
#@ Row 2033, Column 6: Invalid character value for cast specification @#
Mexico172.19.64.32Windows 7 x86 SP1SUN Java Runtime Environment 1.5.0_20Java Runtime Environment unspecified confidentiality integrity availability Vulnerability,28911,2015-09-01 09:55:07.720
#@ Row 2034, Column 6: Invalid character value for cast specification @#
Mexico172.19.64.32Windows 7 x86 SP1SUN Java Runtime Environment 1.5.0_20Java Web Start and Java Plug-in unspecified confidentiality integrity availability Vulnerability,28911,2015-09-01 09:55:07.720
#@ Row 2035, Column 6: Invalid character value for cast specification @#
Mexico172.19.64.32Windows 7 x86 SP1SUN Java Runtime Environment 1.5.0_20Java Runtime Environment unspecified confidentiality integrity availability Vulnerability,28911,2015-09-01 09:55:07.720
#@ Row 2039, Column 6: Invalid character value for cast specification @#
Mexico172.19.64.32Windows 7 x86 SP1SUN Java Runtime Environment 1.5.0_20Java Runtime Environment unspecified confidentiality integrity availability Vulnerability,28911,2015-09-01 09:55:07.720
#@ Row 2040, Column 6: Invalid character value for cast specification @#
Mexico172.19.64.32Windows 7 x86 SP1SUN Java Runtime Environment 1.5.0_20Java Runtime Environment unspecified confidentiality integrity availability Vulnerability,28911,2015-09-01 09:55:07.720
#@ Row 2042, Column 6: Invalid character value for cast specification @#
Mexico172.19.64.32Windows 7 x86 SP1SUN Java Runtime Environment 1.5.0_20Java Runtime Environment unspecified confidentiality integrity availability Vulnerability,28911,2015-09-01 09:55:07.720
#@ Row 2048, Column 6: Invalid character value for cast specification @#
Mexico172.19.64.32Windows 7 x86 SP1SUN Java Runtime Environment 1.5.0_2
September 3, 2015 at 10:02 am
I found the issue:
The data row contains comma in one of the column, which makes the bcp thinking it is a delimiter.
Maybe I need to re-generate the data by changing the query option from Comma delimited to Tab delimited in SSMS?
September 3, 2015 at 10:04 am
Include the max errors option with a large number that will be enough for all the errors.
Don't remove the -e option so you can troubleshoot the problem later.
-m max_errors
Specifies the maximum number of syntax errors that can occur before the bcp operation is canceled. A syntax error implies a data conversion error to the target data type. The max_errors total excludes any errors that can be detected only at the server, such as constraint violations.
A row that cannot be copied by the bcp utility is ignored and is counted as one error. If this option is not included, the default is 10.
September 3, 2015 at 10:06 am
halifaxdal (9/3/2015)
I found the issue:The data row contains comma in one of the column, which makes the bcp thinking it is a delimiter.
Maybe I need to re-generate the data by changing the query option from Comma delimited to Tab delimited in SSMS?
I strongly suggest that you avoid common characters as delimiters. Some people use weird combinations of characters to prevent this problem as you're not limited to a single character.
September 3, 2015 at 10:12 am
Luis Cazares (9/3/2015)
halifaxdal (9/3/2015)
I found the issue:The data row contains comma in one of the column, which makes the bcp thinking it is a delimiter.
Maybe I need to re-generate the data by changing the query option from Comma delimited to Tab delimited in SSMS?
I strongly suggest that you avoid common characters as delimiters. Some people use weird combinations of characters to prevent this problem as you're not limited to a single character.
Just did a quick test by generating the data using tab delimiter and I updated the format file as well as bcp command, here is a new finding:
Both data file ends with
(1000 row(s) affected)
There was no error when bcp it in the Comma file but an error throws for the Tab file:
Error = [Microsoft][SQL Native Client] Unexpected EOF encountered in BCP data-file
And the 1000 rows copied anyway, no error was sent to error.txt though.
September 3, 2015 at 11:27 am
halifaxdal (9/3/2015)
Luis Cazares (9/3/2015)
halifaxdal (9/3/2015)
I found the issue:The data row contains comma in one of the column, which makes the bcp thinking it is a delimiter.
Maybe I need to re-generate the data by changing the query option from Comma delimited to Tab delimited in SSMS?
I strongly suggest that you avoid common characters as delimiters. Some people use weird combinations of characters to prevent this problem as you're not limited to a single character.
Just did a quick test by generating the data using tab delimiter and I updated the format file as well as bcp command, here is a new finding:
Both data file ends with
(1000 row(s) affected)
There was no error when bcp it in the Comma file but an error throws for the Tab file:
Error = [Microsoft][SQL Native Client] Unexpected EOF encountered in BCP data-file
And the 1000 rows copied anyway, no error was sent to error.txt though.
Did you change the format file to use the adequate delimiter?
September 3, 2015 at 11:27 am
The import process took less than half an hour and complete like an amazing miracle, and with no error even for the last line.
Thank you very much Luis for your help.
September 3, 2015 at 11:31 am
halifaxdal (9/3/2015)
The import process took less than half an hour and complete like an amazing miracle, and with no error even for the last line.Thank you very much Luis for your help.
You're welcome.
I had a lot of headaches the first time I tried to use bcp and bulk insert. I hope that you were able to learn something from this and will be able to troubleshoot other processes in the future.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply