bulk insert for dates csv file

I have been trying to bulk insert this file attached.  the table i have created is as follows

USE [mydatabase]
/****** Object:  Table [dbo].[PaySequenceDateExceptions]    Script Date: 04/08/2010 21:18:37 ******/
CREATE TABLE [dbo].[PaySequenceDateExceptions](
      [pay_sequence] [nvarchar](50) NULL,
      [payroll_transaction_date] [smalldatetime] NULL

the bulk insert I statement I am using is as follows:

delete dbo.PaySequenceDateExceptions
INSERT dbo.PaySequenceDateExceptions
FROM 'C:\PaySequenceDateExceptions.csv'

results i am getting is 0 rows updated

should I be using a different data type than smalldatetime

sample upload file attached

Amanda WalshawBusiness Solutions AnalsystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:

What is your CSV file look like? Just asking.

Most likely it is unexpected data causing your issue. You may want to separate the import phase from the data validation phase. You can do this by creating your target table as all varchar.

CREATE TABLE [dbo].[PaySequenceDateExceptions](
      [pay_sequence]  [nvarchar](50) NULL,
      [payroll_transaction_date]  [varchar(50)] NULL

If your import works to that table, it is a data issue. You should then validate the data to verify that it contains only valid dates before moving it to a table with the correct datatypes. If it does not load, re-examine your bulk insert setup.

Amanda WalshawBusiness Solutions AnalsystAuthor Commented:
I thought I added the spreadsheet in.
here it is
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

After seeing your data, I suggest that you format the dates in the most robust version, which is yyyymmdd. It is less likely to be misinterpreted by the database that way, especially with different localizations. Format yyyy/mm/dd will also work.

Uploading a CSV and giving a XLS as sample data .... there is a difference.
Also, you might want to omit the first line (with the column names).  
I generally use SSIS and a staging table (per teh comment in #30180274, above) for pulling data into a SS2005/2008 database.  That lets you get the data in and then not only look at how SS "saw" it but also perform data scrubs to resolve any data related issues.  Once the data is in and scrubbed, then a simple INSERT INTO . . . SELECT . . . FROM . . . query will move the, now valid, data to the actual target table.
Mark WillsTopic AdvisorCommented:
Absolutely agree with the staging table... and suggest the end of line is getting caught up with the date qualifier. It can be a problem...

If you really want to use the BULK INSERT, then you can try (and please note my path in the examples below is C:\ee\) :

-- use BULK insert to load it up

if object_id('Staging_PaySequenceDateExceptions','U') is not null drop table Staging_PaySequenceDateExceptions

CREATE TABLE [Staging_PaySequenceDateExceptions](
      [pay_sequence] varchar(100) NULL,
      [payroll_transaction_date] varchar(100) NULL)

BULK INSERT staging_PaySequenceDateExceptions
FROM 'C:\ee\PaySequenceDateExceptions.csv' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n',FIRSTROW=2)

SELECT convert(int,pay_sequence),convert(datetime, payroll_transaction_date,103) 
FROM staging_PaySequenceDateExceptions

-- Or, if it is already a correctly formatted CSV file, such as saving your sample spreadsheet as a csv,
-- then I often use the openrowset and the built in CSV drivers. Note this does require exclusive access.
-- this can overcome the BULK INSERT data conversion problems, and we can still select individual columns so we can "manage" them

SELECT * FROM OPENROWSET('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\','SELECT * FROM c:\ee\PaySequenceDateExceptions.csv')

-- and if really having a hard time, then I try to inspect using a linked server to see what SQL or the jet engine (or ACE if using later versions)
-- Use a linked server to the CSV file itself... But it MUST have column headings, and be a very, very, clean format using this method

EXEC sp_addlinkedserver MyCSV, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0','c:\ee\', NULL,'Text'
EXEC sp_addlinkedsrvlogin MyCSV, FALSE, NULL,NULL,NULL
EXEC sp_tables_ex MyCSV    -- note how it picks up any / all txt and csv files in that folder
EXEC sp_columns_ex MyCSV,PaySequenceDateExceptions#csv  -- now we can inspect the columns of that table
select * from MyCSV...PaySequenceDateExceptions#csv    -- four part identifier using table name from above
sp_dropserver 'MyCSV', 'droplogins';

-- you could simply create the linked server and leave it there without having to drop every time, 
-- or keep it fairly dynamic by creating and dropping each time as shown above - up to you.

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.