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 data

Hi Guys
Can someone give me some directions on importing data from another server
e.g.
INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
( Suburb, PostCode, State)
SELECT Suburb, PostCode, State
FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
Thanks heaps...BooksOnLine not too helpful
The easiest way I've seen is to use the Import wizard. In the SQL Server
Management Studio Object Explorer, right-click on the target (or source)
database and select All Tasks-->Import Data (or Import Data).
Hope this helps.
Dan Guzman
SQL Server MVP
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>
|||Harry
Have you created a linked server to SQL Server 2000 ?
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>

Importing data

Hi Guys
Can someone give me some directions on importing data from another server
e.g.
INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
( Suburb, PostCode, State)
SELECT Suburb, PostCode, State
FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
Thanks heaps...BooksOnLine not too helpfulThe easiest way I've seen is to use the Import wizard. In the SQL Server
Management Studio Object Explorer, right-click on the target (or source)
database and select All Tasks-->Import Data (or Import Data).
Hope this helps.
Dan Guzman
SQL Server MVP
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>|||Harry
Have you created a linked server to SQL Server 2000 ?
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>

Importing data

Hi Guys
Can someone give me some directions on importing data from another server
e.g.
INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
( Suburb, PostCode, State)
SELECT Suburb, PostCode, State
FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
Thanks heaps...BooksOnLine not too helpfulThe easiest way I've seen is to use the Import wizard. In the SQL Server
Management Studio Object Explorer, right-click on the target (or source)
database and select All Tasks-->Import Data (or Import Data).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>|||Harry
Have you created a linked server to SQL Server 2000 ?
"Harry Strybos" <harry_NOSPAM@.ffapaysmart.com.au> wrote in message
news:3wIRg.14671$b6.160428@.nasal.pacific.net.au...
> Hi Guys
> Can someone give me some directions on importing data from another server
> e.g.
> INSERT INTO SQL2005.MyDB1.dbo.PostCode --this is SQL 2005
> ( Suburb, PostCode, State)
> SELECT Suburb, PostCode, State
> FROM SQL2000.MyDB2.dbo.PostCode --this is SQL 2000
> Thanks heaps...BooksOnLine not too helpful
>

Importing data

Hello everybody,
I need some help with importing data from txt to sql.
I have done that with BULK INSERT, but i had some limitations:
-i cant supress any field in source or destination.
Anyone can help me?
Thanks
Leandro Loureiro dos SantosLeandro,
> I need some help with importing data from txt to sql.
> I have done that with BULK INSERT, but i had some limitations:
> -i cant supress any field in source or destination.
You can do this with a bcp format using
bulk insert ...
with (formatfile = 'format_file_path')
Linda

Importing an Excel Spreadsheet into SQL Server

Hi,
I'm trying to import an excel file into SQL sever(using an insert statement), i'm creating a DTS package (in enterprise manager) and have VB Script. When i parse it, i get no errors, but when i run the package it says that it ran successfully but nothing happens, it doesnt insert into the table, even though i tested the insert statement. Can anyone help me?? Here's the code:

'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************

Function Main()
on error resume next
Set objxl = CreateObject("Excel.Application")
objxl.Visible = False


Dim xlFile
xlFile = "C:\Data\file.xls"
Set objWkb = objxl.Workbooks.Open(xlFile)

'' Connecting to SQL Server
set cn = server.CreateObject("ADODB.Connection")

Dim serverName
serverName = "myserver2"


strCS = "Provider=SQLOLEDB; Data Source=myserver2;Initial
Catalog=mycat; Integrated Security=SSPI"

cn.ConnectionString = strCS
On Error Resume Next
cn.Open
Set objsht = objWkb.Worksheets.Open("Sheet1")
Dim client_name, rb, date_rvd, LOB
Dim sql
Dim row, sequence
row = 2

client_name = Trim(objsht.Cells(row, 2).Value)
Do While IsNull(client_name) = False And client_name <> ""
'client_name = Trim(objsht.Cells(row, 2))
rb = Trim(objsht.Cells(row, 4).value)
date_rvd = Trim(objsht.Cells(row, 6).value)
LOB = "WCS"

sql = "INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES (" & _
" '" & client_name & "' ,'" & rb & "', " & date_rvd & ", '" & LOB & "');"


row = row + 1
MsgBox (sql)
client_name = Trim(objsht.Cells(row, 2).value)
cn.Execute (sql)

Loop

if err.count = 0 then
Main = DTSTaskExecResult_Success
else
Main = DTSTaskExecResult_Failure
end if
End FunctionYou might want to refer to this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Please can you post the value of the sql variable? To us this is several magnitudes more important than all that vb.|||Why exactly are you using this script? It appears to be a very inneficient method to import the data (you're doing it line-by-line!!).|||Actually - I have read the code now. I would dump the on error resume next when debugging. Probably in production too but especially now.

Also - the loop is unecessary - refer to the link I sent. SQL Server is perfectly capable of dealing with an excel sheet as a table rather than a row at a time. Quicker and easier. As such you can skip all the ActiveX stuff altogether. DTS & ActiveX -> difficult to debug code. If possible try to do it all in T-SQL and run it from a job. You will find most of the guys here won't use ActiveX for very much if anything at all and work with the native T-SQL language.|||Why exactly are you using this script? It appears to be a very inneficient method to import the data (you're doing it line-by-line!!).Damn youngsters and their quick typing skills ;)|||Reminds me of some ActiveX in a DTS my old manager wrote...

It went through every row (46K) and assigned zeroes or blank strings to every NULL value... Needless to say it took aaaaaaaages.

A couple of very tiny T-SQL tweaks and it runns in seconds again ;) No prizes for guessing the function anyhow|||Thanks for the quick response, the sql variable = INSERT INTO TEMP_TEST (CLIENT_NAME, RB, DATE_REVIEWED, LOB) VALUES
( 'Adisseo USA Inc.' ,'Wesley Kent', 2/27/2007, 'WCS');

the reason i'm reading line by line is because i only want certain columns having certain values, it doesnt show that in this code, but eventually i will need to read line by line. Thanks!|||i only want certain columns having certain values
Can you expand on this please?
Rules/logic etc|||como se dice?|||Why not load all of it into a temporary table, extract what you want from it, and delete the temp table again?|||the rule is, read specific columns(not all), and then insert the values in a temp table. there are four tabs that need to be imported, all with different columns to import. but it needs to be in a DTS package. I guess loading it all would be another option|||Why not load all of it into a temporary table, extract what you want from it, and delete the temp table again -- With a DTS package?|||I take it you did not bother looking at the link then? You can specify columns & where clauses using that technique.|||Gotta ask - what is everyone's obsession with using DTS packages? Why stipulate that the answer, whatever it is, must be in a DTS package?

In any event - you can call T-SQL from a DTS package too.|||i got it, the problem was with the excel object "Trim(objsht.Cells(row, 2).Value)"
instead i used Trim(objxl.Cells(row, 2).Value). Thanks for all your suggestion guys!|||i did look at the link pootle, but in some cases i would need to skip lines, and i didnt know how to do that with that link|||Gotta ask - what is everyone's obsession with using DTS packages? ...

ummm... because I haven't learned the other way(s?) yet.
Besides, the graphical interface makes it so easy to follow job flow:

importing an excel file and save data into a database in my web account

Hi everyone, sorry if this message is not supposed to be posted here.

I'm learning asp.net , and would like to know how I can insert data from a excel file into a database on my web account. Pretty much insert/update information in the database using excel file or a access file.

Thanks a lot in advance

Checkout thses links:

http://support.microsoft.com/kb/311731

http://www.msdner.com/forum/thread302753.html

http://support.microsoft.com/kb/316934

http://www.vbdotnetheaven.com/UploadFile/mahesh/AccessExcelDatabase04252005014321AM/AccessExcelDatabase.aspx

I hope they will help.

Good luck.

|||

Thanks a lot. I found an interesting article that talks about this. again thanks for your assistance

|||

If my post was helpful for your then please mark it as answer.

If not, please share the answer with us in a post and mark it as answer.

Remeber we have to cooperate to help each other.

Thanks and you are welcome.