BULK INSERT (X)

Hi,
I have to import a file, using BULK INSERT, into a #TEMP_TABLE - the file
has the following contents/format (5 cols):
"B09Z003 ","0058-003-01 ", 57, 0," "
"B09Z005 ","0053-003-R1 ", 19, 0," "
"B09Z008 ","0054-2 ", 0, 0," "
etc. etc.
Please be aware, that not all col's use quote as text qualifiers.
I'm trying with the following formatfile, but it doesn't work:
8.0
10
1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
3 SQLCHAR 0 20 "\"," 3 COL003 ""
4 SQLCHAR 0 8 "," 4 COL004 ""
5 SQLCHAR 0 8 ",\"" 5 COL005 ""
6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
What am I doing wrong?
Also, the last col is a date (if it's not all <space> chars) in the format
12/12/2004 - could this give a problem (the /-char or data-formats) of any
kind?
Best regards
JakobDoes anyone have a clue?
Thanx
Jakob
""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
news:OVFVCGKBEHA.496@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have to import a file, using BULK INSERT, into a #TEMP_TABLE - the file
> has the following contents/format (5 cols):
> "B09Z003 ","0058-003-01 ", 57, 0," "
> "B09Z005 ","0053-003-R1 ", 19, 0," "
> "B09Z008 ","0054-2 ", 0, 0," "
> etc. etc.
> Please be aware, that not all col's use quote as text qualifiers.
> I'm trying with the following formatfile, but it doesn't work:
> 8.0
> 10
> 1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
> 2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
> 3 SQLCHAR 0 20 "\"," 3 COL003 ""
> 4 SQLCHAR 0 8 "," 4 COL004 ""
> 5 SQLCHAR 0 8 ",\"" 5 COL005 ""
> 6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
> What am I doing wrong?
> Also, the last col is a date (if it's not all <space> chars) in the format
> 12/12/2004 - could this give a problem (the /-char or data-formats) of any
> kind?
> Best regards
> Jakob
>|||Jakob,
This works just fine. So, what error you're getting. Also, check your format
file. You might specify more columns than what your data file has (i.e. 10
instead of 6 based on your original post).
/*
--txt.txt
"B09Z003 ","0058-003-01 ", 57, 0," "
"B09Z005 ","0053-003-R1 ", 19, 0," "
"B09Z008 ","0054-2 ", 0, 0," "
*/
/*
--fmt.fmt
8.0
6
1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
3 SQLCHAR 0 20 "\"," 3 COL003 ""
4 SQLCHAR 0 8 "," 4 COL004 ""
5 SQLCHAR 0 8 ",\"" 5 COL005 ""
6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
*/
create table #tmp(i int identity,col002 varchar(12),col003 varchar(20),col00
4
varchar(8),col005 varchar(8),col006 varchar(8))
go
bulk insert #tmp
from 'c:\txt.txt'
with(formatfile='c:\fmt.fmt')
go
select * from #tmp
go
drop table #tmp
go
-oj
http://www.rac4sql.net
""Sokrates"" <somebody@.somewhere.earth> wrote in message
news:ePUpm8aBEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Does anyone have a clue?
> Thanx
> Jakob
> ""Sokrates"" <somebody@.somewhere.earth> skrev i en meddelelse
> news:OVFVCGKBEHA.496@.TK2MSFTNGP12.phx.gbl...
>|||THANX!
It works now!
BR
Jakob
"oj" <nospam_ojngo@.home.com> skrev i en meddelelse
news:%23NHtjFbBEHA.1544@.TK2MSFTNGP09.phx.gbl...
> Jakob,
> This works just fine. So, what error you're getting. Also, check your
format
> file. You might specify more columns than what your data file has (i.e. 10
> instead of 6 based on your original post).
> /*
> --txt.txt
> "B09Z003 ","0058-003-01 ", 57, 0," "
> "B09Z005 ","0053-003-R1 ", 19, 0," "
> "B09Z008 ","0054-2 ", 0, 0," "
> */
> /*
> --fmt.fmt
> 8.0
> 6
> 1 SQLCHAR 0 0 "\"" 1 FIRSTQ ""
> 2 SQLCHAR 0 12 "\",\"" 2 COL002 ""
> 3 SQLCHAR 0 20 "\"," 3 COL003 ""
> 4 SQLCHAR 0 8 "," 4 COL004 ""
> 5 SQLCHAR 0 8 ",\"" 5 COL005 ""
> 6 SQLCHAR 0 8 "\"\r\n" 6 COL006 ""
> */
> create table #tmp(i int identity,col002 varchar(12),col003
varchar(20),col004
> varchar(8),col005 varchar(8),col006 varchar(8))
> go
> bulk insert #tmp
> from 'c:\txt.txt'
> with(formatfile='c:\fmt.fmt')
> go
> select * from #tmp
> go
> drop table #tmp
> go
>
>
> --
> -oj
> http://www.rac4sql.net
>
> ""Sokrates"" <somebody@.somewhere.earth> wrote in message
> news:ePUpm8aBEHA.2600@.TK2MSFTNGP09.phx.gbl...
file
format
any
>