<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
SQL Server 2008
»
SQL Server 2008 Administration
»
BCP Error
BCP Error
Rate Topic
Display Mode
Topic Options
Author
Message
tinausa
tinausa
Posted Monday, February 04, 2013 3:25 PM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:57 AM
Points: 34,
Visits: 82
I am using XML format file to import CSV files using BCP.
I use the following syntax.
bcp dbname.dbo.tablename in "c:\files\test.csv" /T /f"c:\files\test.xml"
But I get an error as below:
Starting copy...
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1030
I tried changing the packet size to 65535 (max allowed), but same error.
I have 10 csv files each of around 400+ mb and I am an accidental DBA.
I do need to load these files, but not sure how to do it. Any help would be greatly appreciated.
Post #1415511
David Webb-CDS
David Webb-CDS
Posted Monday, February 04, 2013 3:34 PM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 827,
Visits: 5,713
Could you post the definition of the target table, the format file, and a few rows of data for us to test with? Pretty sure you'd get a qucik fix if we can work with real stuff.
And then again, I might be wrong ...
David Webb
Post #1415514
tinausa
tinausa
Posted Tuesday, February 05, 2013 9:55 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:57 AM
Points: 34,
Visits: 82
Following is some test data.
"CUSTMR_NBR","ORCL_NBR","CUST_NBR","BLNG_CD","BSNS_LN","MKT","TRTRY","AREA","PRNT_NM","MAIN_NM","LOW_NM","CUST_NM","STAT","SYS","GUD","MON_NBR","YR","RVNU","QTY"
"15.ZB.AD095K","04119.0AD09K","AD09K","9333","PROD1","BOSTON","EAST","CENTRAL","CUST_1","cust1","CUST 1 INC.","CUST 1 INC.","A","XYZ","07XYZ8782",5,2012,0,5923
"15.ZB.AD41231","04119.0AD431","AD431","890","PROD3","NEW YORK","EAST","CENTRAL","CUST_2","NONE","CUST 2 INC.","CUST 2 INC.","A","XYZ","0023XYZ56",5,2012,139.3872,341
"15.ZU.AF1317","04314.0AF117","AF117","220","PROD4","AUSTIN","WEST","WESTERN","CUST_33","CUST33","CUST 33 INC.","CUST 33 INC.","A","ABC","08216ABC1",5,2012,0,359
Post #1415964
tinausa
tinausa
Posted Tuesday, February 05, 2013 9:59 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:57 AM
Points: 34,
Visits: 82
Also the XML format file.
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="12"/>
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="30"/>
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="30"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="CUSTMR_NBR" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="ORCL_NBR" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="CUST_NBR" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="BLNG_CD" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="BSNS_LN" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="MKT" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="TRTRY" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="8" NAME="AREA" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="PRNT_NM" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="10" NAME="MAIN_NM" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="11" NAME="LOW_NM" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="12" NAME="CUST_NM" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="13" NAME="STAT" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="14" NAME="SYS" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="15" NAME="GUD" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="16" NAME="MON_NBR" xsi:type="SQLINT"/>
<COLUMN SOURCE="17" NAME="YR" xsi:type="SQLINT"/>
<COLUMN SOURCE="18" NAME="RVNU" xsi:type="SQLFLT8"/>
<COLUMN SOURCE="19" NAME="QTY" xsi:type="SQLFLT8"/>
</ROW>
</BCPFORMAT>
Post #1415970
opc.three
opc.three
Posted Tuesday, February 05, 2013 10:13 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737,
Visits: 11,791
CREATE TABLE statement for the target table?
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1415976
tinausa
tinausa
Posted Tuesday, February 05, 2013 11:25 AM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:57 AM
Points: 34,
Visits: 82
hereit is.
create table [SalesRevenue].[dbo].tempS
( [CUSTMR_NBR] varchar(255)
,[ORCL_NBR] varchar(255)
,[CUST_NBR] varchar(255)
,[BLNG_CD] varchar(255)
,[BSNS_LN] varchar(255)
,[MKT] varchar(255)
,[TRTRY] varchar(255)
,[AREA] varchar(255)
,[PRNT_NM] varchar(255)
,[MAIN_NM] varchar(255)
,[LOW_NM] varchar(255)
,[CUST_NM] varchar(255)
,[STAT] varchar(255)
,[SYS] varchar(255)
,[GUD] varchar(255)
,[MON_NBR] int
,[YR] int
,[RVNU] float
,[QTY] float
)
Post #1416015
David Webb-CDS
David Webb-CDS
Posted Tuesday, February 05, 2013 12:15 PM
SSC Eights!
Group: General Forum Members
Last Login: Yesterday @ 2:29 PM
Points: 827,
Visits: 5,713
How did you generate the format file? First thing I see is that the format file describes the input as being tab delimited, but the input is comma delimited with quotes. Can you change the input to be tab delimited?
And then again, I might be wrong ...
David Webb
Post #1416026
tinausa
tinausa
Posted Tuesday, February 05, 2013 12:45 PM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:57 AM
Points: 34,
Visits: 82
I have had these tables and asp.net code to load data, but the data until now was in 1.5 m rows at a time ot causing any problem.
So I copied the table structure to this table to create a new table and used bcp to create format file.
The csv files now are 3m rows each and my code runs out of memory. SO I was looking at BCP as an option.
The last four fields in the table and csv file (month, year, revenue, qty) are not enclosed in quotes, so I assume that they are either int or float.
The csv file is comma-delimited.
Thanks.
Post #1416042
opc.three
opc.three
Posted Tuesday, February 05, 2013 12:48 PM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737,
Visits: 11,791
David is right about the format-file not matching the file's format.
For starters you'll have to change all instances of this in your format file:
<FIELD ID="1" xsi:type="CharTerm"
TERMINATOR="\t"
MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
to this:
<FIELD ID="1" xsi:type="CharTerm"
TERMINATOR=","
MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
Note that your data will still be loading with the surrounding quote marks. You can avoid most of that by changing your columns delimiters in this way but you'll still have a leading quote mark in the first column:
<FIELD ID="1" xsi:type="CharTerm"
TERMINATOR='","'
MAX_LENGTH="255" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
Then in your command line you have one issue for sure, and potentially a second issue depending on your environment config:
Change this:
bcp dbname.dbo.tablename in "c:\files\test.csv" /T /f"c:\files\test.xml"
to this:
bcp dbname.dbo.tablename in "c:\files\test.csv" /T /f"c:\files\test.xml"
-F 2 /S servername\instancename
The /F which is required in your case tells bcp to skip the first row containing the column names.
The /S is optional depending on your config. If you're loading data into the default instance on the machine where you run bcp then no issues, else you'll need to provide the /S option.
__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Believe you can and you're halfway there.
--Theodore Roosevelt
Everything Should Be Made as Simple as Possible, But Not Simpler
--Albert Einstein
The significant problems we face cannot be solved at the same level of thinking we were at when we created them.
--Albert Einstein
1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples.
--Giordy
Post #1416043
tinausa
tinausa
Posted Tuesday, February 05, 2013 2:55 PM
SSC Rookie
Group: General Forum Members
Last Login: Sunday, May 19, 2013 5:57 AM
Points: 34,
Visits: 82
Thank you so much. You are the best.
Post #1416116
« Prev Topic
|
Next Topic »
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
