BULK INSERT - Last column all NULL

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!

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources