Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 721
  • Last Modified:

Bulk Insert Help

Hi Everyone

I am trying to bulk insert the attached file into a table I am creating with the code below.  I am getting errors with the Primary key being the wrong data type.

i don't know how to solve this issue, I was hoping someone could iron out the code to allow the bulk insert to be successful.  I had to change the file extension to allow uploads.  The extension is .csv


USE master
Go

DROP DATABASE      ChartOfAccounts
CREATE DATABASE ChartOfAccounts
ON
(NAME = 'ChartOfAccounts',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ChartOfAccounts.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
(NAME = 'ChartOfAccountsLog',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ChartOfAccounts.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )

GO



USE ChartOfAccounts
GO

DROP TABLE ChartOfAccounts
CREATE TABLE ChartOfAccounts
      (
      [Account] int NOT NULL      PRIMARY KEY CLUSTERED IDENTITY (1,1)
      , [Description]      varchar (128) NULL
      , [Short_Description]      varchar(512)      NOT NULL
      , [Balance]       Char (128)            NULL
      )
GO

BULK INSERT ChartOfAccounts
        FROM 'C:\Documents and Settings\Administrator\My Documents\KudlerAccounts\KudlerCOA.csv'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO


BULK
INSERT ChartOfAccounts
        FROM 'C:\Documents and Settings\Administrator\My Documents\KudlerAccounts\KudlerCOA.csv'
            WITH
    (
                FIELDTERMINATOR = ',',
                ROWTERMINATOR = '\n'
    )
GO

COA.csv.txt
0
salberta
Asked:
salberta
  • 8
  • 2
  • 2
  • +2
1 Solution
 
jmoss111Commented:
declare the identity as bigint; your first row is way bigger than integer
0
 
k_murli_krishnaCommented:
See the type of data in CSV file. For first column i.e. Account it should be plain integer without decimal point and any type of quotes or charcters other than digits. If you are providing values for this PK, then IDENTITY insert has to be turned on. Make sure the data is 1 2 3 4 ......................
If data for column is missing then turn IDENTITY insert off. I think there is @@IDENTITY global variable for this. Check what is the default and change it to the only other state if required.
But if it is a purely data type mismatch between data in .CSV file and column, then maybe due to some default/missing option in BULK INSERT command which controls the data load operation, single/double quotes are dynamically enclosing the PK data.
It also maybe that your column data type is int and there is a value exceeding its upper limit in .CSV file. You may also happen to be inserting NULL into NOT NULL column. Also, identity property may be having ORDER/NOORDER just like in a sequence. Check CYCLE/NOCYCLE and MAXVALUE as well.
All the best.

-- k_murli_krishna
0
 
jmoss111Commented:
There are other problems: the .csv file in some cases only accounts for two or three columns and should account for all four in the file.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
jmoss111Commented:
The first account number is 100001 and cant be held in an integer column. You can't insert into an IDENTITY without turning it off. SET IDENTITY_INSERT can be used to disable the IDENTITY property of a column by enabling values to be explicitly inserted.
0
 
jmoss111Commented:
Also you need to add FIRSTROW = 2, because the BULK INSERT will  fail when trying to insert the first row into the data due to the fact that you're attempting to insert text into a numeric column.
0
 
k_murli_krishnaCommented:
jmoss111, you are correct about less data in some rows and IDENTITY insert. Salberta, there is a row in .CSV file having data as ,, i.e. there is no data at all & hence no data for PK which is a real problem. After solving all else based on our comments, you will need to clear this 1 last one since apart from PK, Short_Description is also NOT NULL.
0
 
salbertaAuthor Commented:
Hi Everyone-
 
I am very new to this,  Working code or at least how you would perform this action would be apprecitated.    i am a Systems Administrator and was handed SQL and this project.     The first field needs to be a primary.  If there is duplicates I can find them.
or if you have a way to search for dupes, that would help
I can create and populate the table from the bulk insert statement and from an import.  i cannot change and table values howerver, due to null errors.
I have books online and I am pouring througth them.  
0
 
jmoss111Commented:
salberta,

See code attached.  I made the changes that I told you about in earlier posts, I.E. :

removed IDENTITY (1,1)
added  ,FIRSTROW = 2,

and added a ", " at the end of the first 10 rows to get the correct # of columns and it worked great.

Jim

USE master
Go
 
--DROP DATABASE      ChartOfAccounts
CREATE DATABASE ChartOfAccounts
ON
(NAME = 'ChartOfAccounts',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ChartOfAccounts.mdf',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
(NAME = 'ChartOfAccountsLog',
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ChartOfAccounts.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
 
GO
 
 
 
USE ChartOfAccounts
GO
 
DROP TABLE ChartOfAccounts
CREATE TABLE ChartOfAccounts
      (
      [Account] int NOT NULL      PRIMARY KEY CLUSTERED
      , [Description]      varchar (128) NULL
      , [Short_Description]      varchar(512)      NOT NULL
      , [Balance]       Char (128)            NULL
      )
GO
 
BULK INSERT ChartOfAccounts
        FROM 'C:\coa.txt'
            WITH
    (
                FIELDTERMINATOR = ',',FIRSTROW = 2,
                ROWTERMINATOR = '\n'
    )
GO

Open in new window

0
 
jmoss111Commented:
You can fix the text file, right?
0
 
Mark WillsTopic AdvisorCommented:
FIRSTROW = 2, identity_insert ON, and check for dupes. Use a staging table...



-- first do a staging table
 
if object_id(N'ChartOfAccounts_staging','U') is not null drop table ChartOfAccounts_staging
 
-- create a staging table
CREATE TABLE ChartOfAccounts_Staging
      (
      [Account] int NULL   --  PRIMARY KEY CLUSTERED  --IDENTITY (1,1)
      , [Description]      varchar (128) NULL
      , [Short_Description]      varchar(512)      NULL
      , [Balance]       Char (128)            NULL
      )
GO
 
-- load staging 
 
BULK INSERT ChartOfAccounts_staging
        FROM 'C:\ee\COA.csv.txt'
            WITH
    (
                FIELDTERMINATOR = ',',
                FIRSTROW = 2,
                ROWTERMINATOR = '\n'
    )
GO
 
-- show dupes - maybe remove dupes
 
select * from chartofaccounts_staging
where account in 
(select account from chartofaccounts_staging group by account having count(*) > 1)
order by account
GO
 
-- now populate from staging...
 
if object_id(N'ChartOfAccounts','U') is not null drop table ChartOfAccounts
GO
 
-- create new home
 
CREATE TABLE ChartOfAccounts
      (
      [Account] int NOT NULL      PRIMARY KEY CLUSTERED IDENTITY (1,1)
      , [Description]      varchar (128) NULL
      , [Short_Description]      varchar(512)      NOT NULL
      , [Balance]       Char (128)            NULL
      )
GO
 
-- allow identity updates 
 
set identity_insert ChartOfAccounts ON
GO
 
-- build exclude dupes (any instance)
 
insert chartofaccounts (account,description,short_description,balance)
select distinct account,description,short_description,balance
from chartofaccounts_staging
where isnull(account,0) > 0
and len(isnull(short_description,'')) > 0
and not exists (select * from chartofaccounts c where c.account = chartofaccounts_staging.account)
and account not in (select account from chartofaccounts_staging group by account having count(*) > 1)
 
-- turn identity control back on
 
set identity_insert ChartOfAccounts OFF
GO

Open in new window

0
 
jmoss111Commented:
Like mark_wills said, never import directly into a production table; always use a staging  table.
0
 
salbertaAuthor Commented:
Hi jmoss111-
 
It seems that even a simple mod of the lines of the file are escaping me.   Could you please give me an example.
Sorry to be a bother.
 
salberta
0
 
jmoss111Commented:
Your text file contains 4 columns, some rows of data only contain two columns but you don't have the extra delimiters to fill out the row, IE two ,,
0
 
nmcdermaidCommented:
To extend jmoss111 comments, line 35 just has ,, in it. This indicates three columns (seperated by commas). Every other line has three commas in it, indicating four columns.
You need to work out what is meant to be at line 35. It definitely should not be ,,
Looking at the data, there's no space in the numbers between 121011 and 121012, so I'm guessing someone accidentally typed it in, and it should be removed.
 
Also, do you have a reason to make that column an IDENTITY?
You usually create an IDENTITY column so that if you insert data into it, it will automatically put in an incrementing value in the field for you.
So after you have successfully imported this daat, do you expect to be adding more data to the table, and have the table automatically generate an Account for you? if not, then you shouldn't be using the Identity property in your table.
0
 
Mark WillsTopic AdvisorCommented:
hi salberta - have you tried my solution ? it does work as you want - just uses a staging table in between - is there any problems with that approach ? The only question I have is that it will ignore dupes in the staging table - altogether (ie not even 1 instance wins),  and you probably want to report those as errors for manual intervention rather than trying to decide which CofA entry becomes "real". But if there is a rule to help determine the "real" entry in those dupes, than can add that in...
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 8
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now