Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 BULK INSERT - Last column all NULL

Author  Topic 

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2014-08-17 : 23:25:02
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



sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-19 : 07:31:59
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

47 Posts

Posted - 2014-08-19 : 08:58:20
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

352 Posts

Posted - 2014-08-19 : 20:37:13
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
   

- Advertisement -