With bulk insert I am trying to insert data.
Data is comma delimited.
And strings containing comma's are double quoted.
See below for the command and the format.
I am doing something wrong because strings with
comma's in them get split over more fields.
This should be simple but haven't found the answer yet.
What should I change in de command, or in the format file?
Thanks in advance,
Ben Brugman
The command I use from the 'Query Analyser' in 2005.
BULK INSERT IMP FROM 'D:\folder\import.txt'
WITH (FORMATFILE = 'D:\folder\format.xml' );
The format file:
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="01" NAME="Field01
" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="02" NAME="Field02
" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="51" NAME="Field03
" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="52" NAME="Field04
" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>Ben
> With bulk insert I am trying to insert data.
> Data is comma delimited.
> And strings containing comma's are double quoted.
I did not create a XML file ,howere this works for just great
create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
go
/*
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
8.0
4
1 SQLCHAR 0 0 "\"" 0 first_quote ""
2 SQLCHAR 0 30 "\",\"" 1 c1 ""
3 SQLCHAR 0 30 "\",\"" 2 c2 ""
4 SQLCHAR 0 30 "\r\n" 3 c3 ""
*/
bulk insert dbo.tb1
from 'c:\txt_file.txt'
with (formatfile='c:\fmt_file.fmt')
go
select *
from dbo.tb
go
drop table dbo.tb
"ben brugman" <ben@.niethier.nl> wrote in message
news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
> Hello,
> With bulk insert I am trying to insert data.
> Data is comma delimited.
> And strings containing comma's are double quoted.
> See below for the command and the format.
> I am doing something wrong because strings with
> comma's in them get split over more fields.
> This should be simple but haven't found the answer yet.
> What should I change in de command, or in the format file?
> Thanks in advance,
> Ben Brugman
> The command I use from the 'Query Analyser' in 2005.
> BULK INSERT IMP FROM 'D:\folder\import.txt'
> WITH (FORMATFILE = 'D:\folder\format.xml' );
> The format file:
> <?xml version="1.0"?>
> <BCPFORMAT
> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
> xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
>
> <FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> <FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> <FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> <FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="01" NAME="Field01 " xsi:type="SQLNVARCHAR"/>
> <COLUMN SOURCE="02" NAME="Field02 " xsi:type="SQLNVARCHAR"/>
> <COLUMN SOURCE="51" NAME="Field03 " xsi:type="SQLNVARCHAR"/>
> <COLUMN SOURCE="52" NAME="Field04 " xsi:type="SQLNVARCHAR"/>
> </ROW>
> </BCPFORMAT>
>
>|||Hello Uri,
Thanks for the quick reply,
I'll try to understand and use your method.
In your example does the fmt_file.fmt start with the line starting with 4 or
the line starting with 8?
Tthe text file is the file with the 4 lines with the A/B/C/D examples I
assume?
Not all my fields are double quoted, not all strings are double quoted (only
the one's containing a comma), is this a problem?
You have three fields, but I see four definitions, how does this work?
Sorry to ask so many questions, but I did try to read up on the XML format
(not succesfully yet), so switching to another format is no problem, but
I'll like to understand the 'definitions' so that I do not run into new
problems, which I can not solve by myself.
Also next to get the system working (with your example), I would like to
know how to work in the XML format and still be able to insert double quoted
strings when not all strings are double quoted.
Thanks for your time and attention,
Ben Brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>
> I did not create a XML file ,howere this works for just great
>
> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
> go
> /*
> "A","ISSUE","Misc Material Issue"
> "B","MOVE","Misc Material Move"
> "C","POST","Misc Post Material"
> "D","MOVE","Misc Material Move"
> 8.0
> 4
> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
> */
> bulk insert dbo.tb1
> from 'c:\txt_file.txt'
> with (formatfile='c:\fmt_file.fmt')
> go
> select *
> from dbo.tb
> go
> drop table dbo.tb
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>> See below for the command and the format.
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>> Thanks in advance,
>> Ben Brugman
>> The command I use from the 'Query Analyser' in 2005.
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE = 'D:\folder\format.xml' );
>> The format file:
>> <?xml version="1.0"?>
>> <BCPFORMAT
>> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
>> xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>> <FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE="01" NAME="Field01 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="02" NAME="Field02 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="51" NAME="Field03 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="52" NAME="Field04 " xsi:type="SQLNVARCHAR"/>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>|||Ben
Remove it.
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
Did you try using READ method in .NET to get the file.?
"ben brugman" <ben@.niethier.nl> wrote in message
news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
> Hello Uri,
> Thanks for the quick reply,
> I'll try to understand and use your method.
> In your example does the fmt_file.fmt start with the line starting with 4
> or the line starting with 8?
> Tthe text file is the file with the 4 lines with the A/B/C/D examples I
> assume?
> Not all my fields are double quoted, not all strings are double quoted
> (only the one's containing a comma), is this a problem?
> You have three fields, but I see four definitions, how does this work?
> Sorry to ask so many questions, but I did try to read up on the XML format
> (not succesfully yet), so switching to another format is no problem, but
> I'll like to understand the 'definitions' so that I do not run into new
> problems, which I can not solve by myself.
> Also next to get the system working (with your example), I would like to
> know how to work in the XML format and still be able to insert double
> quoted strings when not all strings are double quoted.
> Thanks for your time and attention,
> Ben Brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> I did not create a XML file ,howere this works for just great
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
>> go
>> /*
>> "A","ISSUE","Misc Material Issue"
>> "B","MOVE","Misc Material Move"
>> "C","POST","Misc Post Material"
>> "D","MOVE","Misc Material Move"
>> 8.0
>> 4
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>> */
>> bulk insert dbo.tb1
>> from 'c:\txt_file.txt'
>> with (formatfile='c:\fmt_file.fmt')
>> go
>> select *
>> from dbo.tb
>> go
>> drop table dbo.tb
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message
>> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>> See below for the command and the format.
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>> Thanks in advance,
>> Ben Brugman
>> The command I use from the 'Query Analyser' in 2005.
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE = 'D:\folder\format.xml' );
>> The format file:
>> <?xml version="1.0"?>
>> <BCPFORMAT
>> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
>> xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>> <FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE="01" NAME="Field01 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="02" NAME="Field02 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="51" NAME="Field03 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="52" NAME="Field04 " xsi:type="SQLNVARCHAR"/>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0021_01C82618.1ED36460
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first character in the first column.
- There is a double quote as the last character in the last column.
- It can not cope with none quoted strings, I get error from that.
It did cope correctly with a comma in a string.
Below a detailled result, mainly your code and example.
The word 'voorbeeld' is just the Dutch word for 'example'.
Maybe I misunderstood your example, so I have included the files
as I understood them to be.
Did you try using READ method in .NET to get the file.?
I do not understand this question, so I assume the anwser is no.
I copied the example from the message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
----=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
delete dbo.voorbeeld_tb1
bulk insert dbo.voorbeeld_tb1
from 'c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
----=--
The used format file c:\voorbeeld_format.fmt
----=--
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
----=--
the content file c:\voorbeeld_content.txt :
----=--
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
"A","ISSUE","Misc , extra"
----=--
select * from dbo.voorbeeld_tb1 Delivers
----=--
c1 c2 c3
-- -- =--
"A ISSUE Misc =Material Issue"
"B MOVE Misc =Material Move"
"C POST Misc Post =Material"
"D MOVE Misc =Material Move"
"A ISSUE Misc , =extra"
(5 row(s) affected)
----=--
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Ben
> Remove it.
> 8.0
> 3
> 1 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 3 SQLCHAR 0 30 "\r\n" 3 c3 ""
> > > Did you try using READ method in .NET to get the file.?
> > "ben brugman" <ben@.niethier.nl> wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello Uri,
>> Thanks for the quick reply,
>> I'll try to understand and use your method.
>> In your example does the fmt_file.fmt start with the line starting =with 4 >> or the line starting with 8?
>> Tthe text file is the file with the 4 lines with the A/B/C/D examples =I >> assume?
>> Not all my fields are double quoted, not all strings are double =quoted >> (only the one's containing a comma), is this a problem?
>> You have three fields, but I see four definitions, how does this =work?
>> Sorry to ask so many questions, but I did try to read up on the XML =format >> (not succesfully yet), so switching to another format is no problem, =but >> I'll like to understand the 'definitions' so that I do not run into =new >> problems, which I can not solve by myself.
>> Also next to get the system working (with your example), I would like =to >> know how to work in the XML format and still be able to insert double =
>> quoted strings when not all strings are double quoted.
>> Thanks for your time and attention,
>> Ben Brugman
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message >> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> I did not create a XML file ,howere this works for just great
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
>> go
>> /*
>> "A","ISSUE","Misc Material Issue"
>> "B","MOVE","Misc Material Move"
>> "C","POST","Misc Post Material"
>> "D","MOVE","Misc Material Move"
>> 8.0
>> 4
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>> */
>> bulk insert dbo.tb1
>> from 'c:\txt_file.txt'
>> with (formatfile=3D'c:\fmt_file.fmt')
>> go
>> select *
>> from dbo.tb
>> go
>> drop table dbo.tb
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message >> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>> See below for the command and the format.
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>> Thanks in advance,
>> Ben Brugman
>> The command I use from the 'Query Analyser' in 2005.
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE =3D 'D:\folder\format.xml' );
>> The format file:
>> <?xml version=3D"1.0"?>
>> <BCPFORMAT >> =xmlns=3D"http://schemas.microsoft.com/sqlserver/2004/bulkload/format"=20
>> xmlns:xsi=3D"">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>> <FIELD ID=3D"01" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"02" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"51" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"52" xsi:type=3D"CharTerm" TERMINATOR=3D",\r\n" >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE=3D"01" NAME=3D"Field01 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"02" NAME=3D"Field02 " xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"51" NAME=3D"Field03 " xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"52" NAME=3D"Field04 " xsi:type=3D"SQLNVARCHAR"/>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>> > >
--=_NextPart_000_0021_01C82618.1ED36460
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server =Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the last =character in the last column.
- It can not cope with none quoted =strings, I get error from that.
It did cope correctly with a comma in a =string.
Below a detailled result, mainly your =code and example.
The word 'voorbeeld' is just the Dutch =word for 'example'.
Maybe I misunderstood your example, so =I have included the files
as I understood them to =be.
Did you try using READ method in =.NET to get the file.?I do not =understand this question, so I assume the anwser is no.
I copied the example from the message =in Outlook Express.
Am I doing something completely =wrong.
Ben Brugman
The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 =varchar(30),c2 varchar(30), c3 varchar(30))
delete dbo.voorbeeld_tb1
bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" 1 =c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post Material""D","MOVE","Misc Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; c2 =; = c3-- -- --"A &=nbsp; &n=bsp; ISSUE &n=bsp; &nb=sp; Misc Material Issue""B &=nbsp; &n=bsp; MOVE &nb=sp; &nbs=p; Misc Material Move""C &n=bsp; &nb=sp; POST &nb=sp; &nbs=p; Misc Post Material""D &nbs=p; =; MOVE &nb=sp; &nbs=p; Misc Material Move""A &n=bsp; &nb=sp; ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--
"Uri Dimant"
--=_NextPart_000_0021_01C82618.1ED36460--|||This is a multi-part message in MIME format.
--=_NextPart_000_0142_01C826AF.C6245020
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ben
You are right. We got double quotes in the table. However you can use =REPLACE function to get rid of them.
I mean .READ method to read XML file in the VB.NET
"ben brugman" <ben@.niethier.nl> wrote in message =news:uOJQ%23%23gJIHA.4196@.TK2MSFTNGP04.phx.gbl...
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first character in the first column.
- There is a double quote as the last character in the last column.
- It can not cope with none quoted strings, I get error from that.
It did cope correctly with a comma in a string.
Below a detailled result, mainly your code and example.
The word 'voorbeeld' is just the Dutch word for 'example'.
Maybe I misunderstood your example, so I have included the files
as I understood them to be.
Did you try using READ method in .NET to get the file.?
I do not understand this question, so I assume the anwser is no.
I copied the example from the message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
=----=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
delete dbo.voorbeeld_tb1
bulk insert dbo.voorbeeld_tb1
from 'c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
=----=--
The used format file c:\voorbeeld_format.fmt
=----=--
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
=----=--
the content file c:\voorbeeld_content.txt :
=----=--
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
"A","ISSUE","Misc , extra"
=----=--
select * from dbo.voorbeeld_tb1 Delivers
=----=--
c1 c2 c3
-- -- =--
"A ISSUE Misc =Material Issue"
"B MOVE Misc =Material Move"
"C POST Misc =Post Material"
"D MOVE Misc =Material Move"
"A ISSUE Misc , =extra"
(5 row(s) affected)
=----=--
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Ben
> Remove it.
> 8.0
> 3
> 1 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 3 SQLCHAR 0 30 "\r\n" 3 c3 ""
> > > Did you try using READ method in .NET to get the file.?
> > "ben brugman" <ben@.niethier.nl> wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello Uri,
>>
>> Thanks for the quick reply,
>> I'll try to understand and use your method.
>>
>> In your example does the fmt_file.fmt start with the line starting =with 4 >> or the line starting with 8?
>> Tthe text file is the file with the 4 lines with the A/B/C/D =examples I >> assume?
>> Not all my fields are double quoted, not all strings are double =quoted >> (only the one's containing a comma), is this a problem?
>> You have three fields, but I see four definitions, how does this =work?
>>
>> Sorry to ask so many questions, but I did try to read up on the XML =format >> (not succesfully yet), so switching to another format is no =problem, but >> I'll like to understand the 'definitions' so that I do not run into =new >> problems, which I can not solve by myself.
>>
>> Also next to get the system working (with your example), I would =like to >> know how to work in the XML format and still be able to insert =double >> quoted strings when not all strings are double quoted.
>>
>> Thanks for your time and attention,
>> Ben Brugman
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message >> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>>
>>
>> I did not create a XML file ,howere this works for just great
>>
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
>>
>> go
>>
>> /*
>>
>> "A","ISSUE","Misc Material Issue"
>>
>> "B","MOVE","Misc Material Move"
>>
>> "C","POST","Misc Post Material"
>>
>> "D","MOVE","Misc Material Move"
>>
>> 8.0
>>
>> 4
>>
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>>
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>>
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>>
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>>
>> */
>>
>> bulk insert dbo.tb1
>>
>> from 'c:\txt_file.txt'
>>
>> with (formatfile=3D'c:\fmt_file.fmt')
>>
>> go
>>
>> select *
>>
>> from dbo.tb
>>
>> go
>>
>> drop table dbo.tb
>>
>>
>>
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message >> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>>
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> See below for the command and the format.
>>
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>>
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>>
>> Thanks in advance,
>> Ben Brugman
>>
>> The command I use from the 'Query Analyser' in 2005.
>>
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE =3D 'D:\folder\format.xml' );
>>
>> The format file:
>>
>> <?xml version=3D"1.0"?>
>> <BCPFORMAT >> =xmlns=3D"http://schemas.microsoft.com/sqlserver/2004/bulkload/format"=20
>> xmlns:xsi=3D"">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>>
>> <FIELD ID=3D"01" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"02" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> <FIELD ID=3D"51" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"52" xsi:type=3D"CharTerm" TERMINATOR=3D",\r\n" >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE=3D"01" NAME=3D"Field01 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"02" NAME=3D"Field02 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> <COLUMN SOURCE=3D"51" NAME=3D"Field03 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"52" NAME=3D"Field04 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>>
>>
>>
>>
>> > >
--=_NextPart_000_0142_01C826AF.C6245020
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ben
You are right. We got double quotes in =the table. However you can use REPLACE function to get rid of them.
I mean .READ method to read XML file in =the VB.NET
"ben brugman"
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would = like.
I am working in MicroSoft SQL Server =Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the last =character in the last column.
- It can not cope with none quoted =strings, I get error from that.
It did cope correctly with a comma in =a string.
Below a detailled result, mainly your =code and example.
The word 'voorbeeld' is just the =Dutch word for 'example'.
Maybe I misunderstood your example, =so I have included the files
as I understood them to =be.
Did you try using READ method =in .NET to get the file.?I do not =understand this question, so I assume the anwser is no.
I copied the example from the message =in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 =varchar(30),c2 varchar(30), c3 varchar(30))
delete dbo.voorbeeld_tb1
bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" 1 =c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post Material""D","MOVE","Misc =Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; =c2 =; = c3-- -- =--"A &=nbsp; &n=bsp; =ISSUE &n=bsp; &nb=sp; Misc Material =Issue""B &=nbsp; &n=bsp; =MOVE &nb=sp; &nbs=p; Misc Material =Move""C &n=bsp; &nb=sp; =POST &nb=sp; &nbs=p; Misc Post =Material""D &nbs=p; =; =MOVE &nb=sp; &nbs=p; Misc Material =Move""A &n=bsp; &nb=sp; =ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--
"Uri Dimant"
--=_NextPart_000_0142_01C826AF.C6245020--|||This is a multi-part message in MIME format.
--=_NextPart_000_005A_01C826BC.39421B80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello Uri,
Yes the double quotes can be removed with a REPLACE, specific if they =are the first or last character.
But the main problem is still that not all strings are quoted and this =import loses track when a string is
not quoted. I get error messages. And sometimes it 'jumbles' up the =columns.
Also I tried to to continue with the xml definition file, no succes =there either.
The import files are delivered to me, but in total there are hundreds of =import files. So editing them is not realy an option.
As far as I remember this wasn't a problem in SQL-server 2000 using DTS, =but am not sure of that.
Thanks again for your time and attention.
ben
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht =news:%232ikj8pJIHA.4592@.TK2MSFTNGP02.phx.gbl...
Ben
You are right. We got double quotes in the table. However you can use =REPLACE function to get rid of them.
I mean .READ method to read XML file in the VB.NET
"ben brugman" <ben@.niethier.nl> wrote in message =news:uOJQ%23%23gJIHA.4196@.TK2MSFTNGP04.phx.gbl...
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first character in the first column.
- There is a double quote as the last character in the last column.
- It can not cope with none quoted strings, I get error from that.
It did cope correctly with a comma in a string.
Below a detailled result, mainly your code and example.
The word 'voorbeeld' is just the Dutch word for 'example'.
Maybe I misunderstood your example, so I have included the files
as I understood them to be.
Did you try using READ method in .NET to get the file.?
I do not understand this question, so I assume the anwser is no.
I copied the example from the message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
=----=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
delete dbo.voorbeeld_tb1
bulk insert dbo.voorbeeld_tb1
from 'c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
=----=--
The used format file c:\voorbeeld_format.fmt
=----=--
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
=----=--
the content file c:\voorbeeld_content.txt :
=----=--
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
"A","ISSUE","Misc , extra"
=----=--
select * from dbo.voorbeeld_tb1 Delivers
=----=--
c1 c2 c3
-- -- =--
"A ISSUE Misc =Material Issue"
"B MOVE Misc =Material Move"
"C POST Misc =Post Material"
"D MOVE Misc =Material Move"
"A ISSUE Misc , =extra"
(5 row(s) affected)
=----=--
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Ben
> Remove it.
> 8.0
> 3
> 1 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 3 SQLCHAR 0 30 "\r\n" 3 c3 ""
> > > Did you try using READ method in .NET to get the file.?
> > "ben brugman" <ben@.niethier.nl> wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello Uri,
>>
>> Thanks for the quick reply,
>> I'll try to understand and use your method.
>>
>> In your example does the fmt_file.fmt start with the line =starting with 4 >> or the line starting with 8?
>> Tthe text file is the file with the 4 lines with the A/B/C/D =examples I >> assume?
>> Not all my fields are double quoted, not all strings are double =quoted >> (only the one's containing a comma), is this a problem?
>> You have three fields, but I see four definitions, how does this =work?
>>
>> Sorry to ask so many questions, but I did try to read up on the =XML format >> (not succesfully yet), so switching to another format is no =problem, but >> I'll like to understand the 'definitions' so that I do not run =into new >> problems, which I can not solve by myself.
>>
>> Also next to get the system working (with your example), I would =like to >> know how to work in the XML format and still be able to insert =double >> quoted strings when not all strings are double quoted.
>>
>> Thanks for your time and attention,
>> Ben Brugman
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message >> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>>
>>
>> I did not create a XML file ,howere this works for just great
>>
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
>>
>> go
>>
>> /*
>>
>> "A","ISSUE","Misc Material Issue"
>>
>> "B","MOVE","Misc Material Move"
>>
>> "C","POST","Misc Post Material"
>>
>> "D","MOVE","Misc Material Move"
>>
>> 8.0
>>
>> 4
>>
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>>
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>>
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>>
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>>
>> */
>>
>> bulk insert dbo.tb1
>>
>> from 'c:\txt_file.txt'
>>
>> with (formatfile=3D'c:\fmt_file.fmt')
>>
>> go
>>
>> select *
>>
>> from dbo.tb
>>
>> go
>>
>> drop table dbo.tb
>>
>>
>>
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message >> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>>
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> See below for the command and the format.
>>
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>>
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>>
>> Thanks in advance,
>> Ben Brugman
>>
>> The command I use from the 'Query Analyser' in 2005.
>>
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE =3D 'D:\folder\format.xml' );
>>
>> The format file:
>>
>> <?xml version=3D"1.0"?>
>> <BCPFORMAT >> =xmlns=3D"http://schemas.microsoft.com/sqlserver/2004/bulkload/format"=20
>> xmlns:xsi=3D"">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>>
>> <FIELD ID=3D"01" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"02" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> <FIELD ID=3D"51" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"52" xsi:type=3D"CharTerm" TERMINATOR=3D",\r\n" >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE=3D"01" NAME=3D"Field01 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"02" NAME=3D"Field02 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> <COLUMN SOURCE=3D"51" NAME=3D"Field03 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"52" NAME=3D"Field04 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>>
>>
>>
>>
>> > >
--=_NextPart_000_005A_01C826BC.39421B80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello Uri,
Yes the double quotes can be removed =with a REPLACE, specific if they are the first or last character.
But the main problem is still that not =all strings are quoted and this import loses track when a string is
not quoted. I get error messages. And =sometimes it 'jumbles' up the columns.
Also I tried to to continue with the =xml definition file, no succes there either.
The import files are delivered to me, =but in total there are hundreds of import files. So editing them is not realy an option.
As far as I remember this wasn't a =problem in SQL-server 2000 using DTS, but am not sure of that.
Thanks again for your time and attention.
ben
"Uri Dimant"
Ben
You are right. We got double quotes =in the table. However you can use REPLACE function to get rid of them.
I mean .READ method to read XML file =in the VB.NET
"ben brugman"
Hello Uri,
I tried your script, as I =understood it.
But I can't get it to work as I =would like.
I am working in MicroSoft SQL =Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the =last character in the last column.
- It can not cope with none quoted =strings, I get error from that.
It did cope correctly with a comma =in a string.
Below a detailled result, mainly =your code and example.
The word 'voorbeeld' is just the =Dutch word for 'example'.
Maybe I misunderstood your example, =so I have included the files
as I understood them to =be.
Did you try using READ method =in .NET to get the file.?I =do not understand this question, so I assume the anwser is no.
I copied the example from the =message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
delete =dbo.voorbeeld_tb1
bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" =1 c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post =Material""D","MOVE","Misc Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; =c2 =; = c3-- -- =--"A &=nbsp; &n=bsp; =ISSUE &n=bsp; &nb=sp; Misc Material =Issue""B &=nbsp; &n=bsp; =MOVE &nb=sp; &nbs=p; Misc Material =Move""C &n=bsp; &nb=sp; =POST &nb=sp; &nbs=p; Misc Post =Material""D &nbs=p; =; =MOVE &nb=sp; &nbs=p; Misc Material =Move""A &n=bsp; &nb=sp; =ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--
"Uri Dimant"
--=_NextPart_000_005A_01C826BC.39421B80--
No comments:
Post a Comment