Bulk Insert/BCP Format File + Text Qualifiers

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

Transact-SQL (2005)

Bulk Insert/BCP Format File + Text Qualifiers

Author

Topic

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-06 : 06:32:47

Hi,

Please help!! I have an issue bulk inserting with text qualifiers. I have an example which works (Example 1) but cant get it to work for actual file - Example 2.

------------------------
Example 1
------------------------
File: 1bcp.txt. With the following contents:
"a",1,"b","c"
"d",2,"e","f"
"g",3,"h","i"

Format file:
9.0
5
1 SQLCHAR 0 1 "\"" 0 quote ""
2 SQLCHAR 0 0 "\"," 1 s Latin1_General_CI_AS
3 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
4 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
5 SQLCHAR 0 0 "\"\r\n" 4 u Latin1_General_CI_AS

SQL
drop table ##a

create table ##a (s varchar(5), i int, t varchar(5), u varchar(1000))

exec master..xp_cmdshell 'bcp ##a in c:\BulkInsert\1bcp.txt -fc:\BulkInsert\1bcpfmt.txt -T'

select * from ##a

------------------------
Example 2
------------------------
File: 1bcp.txt. With the following contents:
a,1,"b","c"
d,2,"e","f"
g,3,"h","i"

Please help with format file for dealing with above file.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 06:45:19

try with


9.0
5
1 SQLCHAR 0 0 "," 1 s Latin1_General_CI_AS
2 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
3 SQLCHAR 0 0 "\"\r\n"" 4 u Latin1_General_CI_AS

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-06 : 06:49:26

this doesnt work and looks like it only mapping 3 cols.
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 07:01:16

did you try edited solution?

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-06 : 07:11:14

yes - still doesnt work. have you tried it ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 07:12:16

quote:
Originally posted by davidagnew37

yes - still doesnt work. have you tried it ?

I'm not near sql box so cant test it

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-06 : 07:56:11

thats figures!! ...got it.
thanks

9.0
4
1 SQLCHAR 0 0 "," 1 s Latin1_General_CI_AS
2 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
4 SQLCHAR 0 0 "\"\r\n" 4 u Latin1_General_CI_AS

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 09:42:02

quote:
Originally posted by davidagnew37

thats figures!! ...got it.
thanks

9.0
4
1 SQLCHAR 0 0 "," 1 s Latin1_General_CI_AS
2 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 0 "\",\"" 3 t Latin1_General_CI_AS
4 SQLCHAR 0 0 "\"\r\n" 4 u Latin1_General_CI_AS

Ok Great

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-06 : 10:23:22

another question...

The bulk insert fails if one the the column field values which we have specified to have text qualifiers - has a NULL value - and therefore no text qualifier. i.e. - see second line. because its NULL and not an empty string - it has no "".

a,1,"b","c"
d,2,,"f"
g,3,"h","i"

any suggestions much appreciated. I think ive hit a dead end here????

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-06 : 10:32:15

quote:
Originally posted by davidagnew37

another question...

The bulk insert fails if one the the column field values which we have specified to have text qualifiers - has a NULL value - and therefore no text qualifier. i.e. - see second line. because its NULL and not an empty string - it has no "".

a,1,"b","c"
d,2,,"f"
g,3,"h","i"

any suggestions much appreciated. I think ive hit a dead end here????

Try specifying a prefix length for column with NULLs

3 SQLCHAR 2 0 "\",\"" 3 t Latin1_General_CI_AS

davidagnew37
Starting Member

33 Posts

Posted - 2010-01-14 : 07:28:57

Still no joy with this. Please help - or let me know if not possible.
thanks

file
-------------------------------
a,1,"b","c"
d,2,,"f"
g,3,"h","i"

format file
-------------------------------
9.0
4
1 SQLCHAR 0 0 "," 1 s Latin1_General_CI_AS
2 SQLCHAR 0 0 ",\"" 2 i Latin1_General_CI_AS
3 SQLCHAR 0 1 "\"," 3 t Latin1_General_CI_AS
4 SQLCHAR 0 0 "\r\n" 4 u Latin1_General_CI_AS

sql
-----------------------------------
drop table ##a
create table ##a (s varchar(5), i int, t varchar(5), u varchar(1000))

--select * from ##a
truncate table ##a
exec master..xp_cmdshell 'bcp ##a in c:\BulkInsert\4bcp.txt -fc:\BulkInsert\4bcpfmt.txt -T'
select * from ##a

bulk insert ##a from 'c:\BulkInsert\4bcp.txt' with (formatfile = 'c:\BulkInsert\4bcpfmt.txt')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources