Importing Access DB: containing

Showing posts with label containing. Show all posts

Monday, March 26, 2012

Importing strings.

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
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='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...
>

Importing strings.

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
> 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" =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" = =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" =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" =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:>> > xmlns=3D"" > xmlns:xsi=3D"">> >>>>=; COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> > => > >>&nbs=p; > >> > >>>>=;>>>>>=>> > >

--=_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" wrote in =message news:uOJQ%23%23gJ=IHA.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.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" =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" =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" =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" =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:>> > xmlns=3D"" > xmlns:xsi=3D"">> =>>>>=; =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> > > > >>&nbs=p; > >> > =>>>>=;>>>>>=>> > >

--=_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" 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" wrote in =message news:uOJQ%23%23gJ=IHA.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.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" =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" =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" = =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" =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:>> > xmlns=3D"" > xmlns:xsi=3D"">> =>>>>=; =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> > > > >>&nbs=p; > >> > =>>>>=;>>>>>=>> > >

--=_NextPart_000_005A_01C826BC.39421B80--

Importing strings.

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
> 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
>
> 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...
>|||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...
>|||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='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 Mate
rial"
"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...
>
>|||Ben
You are right. We got double quotes in the table. However you can use REPLAC
E 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@.T
K2MSFTNGP04.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='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 Mate
rial"
"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,
Yes the double quotes can be removed with a REPLACE, specific if they are th
e first or last character.
But the main problem is still that not all strings are quoted and this impor
t 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 ei
ther.
The import files are delivered to me, but in total there are hundreds of imp
ort 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@.T
K2MSFTNGP02.phx.gbl...
Ben
You are right. We got double quotes in the table. However you can use REPLAC
E 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@.T
K2MSFTNGP04.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='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 Mate
rial"
"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...
>
>

Importing stored procedures

Hello

Sorry if this is a stupid question, but.....

I have just upgraded to SQL2005 and I have a local database containing about 30 stored procedures, and I cant find how to upload these to the live server.

In SQL2000 there used to be an import/export option for copying objects, but this is no longer there.

Cheers
pie

Sorry, in addition when I use the copy tables and data tool, it doesn't maintain the increment fields.

This was the same for SQL2000, which is why I always used the Copy Objects tool.

pie

Friday, February 24, 2012

Importing an XML file with hirarchial data

Hello,

I would like to import an XML file containing hierarchial data into a table in SQLserver. I guess that I am supposed to use the XML source editor and connect it to the xml file containing the following:

<?xml version="1.0"?>
<VariantFamilies>
<VariantFamily FamilyID="XXX">
<FamilyDescription Language="en-GB">Variant Family Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1XX">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2XX">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
<VariantFamily FamilyID="XYY">
<FamilyDescription Language="en-GB">Variant Family 2 Description in English</FamilyDescription>
<FamilyDescription Language="sv-SE">Variantfamiljsbeskrivning 2 p? svenska</FamilyDescription>
<Variants>
<Variant VariantID="1YY">
<VariantDescription Language="en-GB">Variant Description 1 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 1 p? svenska</VariantDescription>
</Variant>
<Variant VariantID="2YY">
<VariantDescription Language="en-GB">Variant Description 2 in English</VariantDescription>
<VariantDescription Language="sv-SE">Variantbeskrivning 2 p? svenska</VariantDescription>
</Variant>
</Variants>
</VariantFamily>
</VariantFamilies>

Following columns exist in the table: WK, VariantFamily, VariantFamilyDescription, VariantID, VariantDescription.
wk is automatic,
Variant family should contain XYY,
VariantFamilyDescription should contain Variant Family 2 Description in English,
VariantID should contain 2YY,
VariantDescription should contain Variant Description 2 in English

in the outputname I can only choose one of the attributes at a time (the system has created two new items for each ID to identify the "parent". Now how do I go about to put the actual information into the columns? NOT the system created IDs. I would like to have one row per smalles grain (VariantID) hence having redundant data in Variant family column.

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,Hanna,
XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table (which is an output at xmlsrc) and elements in that complexType will be columns of that table. Hirarchial data will become multiple tables with _id columns in each related table to track their relationship.
From the xml above, I don't see you can import it to a single table using XMLSrc. Depends on your xsd, you may need further steps in XMLSrc downstreams, unionAll? merge?, to accomplish your scenario.

Thanks
Wenyang|||

I seem to be in a similar conundrum. However, what I want to do is move data elements from my XML file into multiple SQL Server database tables. I've created the XML data flow source and multiple data flow destinations, each pointing to their own table.

As Wenyang Hu pointed out, the XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table. I've now got over 50 tables, but can only select one (from the XML Source Editor columns form). Subsequently, when I go to add a data flow path to my data flow destination, I can only select a single output, which doesn't provide me with all the data I need to load a single database table.

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

That's true one xmlsrc output can be hooked with only one downstream, hence if you want several xmlsrc outputs to be loaded to one single destination, then as said above you'll need other transformation(s) either in between XMLSrc and destination, or, in another dataflow to manipulate.

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

To Sort on various key columns then join on those key columns at MergeJoin to eventually merge these 3 xmlsrc outputs into one destination. Since you have 50 outputs from xmlsrc, you can also save them into 50 destionation tables first, then use another dataflow with those tables as sources, then use other SSIS transforms to process based on your specific data hierarchy.

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering if the 'Union All' transformation task might work. The help on that task indicates it can have multiple inputs. If I map multiple inputs from my XMLSrc outputs, then maybe this will work. Then I can have about 10 union all task to map to my 10 SQL tables. Thoughts? I'm hoping to understand the union all as the help doesn't indicate that it does anything unusual.
thanks.
|||

UnionAll unions all its input rows but a) the column metadata will be built up based only on the primary input(the first one hooked up with unionAll) b)it does not "join" on key columns which you may need to merge those hierarchical data from xml file into one destination without loosing your data logic. So unless your multiple xmlsrc outputs have identical column structure, unionall won't fit your need I think.

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

<Property>
<PropertyId>TheIdINeed</PropertyId>
....
....
<Buildings>
<Building id="123">
.....
......
<Rooms>
<Room id ="ABC">
.....
.....
<Beds>
<Bed id="cfg">
.....
.....
</Bed>
</Beds>
</Room>
</Rooms>
</Building>
</Buildings>
</Property>

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

Importing an XML file with hirarchial data

Hello,

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,

Hanna,
XMLSrc adapter builds up its metadata based on the schema, a complexType in xsd will be read as a table (which is an output at xmlsrc) and elements in that complexType will be columns of that table. Hirarchial data will become multiple tables with _id columns in each related table to track their relationship.
From the xml above, I don't see you can import it to a single table using XMLSrc. Depends on your xsd, you may need further steps in XMLSrc downstreams, unionAll? merge?, to accomplish your scenario.

Thanks
Wenyang|||

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering

if the 'Union All' transformation task might work.

The help on that task indicates it can have multiple inputs. If I

map multiple inputs from my XMLSrc outputs, then maybe this will

work. Then I can have about 10 union all task to map to my 10 SQL

tables. Thoughts? I'm hoping to understand the union

all as the help doesn't indicate that it does anything unusual.

thanks.|||

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

Importing an XML file with hirarchial data

Hello,

does it have anything to do with how the XSD is defined? or do I need to use the xml task and how do I do then?

Thankful for descriptions of how to go about.

Sincerely,

Thanks
Wenyang|||

I hope this makes sense and look forward to a solution.

Thanks,

Johnny

|||

For example, you can use a dataflow like

XMLSrc (output1)->Sort->MergeJoin->Sort->MergeJoin>Destination

(output2)->Sort--^ ^

(output3)->Sort-|

Thanks

Wenyang

|||Either way, that will be quite a mapping exercise. I'm wondering

if the 'Union All' transformation task might work.

The help on that task indicates it can have multiple inputs. If I

map multiple inputs from my XMLSrc outputs, then maybe this will

work. Then I can have about 10 union all task to map to my 10 SQL

tables. Thoughts? I'm hoping to understand the union

all as the help doesn't indicate that it does anything unusual.

thanks.|||

Thanks

Wenyang

|||

I have a similar problem, except the XML source outputs would be fine if I just could reference the id of the parent node.

Example:

My problem is that I can't get the PropertyId from the Xml Source output for Building, Room,Bed.
I have tried to make a reference i the XSD and a new External column, but that was just Null.

Best regards
Claus

Monday, March 26, 2012

Importing strings.

Importing strings.

Importing strings.

Importing stored procedures

Friday, February 24, 2012

Importing an XML file with hirarchial data

Importing an XML file with hirarchial data

Importing an XML file with hirarchial data

Importing Access DB

Blog Archive

About Me