Bulk Insert does not read entire csv file.....

Question

Post reply

Bulk Insert does not read entire csv file.....

Eric Weinstein

Old Hand

Points: 381
More actions
August 11, 2009 at 9:33 am

#97972

I have a stored procedure that is reading in a csv file using the bulk insert command. When the script runs, everything looks fine, but it only reads most of the file. It leaves other records in the file and does not import them into the temporary table. I noticed when I ran it as an administrator, it read all of the entire file into the table. But when I run the stored procedure as a non sysadmin users, it only reads 3/4th of the file. Does anyone know if their is a limited size it can read in? I'm reading each line through a nvarchar(4000) variable.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

edwardelliott SSCommitted Points: 1863 More actions · Answer 1

can you upload a copy of the stored procedure and file?

edwardelliott SSCommitted Points: 1863 More actions · Answer 2

edwardelliott

SSCommitted

Points: 1863

August 11, 2009 at 2:38 pm

#1037737

and which version of sql...

Eric Weinstein Old Hand Points: 381 More actions · Answer 3

We have sql server 2000.

Here is the sql server code to import the data file:

DROP TABLE dbo.SALES_IMPORT

CREATE TABLE dbo.SALES_IMPORT (

STORE int,

[DATE(yyyymmdd)] smalldatetime,

[TAXABLE SALES] decimal(19,2),

[NON TAXABLE SALES] decimal(19,2),

[GIFT CERTIFICATE SALES] decimal(19,2),

[SALES TAX COLLECTED] decimal(19,2),

[LABOR] decimal(19,2),

[RETURNS] decimal(19,2),

[NON TAXABLE RETURNS] decimal(19,2),

[NET SALES] decimal(19,2),

[MASTER CARD/VISA] decimal(19,2),

[DEPOSIT AMOUNT] decimal(19,2),

[DISCOVER] decimal(19,2),

[AMERICAN EXPRESS] decimal(19,2),

[VAC VOUCHER] decimal(19,2),

[ACCOUNT RECEIVABLE] decimal(19,2),

[CHECK REFUND] decimal(19,2),

[CUSTOMER COUNT] int,

[AVERAGE SALE] decimal(19,2),

[NON MERCHANDISE SALES] decimal(19,2),

DISCOUNTS decimal(19,2),

WARRANTIES decimal(19,2),

[TICKET DISCOUNTS] decimal(19,2),

[TIRE TAX COLLECTED] decimal(19,2),

[GIFT CERTIFICATE RED] decimal(19,2),

COUPON decimal(19,2),

[GIFT CARD RED] decimal(19,2),

[GIFT CARD SALE] decimal(19,2),

DEBIT decimal(19,2),

[RTRN NONMRCH] decimal(19,2),

OVERSHORT decimal(19,2),

[DEPOSIT APP] decimal(19,2),

[DEPOSIT RCV] decimal(19,2),

PETTY decimal(19,2),

[NET FOOD] decimal(19,2),

[RETURN FOOD] decimal(19,2),

[NET CANDY] decimal(19,2),

[RETURN CANDY] decimal(19,2),

[NET SODA] decimal(19,2),

[RETURN SODA] decimal(19,2),

[NET WATER] decimal(19,2),

[RETURN WATER] decimal(19,2),

CRV decimal(19,2),

[TITLE FEE] decimal(19,2)

)

DECLARE @sql nvarchar(4000)

SELECT @sql =

'BULK INSERT dbo.SALES_IMPORT

FROM ''' + dbo.GET_INI('PATH_SALES_HOME') + dbo.GET_INI('FILE_SALES') + '''

WITH (FIELDTERMINATOR = '','',

ROWTERMINATOR = '''',

FIRSTROW = 2 )'

EXEC(@sql)

SELECT * FROM dbo.SALES_IMPORT

DROP TABLE dbo.tblSales_Details

CREATE TABLE dbo.tblSales_Details (

STORE int,

[DATE(yyyymmdd)] smalldatetime,

[TAXABLE SALES] decimal(19,2),

[NON TAXABLE SALES] decimal(19,2),

[GIFT CERTIFICATE SALES] decimal(19,2),

[SALES TAX COLLECTED] decimal(19,2),

[LABOR] decimal(19,2),

[RETURNS] decimal(19,2),

[NON TAXABLE RETURNS] decimal(19,2),

[NET SALES] decimal(19,2),

[MASTER CARD/VISA] decimal(19,2),

[DEPOSIT AMOUNT] decimal(19,2),

[DISCOVER] decimal(19,2),

[AMERICAN EXPRESS] decimal(19,2),

[VAC VOUCHER] decimal(19,2),

[ACCOUNT RECEIVABLE] decimal(19,2),

[CHECK REFUND] decimal(19,2),

[CUSTOMER COUNT] int,

[AVERAGE SALE] decimal(19,2),

[NON MERCHANDISE SALES] decimal(19,2),

DISCOUNTS decimal(19,2),

WARRANTIES decimal(19,2),

[TICKET DISCOUNTS] decimal(19,2),

[TIRE TAX COLLECTED] decimal(19,2),

[GIFT CERTIFICATE RED] decimal(19,2),

COUPON decimal(19,2),

[GIFT CARD RED] decimal(19,2),

[GIFT CARD SALE] decimal(19,2),

DEBIT decimal(19,2),

[RTRN NONMRCH] decimal(19,2),

OVERSHORT decimal(19,2),

[DEPOSIT APP] decimal(19,2),

[DEPOSIT RCV] decimal(19,2),

PETTY decimal(19,2),

[NET FOOD] decimal(19,2),

[RETURN FOOD] decimal(19,2),

[NET CANDY] decimal(19,2),

[RETURN CANDY] decimal(19,2),

[NET SODA] decimal(19,2),

[RETURN SODA] decimal(19,2),

[NET WATER] decimal(19,2),

[RETURN WATER] decimal(19,2),

CRV decimal(19,2),

[TITLE FEE] decimal(19,2)

)

INSERT INTO dbo.tblSales_Details

SELECT

STORE,

[DATE(yyyymmdd)],

[TAXABLE SALES],

[NON TAXABLE SALES],

[GIFT CERTIFICATE SALES],

[SALES TAX COLLECTED],

[LABOR],

[RETURNS],

[NON TAXABLE RETURNS],

[NET SALES],

[MASTER CARD/VISA],

[DEPOSIT AMOUNT],

[DISCOVER],

[AMERICAN EXPRESS],

[VAC VOUCHER],

[ACCOUNT RECEIVABLE],

[CHECK REFUND],

[CUSTOMER COUNT],

[AVERAGE SALE],

[NON MERCHANDISE SALES],

DISCOUNTS,

WARRANTIES,

[TICKET DISCOUNTS],

[TIRE TAX COLLECTED],

[GIFT CERTIFICATE RED],

COUPON,

[GIFT CARD RED],

[GIFT CARD SALE],

DEBIT,

[RTRN NONMRCH],

OVERSHORT,

[DEPOSIT APP],

[DEPOSIT RCV],

PETTY,

[NET FOOD],

[RETURN FOOD],

[NET CANDY],

[RETURN CANDY],

[NET SODA],

[RETURN SODA],

[NET WATER],

[RETURN WATER],

CRV,

[TITLE FEE]

FROM dbo.SALES_IMPORT

and I have attached one of the files to upload using this import process. Any input on why it is only importing 3/4ths of the file, would be appreciated.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 4

Eric Weinstein
dbo.GET_INI

Take it that this is a procedure developed by persons at your location, if not violating any company rule would you care the share that code?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Eric Weinstein Old Hand Points: 381 More actions · Answer 5

We have sql server 2000.

Here is the sql server code to import the data file:

DROP TABLE dbo.SALES_IMPORT

CREATE TABLE dbo.SALES_IMPORT (

STORE int,

[DATE(yyyymmdd)] smalldatetime,

[TAXABLE SALES] decimal(19,2),

[NON TAXABLE SALES] decimal(19,2),

[GIFT CERTIFICATE SALES] decimal(19,2),

[SALES TAX COLLECTED] decimal(19,2),

[LABOR] decimal(19,2),

[RETURNS] decimal(19,2),

[NON TAXABLE RETURNS] decimal(19,2),

[NET SALES] decimal(19,2),

[MASTER CARD/VISA] decimal(19,2),

[DEPOSIT AMOUNT] decimal(19,2),

[DISCOVER] decimal(19,2),

[AMERICAN EXPRESS] decimal(19,2),

[VAC VOUCHER] decimal(19,2),

[ACCOUNT RECEIVABLE] decimal(19,2),

[CHECK REFUND] decimal(19,2),

[CUSTOMER COUNT] int,

[AVERAGE SALE] decimal(19,2),

[NON MERCHANDISE SALES] decimal(19,2),

DISCOUNTS decimal(19,2),

WARRANTIES decimal(19,2),

[TICKET DISCOUNTS] decimal(19,2),

[TIRE TAX COLLECTED] decimal(19,2),

[GIFT CERTIFICATE RED] decimal(19,2),

COUPON decimal(19,2),

[GIFT CARD RED] decimal(19,2),

[GIFT CARD SALE] decimal(19,2),

DEBIT decimal(19,2),

[RTRN NONMRCH] decimal(19,2),

OVERSHORT decimal(19,2),

[DEPOSIT APP] decimal(19,2),

[DEPOSIT RCV] decimal(19,2),

PETTY decimal(19,2),

[NET FOOD] decimal(19,2),

[RETURN FOOD] decimal(19,2),

[NET CANDY] decimal(19,2),

[RETURN CANDY] decimal(19,2),

[NET SODA] decimal(19,2),

[RETURN SODA] decimal(19,2),

[NET WATER] decimal(19,2),

[RETURN WATER] decimal(19,2),

CRV decimal(19,2),

[TITLE FEE] decimal(19,2)

)

DECLARE @sql nvarchar(4000)

SELECT @sql =

'BULK INSERT dbo.SALES_IMPORT

FROM ''' + dbo.GET_INI('PATH_SALES_HOME') + dbo.GET_INI('FILE_SALES') + '''

WITH ( FIELDTERMINATOR = '','',

ROWTERMINATOR = '''',

FIRSTROW = 2 )'

EXEC(@sql)

SELECT * FROM dbo.SALES_IMPORT

DROP TABLE dbo.tblSales_Details

CREATE TABLE dbo.tblSales_Details (

STORE int,

[DATE(yyyymmdd)] smalldatetime,

[TAXABLE SALES] decimal(19,2),

[NON TAXABLE SALES] decimal(19,2),

[GIFT CERTIFICATE SALES] decimal(19,2),

[SALES TAX COLLECTED] decimal(19,2),

[LABOR] decimal(19,2),

[RETURNS] decimal(19,2),

[NON TAXABLE RETURNS] decimal(19,2),

[NET SALES] decimal(19,2),

[MASTER CARD/VISA] decimal(19,2),

[DEPOSIT AMOUNT] decimal(19,2),

[DISCOVER] decimal(19,2),

[AMERICAN EXPRESS] decimal(19,2),

[VAC VOUCHER] decimal(19,2),

[ACCOUNT RECEIVABLE] decimal(19,2),

[CHECK REFUND] decimal(19,2),

[CUSTOMER COUNT] int,

[AVERAGE SALE] decimal(19,2),

[NON MERCHANDISE SALES] decimal(19,2),

DISCOUNTS decimal(19,2),

WARRANTIES decimal(19,2),

[TICKET DISCOUNTS] decimal(19,2),

[TIRE TAX COLLECTED] decimal(19,2),

[GIFT CERTIFICATE RED] decimal(19,2),

COUPON decimal(19,2),

[GIFT CARD RED] decimal(19,2),

[GIFT CARD SALE] decimal(19,2),

DEBIT decimal(19,2),

[RTRN NONMRCH] decimal(19,2),

OVERSHORT decimal(19,2),

[DEPOSIT APP] decimal(19,2),

[DEPOSIT RCV] decimal(19,2),

PETTY decimal(19,2),

[NET FOOD] decimal(19,2),

[RETURN FOOD] decimal(19,2),

[NET CANDY] decimal(19,2),

[RETURN CANDY] decimal(19,2),

[NET SODA] decimal(19,2),

[RETURN SODA] decimal(19,2),

[NET WATER] decimal(19,2),

[RETURN WATER] decimal(19,2),

CRV decimal(19,2),

[TITLE FEE] decimal(19,2)

)

INSERT INTO dbo.tblSales_Details

SELECT

STORE,

[DATE(yyyymmdd)],

[TAXABLE SALES],

[NON TAXABLE SALES],

[GIFT CERTIFICATE SALES],

[SALES TAX COLLECTED],

[LABOR],

[RETURNS],

[NON TAXABLE RETURNS],

[NET SALES],

[MASTER CARD/VISA],

[DEPOSIT AMOUNT],

[DISCOVER],

[AMERICAN EXPRESS],

[VAC VOUCHER],

[ACCOUNT RECEIVABLE],

[CHECK REFUND],

[CUSTOMER COUNT],

[AVERAGE SALE],

[NON MERCHANDISE SALES],

DISCOUNTS,

WARRANTIES,

[TICKET DISCOUNTS],

[TIRE TAX COLLECTED],

[GIFT CERTIFICATE RED],

COUPON,

[GIFT CARD RED],

[GIFT CARD SALE],

DEBIT,

[RTRN NONMRCH],

OVERSHORT,

[DEPOSIT APP],

[DEPOSIT RCV],

PETTY,

[NET FOOD],

[RETURN FOOD],

[NET CANDY],

[RETURN CANDY],

[NET SODA],

[RETURN SODA],

[NET WATER],

[RETURN WATER],

CRV,

[TITLE FEE]

FROM dbo.SALES_IMPORT

Jeff Moden SSC Guru Points: 1004292 More actions · Answer 6

Eric,

Bit Bucket wanted you to post the code for the function that creates the path in the FROM clause of the BCP statement... not all the stuff you've already posted.

I just ran your code using your file (after changing the row terminator in the BCP to backslash N because the forum eats those" and I hardcoded the FROM clause of the BCP just because you haven't posted the function that makes that. Of course, it worked fine... I got all the rows.

So, there's only one thing left... there are two files (somewhere) and either the functions are returning different things based on who you are logged in as (probably not) or there's some sort of drive mapping that is different based on who you log in as.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

edwardelliott SSCommitted Points: 1863 More actions · Answer 7

Hi Eric,

To confirm which file (and whether it is all being read or not) use process monitor, (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) and setup a filter on the filename and re-run the import - does the right file get read and also does all the data get read?

If you monitor both accounts and verify you get the same results?

ed