Bulk insert from txt file less data than columns

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 2005 Forums

SSIS and Import/Export (2005)

Bulk insert from txt file less data than columns

Author

Topic

rimpampa
Starting Member

2 Posts

Posted - 2007-05-23 : 08:36:09

Hi, I´m trying to bulk insert files that looks like this:

aaaa,bbb,dddd,
ccc,dfd,tghj,

each file can have up to 10 data fileds per line, and each file will have same number of data fileds in particular file, let´s say 3 like above. Second file could have let´s say 10 and that is maximum.

I read the file and insert data with fieldterminator in temp table from witch I insert data to other tables regarding some parameters inside.

Now problem is:
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)".

That is because I´m trying to insert 3 fields of data in temporary table which is made of 10 columns (It have to be 10 because next file could have 10 fileds of data). If the temp table has same number of columns like text file has data fields than it works.

What is solution for this problem?
Can I bulk insert NULL in columns for which I don't have data?

I can also import each line of text file to one column (with delimiter inside) but than I don´t know how to insert that data to correct tables or even to one table but to seperate data fields to columns with fieldterminator which is , in this case.

I'm new to SQL and I would apriciate any help.
Thank you

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-05-23 : 09:57:43

See if this helps: [url]http://msdn2.microsoft.com/en-us/library/ms179250.aspx[/url]

Mark

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-05-23 : 10:00:34

If column allows null value, you don't need put data for that column. Can you post table schema and bulk insert command?

rimpampa
Starting Member

2 Posts

Posted - 2007-05-23 : 10:17:28

There is a lot of code inside because I´m testing diferent things and solutions, it is confusing somethimes for myself, so here are the parts you asked for:

--Creating temp table
CREATE TABLE ##Import(
[s] [nvarchar](100) NULL,
[s2] [nvarchar](100) NULL,
[s3] [nvarchar](100) NULL,
[s4] [nvarchar](100) NULL,
[s5] [nvarchar](100) NULL,
[s6] [nvarchar](100) NULL,
[s7] [nvarchar](100) NULL,
[s8] [nvarchar](100) NULL,
[s9] [nvarchar](100) NULL,
[s10] [nvarchar](100) NULL
)

--Importing file
select @cmd = 'dir /B ' + @FilePath + @FileNameMask
delete #Dir
insert #Dir exec master..xp_cmdshell @cmd

delete #Dir where s is null or s like '%not found%'
while exists (select * from #Dir)
begin
select @FileName = min(s) from #Dir
select @File = @FilePath + @FileName

select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (FIELDTERMINATOR='','''
select @cmd = @cmd + ',ROWTERMINATOR = '',\n'')'

--Inserting from temp table
INSERT EXIT
SELECT * FROM ##Import WHERE s3 = 'some data'

This is a script that I found and trying to moderate it to fit my needs. If you realy need all the code I will clean it up and post it.
Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources