February 4, 2013 at 3:25 pm
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.
February 4, 2013 at 3:34 pm
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.
February 5, 2013 at 9:55 am
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
February 5, 2013 at 9:59 am
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>
February 5, 2013 at 10:13 am
CREATE TABLE statement for the target table?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
February 5, 2013 at 11:25 am
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)
,
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
)
February 5, 2013 at 12:15 pm
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?
February 5, 2013 at 12:45 pm
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.
February 5, 2013 at 12:48 pm
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
February 5, 2013 at 2:55 pm
Thank you so much. You are the best.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply