SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 BULK INSERT - Last column all NULL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 08/17/2014 :  23:25:02  Show Profile  Reply with Quote
I have the following table. If one or more of the rows for the last column contain a value then this code works. If all values in the csv for the last column have no value then it fails? What's interesting is if I make say Program field all with no value it also works.

Create Table #t ([EntryDate] varchar(20), [Name] varchar(100),[ProgramDate] varchar(10), [Program] varchar(100), [Event] varchar(100), [Open_Over_35_Age_Group] varchar(100) NULL)

Set @sql='BULK INSERT #t
FROM "'+@path_name+'"
WITH
(DATAFILETYPE=''char'', FIELDTERMINATOR='','', ROWTERMINATOR = ''\n'', FIRSTROW = 1, KEEPNULLS);'


Print @sql
Exec(@sql)



c:\SARWCStartList\SARWC_120814.csv
BULK INSERT #t
FROM "c:\SARWCStartList\SARWC_120814.csv"
WITH
(DATAFILETYPE='char', FIELDTERMINATOR=',', ROWTERMINATOR = '\n', FIRSTROW = 1, KEEPNULLS);
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


Examples;

Works;

EntryDate Name Bib No. ProgramDate Program Event Open_Over_35_Age_Group
06/08/2014 12:14 Alix 1 Harlington 848 2014-08-09 Timed Walks 1 Hour Open

Works;

EntryDate Name Bib No. ProgramDate Program Event Open_Over_35_Age_Group
06/08/2014 12:14 Alix 1 Harlington 848 2014-08-09 Timed Walks NULL Open


Fails where input data (csv) is;


06/08/2014 12:14 Alix 1 Harlington 09/08/2014 Timed Walks 1 Hour




Edited by - harlingtonthewizard on 08/18/2014 01:07:44

sz1
Constraint Violating Yak Guru

United Kingdom
443 Posts

Posted - 08/19/2014 :  07:31:59  Show Profile  Reply with Quote
The error can occur when you have a different number of delimited fields in my CSV than columns in the table . Check if you have the right number of fields in csv to match the table.

We are the creators of our own reality!
Go to Top of Page

Mar
Starting Member

44 Posts

Posted - 08/19/2014 :  08:58:20  Show Profile  Reply with Quote
sz1 is right. Look at the one that fails, it has between 1 and 2 less fields than the ones that work.
Go to Top of Page

harlingtonthewizard
Constraint Violating Yak Guru

Australia
352 Posts

Posted - 08/19/2014 :  20:37:13  Show Profile  Reply with Quote
I fixed this issue by including a header row in row 1 of the csv file;

EntryDate Name ProgramDate Program Event Open_Over_35_Age_Group
06/08/2014 12:14 Alix 1 09/08/2014 Timed Walks 1 Hour
06/08/2014 12:14 Alix 2 09/08/2014 Timed Walks 1 Hour
06/08/2014 12:14 Alix 3 09/08/2014 Timed Walks 30 Minutes
06/08/2014 12:14 Alix 4 09/08/2014 Timed Walks 30 Minutes
06/08/2014 12:14 Alix 5 09/08/2014 Timed Walks 15 Minutes


and the following code;

Create Table #t ([EntryDate] varchar(20), [Name] varchar(100), [ProgramDate] varchar(10), [Program] varchar(100), [Event] varchar(100), [Open_Over_35_Age_Group] varchar(100))

Set @sql='BULK INSERT #t
FROM "'+@path_name+'"
WITH
(DATAFILETYPE=''char'', FIELDTERMINATOR='','', ROWTERMINATOR = ''\n'', FIRSTROW = 2, KEEPNULLS);'
--, ERRORFILE = ''c:\SARWCStartList\Error.log''
--, FORMATFILE = ''c:\SARWCStartList\Format.Fmt''


I now get this with my csv file where all values in the last column are NULL.

EntryDate Name Bib No. ProgramDate Program Event Open_Over_35_Age_Group
06/08/2014 12:14 Alix 1 848 2014-08-09 Timed Walks 1 Hour NULL
06/08/2014 12:14 Alix 2 848 2014-08-09 Timed Walks 1 Hour NULL
06/08/2014 12:14 Alix 3 848 2014-08-09 Timed Walks 30 Minutes NULL
06/08/2014 12:14 Alix 4 848 2014-08-09 Timed Walks 30 Minutes NULL
06/08/2014 12:14 Alix 5 848 2014-08-09 Timed Walks 15 Minutes NULL
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000