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...
>
Showing posts with label comma. Show all posts
Showing posts with label comma. Show all posts
Monday, March 26, 2012
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--
With bulk insert I am trying to insert data.
Data is comma delimited.
And strings containing comma's are double quoted.
See below for the command and the format.
I am doing something wrong because strings with
comma's in them get split over more fields.
This should be simple but haven't found the answer yet.
What should I change in de command, or in the format file?
Thanks in advance,
Ben Brugman
The command I use from the 'Query Analyser' in 2005.
BULK INSERT IMP FROM 'D:\folder\import.txt'
WITH (FORMATFILE = 'D:\folder\format.xml' );
The format file:
<?xml version="1.0"?>
<BCPFORMAT
xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="01" NAME="Field01
" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="02" NAME="Field02
" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="51" NAME="Field03
" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="52" NAME="Field04
" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>Ben
> With bulk insert I am trying to insert data.
> Data is comma delimited.
> And strings containing comma's are double quoted.
I did not create a XML file ,howere this works for just great
create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
go
/*
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
8.0
4
1 SQLCHAR 0 0 "\"" 0 first_quote ""
2 SQLCHAR 0 30 "\",\"" 1 c1 ""
3 SQLCHAR 0 30 "\",\"" 2 c2 ""
4 SQLCHAR 0 30 "\r\n" 3 c3 ""
*/
bulk insert dbo.tb1
from 'c:\txt_file.txt'
with (formatfile='c:\fmt_file.fmt')
go
select *
from dbo.tb
go
drop table dbo.tb
"ben brugman" <ben@.niethier.nl> wrote in message
news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
> Hello,
> With bulk insert I am trying to insert data.
> Data is comma delimited.
> And strings containing comma's are double quoted.
> See below for the command and the format.
> I am doing something wrong because strings with
> comma's in them get split over more fields.
> This should be simple but haven't found the answer yet.
> What should I change in de command, or in the format file?
> Thanks in advance,
> Ben Brugman
> The command I use from the 'Query Analyser' in 2005.
> BULK INSERT IMP FROM 'D:\folder\import.txt'
> WITH (FORMATFILE = 'D:\folder\format.xml' );
> The format file:
> <?xml version="1.0"?>
> <BCPFORMAT
> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
> xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
> <RECORD>
>
> <FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> <FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> <FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> <FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
> </RECORD>
> <ROW>
> <COLUMN SOURCE="01" NAME="Field01 " xsi:type="SQLNVARCHAR"/>
> <COLUMN SOURCE="02" NAME="Field02 " xsi:type="SQLNVARCHAR"/>
> <COLUMN SOURCE="51" NAME="Field03 " xsi:type="SQLNVARCHAR"/>
> <COLUMN SOURCE="52" NAME="Field04 " xsi:type="SQLNVARCHAR"/>
> </ROW>
> </BCPFORMAT>
>
>|||Hello Uri,
Thanks for the quick reply,
I'll try to understand and use your method.
In your example does the fmt_file.fmt start with the line starting with 4 or
the line starting with 8?
Tthe text file is the file with the 4 lines with the A/B/C/D examples I
assume?
Not all my fields are double quoted, not all strings are double quoted (only
the one's containing a comma), is this a problem?
You have three fields, but I see four definitions, how does this work?
Sorry to ask so many questions, but I did try to read up on the XML format
(not succesfully yet), so switching to another format is no problem, but
I'll like to understand the 'definitions' so that I do not run into new
problems, which I can not solve by myself.
Also next to get the system working (with your example), I would like to
know how to work in the XML format and still be able to insert double quoted
strings when not all strings are double quoted.
Thanks for your time and attention,
Ben Brugman
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>
> I did not create a XML file ,howere this works for just great
>
> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
> go
> /*
> "A","ISSUE","Misc Material Issue"
> "B","MOVE","Misc Material Move"
> "C","POST","Misc Post Material"
> "D","MOVE","Misc Material Move"
> 8.0
> 4
> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
> */
> bulk insert dbo.tb1
> from 'c:\txt_file.txt'
> with (formatfile='c:\fmt_file.fmt')
> go
> select *
> from dbo.tb
> go
> drop table dbo.tb
>
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>> See below for the command and the format.
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>> Thanks in advance,
>> Ben Brugman
>> The command I use from the 'Query Analyser' in 2005.
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE = 'D:\folder\format.xml' );
>> The format file:
>> <?xml version="1.0"?>
>> <BCPFORMAT
>> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
>> xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>> <FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE="01" NAME="Field01 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="02" NAME="Field02 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="51" NAME="Field03 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="52" NAME="Field04 " xsi:type="SQLNVARCHAR"/>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>|||Ben
Remove it.
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
Did you try using READ method in .NET to get the file.?
"ben brugman" <ben@.niethier.nl> wrote in message
news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
> Hello Uri,
> Thanks for the quick reply,
> I'll try to understand and use your method.
> In your example does the fmt_file.fmt start with the line starting with 4
> or the line starting with 8?
> Tthe text file is the file with the 4 lines with the A/B/C/D examples I
> assume?
> Not all my fields are double quoted, not all strings are double quoted
> (only the one's containing a comma), is this a problem?
> You have three fields, but I see four definitions, how does this work?
> Sorry to ask so many questions, but I did try to read up on the XML format
> (not succesfully yet), so switching to another format is no problem, but
> I'll like to understand the 'definitions' so that I do not run into new
> problems, which I can not solve by myself.
> Also next to get the system working (with your example), I would like to
> know how to work in the XML format and still be able to insert double
> quoted strings when not all strings are double quoted.
> Thanks for your time and attention,
> Ben Brugman
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> I did not create a XML file ,howere this works for just great
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
>> go
>> /*
>> "A","ISSUE","Misc Material Issue"
>> "B","MOVE","Misc Material Move"
>> "C","POST","Misc Post Material"
>> "D","MOVE","Misc Material Move"
>> 8.0
>> 4
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>> */
>> bulk insert dbo.tb1
>> from 'c:\txt_file.txt'
>> with (formatfile='c:\fmt_file.fmt')
>> go
>> select *
>> from dbo.tb
>> go
>> drop table dbo.tb
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message
>> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>> See below for the command and the format.
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>> Thanks in advance,
>> Ben Brugman
>> The command I use from the 'Query Analyser' in 2005.
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE = 'D:\folder\format.xml' );
>> The format file:
>> <?xml version="1.0"?>
>> <BCPFORMAT
>> xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
>> xmlns:xsi="">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>> <FIELD ID="01" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="02" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="51" xsi:type="CharTerm" TERMINATOR=","
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID="52" xsi:type="CharTerm" TERMINATOR=",\r\n"
>> COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE="01" NAME="Field01 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="02" NAME="Field02 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="51" NAME="Field03 " xsi:type="SQLNVARCHAR"/>
>> <COLUMN SOURCE="52" NAME="Field04 " xsi:type="SQLNVARCHAR"/>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0021_01C82618.1ED36460
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first character in the first column.
- There is a double quote as the last character in the last column.
- It can not cope with none quoted strings, I get error from that.
It did cope correctly with a comma in a string.
Below a detailled result, mainly your code and example.
The word 'voorbeeld' is just the Dutch word for 'example'.
Maybe I misunderstood your example, so I have included the files
as I understood them to be.
Did you try using READ method in .NET to get the file.?
I do not understand this question, so I assume the anwser is no.
I copied the example from the message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
----=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
delete dbo.voorbeeld_tb1
bulk insert dbo.voorbeeld_tb1
from 'c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
----=--
The used format file c:\voorbeeld_format.fmt
----=--
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
----=--
the content file c:\voorbeeld_content.txt :
----=--
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
"A","ISSUE","Misc , extra"
----=--
select * from dbo.voorbeeld_tb1 Delivers
----=--
c1 c2 c3
-- -- =--
"A ISSUE Misc =Material Issue"
"B MOVE Misc =Material Move"
"C POST Misc Post =Material"
"D MOVE Misc =Material Move"
"A ISSUE Misc , =extra"
(5 row(s) affected)
----=--
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Ben
> Remove it.
> 8.0
> 3
> 1 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 3 SQLCHAR 0 30 "\r\n" 3 c3 ""
> > > Did you try using READ method in .NET to get the file.?
> > "ben brugman" <ben@.niethier.nl> wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello Uri,
>> Thanks for the quick reply,
>> I'll try to understand and use your method.
>> In your example does the fmt_file.fmt start with the line starting =with 4 >> or the line starting with 8?
>> Tthe text file is the file with the 4 lines with the A/B/C/D examples =I >> assume?
>> Not all my fields are double quoted, not all strings are double =quoted >> (only the one's containing a comma), is this a problem?
>> You have three fields, but I see four definitions, how does this =work?
>> Sorry to ask so many questions, but I did try to read up on the XML =format >> (not succesfully yet), so switching to another format is no problem, =but >> I'll like to understand the 'definitions' so that I do not run into =new >> problems, which I can not solve by myself.
>> Also next to get the system working (with your example), I would like =to >> know how to work in the XML format and still be able to insert double =
>> quoted strings when not all strings are double quoted.
>> Thanks for your time and attention,
>> Ben Brugman
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message >> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> I did not create a XML file ,howere this works for just great
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
>> go
>> /*
>> "A","ISSUE","Misc Material Issue"
>> "B","MOVE","Misc Material Move"
>> "C","POST","Misc Post Material"
>> "D","MOVE","Misc Material Move"
>> 8.0
>> 4
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>> */
>> bulk insert dbo.tb1
>> from 'c:\txt_file.txt'
>> with (formatfile=3D'c:\fmt_file.fmt')
>> go
>> select *
>> from dbo.tb
>> go
>> drop table dbo.tb
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message >> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>> See below for the command and the format.
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>> Thanks in advance,
>> Ben Brugman
>> The command I use from the 'Query Analyser' in 2005.
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE =3D 'D:\folder\format.xml' );
>> The format file:
>> <?xml version=3D"1.0"?>
>> <BCPFORMAT >> =xmlns=3D"http://schemas.microsoft.com/sqlserver/2004/bulkload/format"=20
>> xmlns:xsi=3D"">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>> <FIELD ID=3D"01" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"02" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"51" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"52" xsi:type=3D"CharTerm" TERMINATOR=3D",\r\n" >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE=3D"01" NAME=3D"Field01 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"02" NAME=3D"Field02 " xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"51" NAME=3D"Field03 " xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"52" NAME=3D"Field04 " xsi:type=3D"SQLNVARCHAR"/>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>> > >
--=_NextPart_000_0021_01C82618.1ED36460
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server =Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the last =character in the last column.
- It can not cope with none quoted =strings, I get error from that.
It did cope correctly with a comma in a =string.
Below a detailled result, mainly your =code and example.
The word 'voorbeeld' is just the Dutch =word for 'example'.
Maybe I misunderstood your example, so =I have included the files
as I understood them to =be.
Did you try using READ method in =.NET to get the file.?I do not =understand this question, so I assume the anwser is no.
I copied the example from the message =in Outlook Express.
Am I doing something completely =wrong.
Ben Brugman
The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 =varchar(30),c2 varchar(30), c3 varchar(30))
delete dbo.voorbeeld_tb1
bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" 1 =c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post Material""D","MOVE","Misc Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; c2 =; = c3-- -- --"A &=nbsp; &n=bsp; ISSUE &n=bsp; &nb=sp; Misc Material Issue""B &=nbsp; &n=bsp; MOVE &nb=sp; &nbs=p; Misc Material Move""C &n=bsp; &nb=sp; POST &nb=sp; &nbs=p; Misc Post Material""D &nbs=p; =; MOVE &nb=sp; &nbs=p; Misc Material Move""A &n=bsp; &nb=sp; ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--
"Uri Dimant"
--=_NextPart_000_0021_01C82618.1ED36460--|||This is a multi-part message in MIME format.
--=_NextPart_000_0142_01C826AF.C6245020
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Ben
You are right. We got double quotes in the table. However you can use =REPLACE function to get rid of them.
I mean .READ method to read XML file in the VB.NET
"ben brugman" <ben@.niethier.nl> wrote in message =news:uOJQ%23%23gJIHA.4196@.TK2MSFTNGP04.phx.gbl...
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first character in the first column.
- There is a double quote as the last character in the last column.
- It can not cope with none quoted strings, I get error from that.
It did cope correctly with a comma in a string.
Below a detailled result, mainly your code and example.
The word 'voorbeeld' is just the Dutch word for 'example'.
Maybe I misunderstood your example, so I have included the files
as I understood them to be.
Did you try using READ method in .NET to get the file.?
I do not understand this question, so I assume the anwser is no.
I copied the example from the message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
=----=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
delete dbo.voorbeeld_tb1
bulk insert dbo.voorbeeld_tb1
from 'c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
=----=--
The used format file c:\voorbeeld_format.fmt
=----=--
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
=----=--
the content file c:\voorbeeld_content.txt :
=----=--
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
"A","ISSUE","Misc , extra"
=----=--
select * from dbo.voorbeeld_tb1 Delivers
=----=--
c1 c2 c3
-- -- =--
"A ISSUE Misc =Material Issue"
"B MOVE Misc =Material Move"
"C POST Misc =Post Material"
"D MOVE Misc =Material Move"
"A ISSUE Misc , =extra"
(5 row(s) affected)
=----=--
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Ben
> Remove it.
> 8.0
> 3
> 1 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 3 SQLCHAR 0 30 "\r\n" 3 c3 ""
> > > Did you try using READ method in .NET to get the file.?
> > "ben brugman" <ben@.niethier.nl> wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello Uri,
>>
>> Thanks for the quick reply,
>> I'll try to understand and use your method.
>>
>> In your example does the fmt_file.fmt start with the line starting =with 4 >> or the line starting with 8?
>> Tthe text file is the file with the 4 lines with the A/B/C/D =examples I >> assume?
>> Not all my fields are double quoted, not all strings are double =quoted >> (only the one's containing a comma), is this a problem?
>> You have three fields, but I see four definitions, how does this =work?
>>
>> Sorry to ask so many questions, but I did try to read up on the XML =format >> (not succesfully yet), so switching to another format is no =problem, but >> I'll like to understand the 'definitions' so that I do not run into =new >> problems, which I can not solve by myself.
>>
>> Also next to get the system working (with your example), I would =like to >> know how to work in the XML format and still be able to insert =double >> quoted strings when not all strings are double quoted.
>>
>> Thanks for your time and attention,
>> Ben Brugman
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message >> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>>
>>
>> I did not create a XML file ,howere this works for just great
>>
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
>>
>> go
>>
>> /*
>>
>> "A","ISSUE","Misc Material Issue"
>>
>> "B","MOVE","Misc Material Move"
>>
>> "C","POST","Misc Post Material"
>>
>> "D","MOVE","Misc Material Move"
>>
>> 8.0
>>
>> 4
>>
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>>
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>>
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>>
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>>
>> */
>>
>> bulk insert dbo.tb1
>>
>> from 'c:\txt_file.txt'
>>
>> with (formatfile=3D'c:\fmt_file.fmt')
>>
>> go
>>
>> select *
>>
>> from dbo.tb
>>
>> go
>>
>> drop table dbo.tb
>>
>>
>>
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message >> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>>
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> See below for the command and the format.
>>
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>>
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>>
>> Thanks in advance,
>> Ben Brugman
>>
>> The command I use from the 'Query Analyser' in 2005.
>>
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE =3D 'D:\folder\format.xml' );
>>
>> The format file:
>>
>> <?xml version=3D"1.0"?>
>> <BCPFORMAT >> =xmlns=3D"http://schemas.microsoft.com/sqlserver/2004/bulkload/format"=20
>> xmlns:xsi=3D"">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>>
>> <FIELD ID=3D"01" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"02" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> <FIELD ID=3D"51" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"52" xsi:type=3D"CharTerm" TERMINATOR=3D",\r\n" >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE=3D"01" NAME=3D"Field01 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"02" NAME=3D"Field02 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> <COLUMN SOURCE=3D"51" NAME=3D"Field03 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"52" NAME=3D"Field04 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>>
>>
>>
>>
>> > >
--=_NextPart_000_0142_01C826AF.C6245020
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Ben
You are right. We got double quotes in =the table. However you can use REPLACE function to get rid of them.
I mean .READ method to read XML file in =the VB.NET
"ben brugman"
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would = like.
I am working in MicroSoft SQL Server =Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the last =character in the last column.
- It can not cope with none quoted =strings, I get error from that.
It did cope correctly with a comma in =a string.
Below a detailled result, mainly your =code and example.
The word 'voorbeeld' is just the =Dutch word for 'example'.
Maybe I misunderstood your example, =so I have included the files
as I understood them to =be.
Did you try using READ method =in .NET to get the file.?I do not =understand this question, so I assume the anwser is no.
I copied the example from the message =in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 =varchar(30),c2 varchar(30), c3 varchar(30))
delete dbo.voorbeeld_tb1
bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" 1 =c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post Material""D","MOVE","Misc =Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; =c2 =; = c3-- -- =--"A &=nbsp; &n=bsp; =ISSUE &n=bsp; &nb=sp; Misc Material =Issue""B &=nbsp; &n=bsp; =MOVE &nb=sp; &nbs=p; Misc Material =Move""C &n=bsp; &nb=sp; =POST &nb=sp; &nbs=p; Misc Post =Material""D &nbs=p; =; =MOVE &nb=sp; &nbs=p; Misc Material =Move""A &n=bsp; &nb=sp; =ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--
"Uri Dimant"
--=_NextPart_000_0142_01C826AF.C6245020--|||This is a multi-part message in MIME format.
--=_NextPart_000_005A_01C826BC.39421B80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Hello Uri,
Yes the double quotes can be removed with a REPLACE, specific if they =are the first or last character.
But the main problem is still that not all strings are quoted and this =import loses track when a string is
not quoted. I get error messages. And sometimes it 'jumbles' up the =columns.
Also I tried to to continue with the xml definition file, no succes =there either.
The import files are delivered to me, but in total there are hundreds of =import files. So editing them is not realy an option.
As far as I remember this wasn't a problem in SQL-server 2000 using DTS, =but am not sure of that.
Thanks again for your time and attention.
ben
"Uri Dimant" <urid@.iscar.co.il> schreef in bericht =news:%232ikj8pJIHA.4592@.TK2MSFTNGP02.phx.gbl...
Ben
You are right. We got double quotes in the table. However you can use =REPLACE function to get rid of them.
I mean .READ method to read XML file in the VB.NET
"ben brugman" <ben@.niethier.nl> wrote in message =news:uOJQ%23%23gJIHA.4196@.TK2MSFTNGP04.phx.gbl...
Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first character in the first column.
- There is a double quote as the last character in the last column.
- It can not cope with none quoted strings, I get error from that.
It did cope correctly with a comma in a string.
Below a detailled result, mainly your code and example.
The word 'voorbeeld' is just the Dutch word for 'example'.
Maybe I misunderstood your example, so I have included the files
as I understood them to be.
Did you try using READ method in .NET to get the file.?
I do not understand this question, so I assume the anwser is no.
I copied the example from the message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
=----=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
delete dbo.voorbeeld_tb1
bulk insert dbo.voorbeeld_tb1
from 'c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
=----=--
The used format file c:\voorbeeld_format.fmt
=----=--
8.0
3
1 SQLCHAR 0 30 "\",\"" 1 c1 ""
2 SQLCHAR 0 30 "\",\"" 2 c2 ""
3 SQLCHAR 0 30 "\r\n" 3 c3 ""
=----=--
the content file c:\voorbeeld_content.txt :
=----=--
"A","ISSUE","Misc Material Issue"
"B","MOVE","Misc Material Move"
"C","POST","Misc Post Material"
"D","MOVE","Misc Material Move"
"A","ISSUE","Misc , extra"
=----=--
select * from dbo.voorbeeld_tb1 Delivers
=----=--
c1 c2 c3
-- -- =--
"A ISSUE Misc =Material Issue"
"B MOVE Misc =Material Move"
"C POST Misc =Post Material"
"D MOVE Misc =Material Move"
"A ISSUE Misc , =extra"
(5 row(s) affected)
=----=--
"Uri Dimant" <urid@.iscar.co.il> wrote in message =news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...
> Ben
> Remove it.
> 8.0
> 3
> 1 SQLCHAR 0 30 "\",\"" 1 c1 ""
> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""
> 3 SQLCHAR 0 30 "\r\n" 3 c3 ""
> > > Did you try using READ method in .NET to get the file.?
> > "ben brugman" <ben@.niethier.nl> wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Hello Uri,
>>
>> Thanks for the quick reply,
>> I'll try to understand and use your method.
>>
>> In your example does the fmt_file.fmt start with the line =starting with 4 >> or the line starting with 8?
>> Tthe text file is the file with the 4 lines with the A/B/C/D =examples I >> assume?
>> Not all my fields are double quoted, not all strings are double =quoted >> (only the one's containing a comma), is this a problem?
>> You have three fields, but I see four definitions, how does this =work?
>>
>> Sorry to ask so many questions, but I did try to read up on the =XML format >> (not succesfully yet), so switching to another format is no =problem, but >> I'll like to understand the 'definitions' so that I do not run =into new >> problems, which I can not solve by myself.
>>
>> Also next to get the system working (with your example), I would =like to >> know how to work in the XML format and still be able to insert =double >> quoted strings when not all strings are double quoted.
>>
>> Thanks for your time and attention,
>> Ben Brugman
>>
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message >> news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...
>> Ben
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>>
>>
>> I did not create a XML file ,howere this works for just great
>>
>>
>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))
>>
>> go
>>
>> /*
>>
>> "A","ISSUE","Misc Material Issue"
>>
>> "B","MOVE","Misc Material Move"
>>
>> "C","POST","Misc Post Material"
>>
>> "D","MOVE","Misc Material Move"
>>
>> 8.0
>>
>> 4
>>
>> 1 SQLCHAR 0 0 "\"" 0 first_quote ""
>>
>> 2 SQLCHAR 0 30 "\",\"" 1 c1 ""
>>
>> 3 SQLCHAR 0 30 "\",\"" 2 c2 ""
>>
>> 4 SQLCHAR 0 30 "\r\n" 3 c3 ""
>>
>> */
>>
>> bulk insert dbo.tb1
>>
>> from 'c:\txt_file.txt'
>>
>> with (formatfile=3D'c:\fmt_file.fmt')
>>
>> go
>>
>> select *
>>
>> from dbo.tb
>>
>> go
>>
>> drop table dbo.tb
>>
>>
>>
>>
>>
>> "ben brugman" <ben@.niethier.nl> wrote in message >> news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...
>> Hello,
>>
>> With bulk insert I am trying to insert data.
>> Data is comma delimited.
>> And strings containing comma's are double quoted.
>>
>> See below for the command and the format.
>>
>> I am doing something wrong because strings with
>> comma's in them get split over more fields.
>>
>> This should be simple but haven't found the answer yet.
>> What should I change in de command, or in the format file?
>>
>> Thanks in advance,
>> Ben Brugman
>>
>> The command I use from the 'Query Analyser' in 2005.
>>
>> BULK INSERT IMP FROM 'D:\folder\import.txt'
>> WITH (FORMATFILE =3D 'D:\folder\format.xml' );
>>
>> The format file:
>>
>> <?xml version=3D"1.0"?>
>> <BCPFORMAT >> =xmlns=3D"http://schemas.microsoft.com/sqlserver/2004/bulkload/format"=20
>> xmlns:xsi=3D"">http://www.w3.org/2001/XMLSchema-instance">
>> <RECORD>
>>
>>
>> <FIELD ID=3D"01" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"02" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> <FIELD ID=3D"51" xsi:type=3D"CharTerm" TERMINATOR=3D"," >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>> <FIELD ID=3D"52" xsi:type=3D"CharTerm" TERMINATOR=3D",\r\n" >> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>
>>
>> </RECORD>
>> <ROW>
>> <COLUMN SOURCE=3D"01" NAME=3D"Field01 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"02" NAME=3D"Field02 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> <COLUMN SOURCE=3D"51" NAME=3D"Field03 " =xsi:type=3D"SQLNVARCHAR"/>
>> <COLUMN SOURCE=3D"52" NAME=3D"Field04 " =xsi:type=3D"SQLNVARCHAR"/>
>>
>> </ROW>
>> </BCPFORMAT>
>>
>>
>>
>>
>>
>>
>>
>> > >
--=_NextPart_000_005A_01C826BC.39421B80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Hello Uri,
Yes the double quotes can be removed =with a REPLACE, specific if they are the first or last character.
But the main problem is still that not =all strings are quoted and this import loses track when a string is
not quoted. I get error messages. And =sometimes it 'jumbles' up the columns.
Also I tried to to continue with the =xml definition file, no succes there either.
The import files are delivered to me, =but in total there are hundreds of import files. So editing them is not realy an option.
As far as I remember this wasn't a =problem in SQL-server 2000 using DTS, but am not sure of that.
Thanks again for your time and attention.
ben
"Uri Dimant"
Ben
You are right. We got double quotes =in the table. However you can use REPLACE function to get rid of them.
I mean .READ method to read XML file =in the VB.NET
"ben brugman"
Hello Uri,
I tried your script, as I =understood it.
But I can't get it to work as I =would like.
I am working in MicroSoft SQL =Server Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the =last character in the last column.
- It can not cope with none quoted =strings, I get error from that.
It did cope correctly with a comma =in a string.
Below a detailled result, mainly =your code and example.
The word 'voorbeeld' is just the =Dutch word for 'example'.
Maybe I misunderstood your example, =so I have included the files
as I understood them to =be.
Did you try using READ method =in .NET to get the file.?I =do not understand this question, so I assume the anwser is no.
I copied the example from the =message in Outlook Express.
Am I doing something completely wrong.
Ben Brugman
The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))
delete =dbo.voorbeeld_tb1
bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" =1 c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post =Material""D","MOVE","Misc Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; =c2 =; = c3-- -- =--"A &=nbsp; &n=bsp; =ISSUE &n=bsp; &nb=sp; Misc Material =Issue""B &=nbsp; &n=bsp; =MOVE &nb=sp; &nbs=p; Misc Material =Move""C &n=bsp; &nb=sp; =POST &nb=sp; &nbs=p; Misc Post =Material""D &nbs=p; =; =MOVE &nb=sp; &nbs=p; Misc Material =Move""A &n=bsp; &nb=sp; =ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--
"Uri Dimant"
--=_NextPart_000_005A_01C826BC.39421B80--
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...
>
>
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...
>
>
Monday, March 19, 2012
importing delimited files...
Hi,
I am importing a batch of comma delimited files. The process reads several
files and then moves them to an archive folder.
The process reads each line of each file into a temp db, but it does not
parse the line out onto fields. It just dumps the whole line into the table.
What I need is to be able to take that data and parse it out and insert it
into my new table, either AFTER the text has been imported or AS it is
imported.
Here is an example of the data that is being imported:
FLD1---
9876543.00, "Mr. John Doe, Jr.","John","Doe","1234","MyCity","MyState"
So I really need that to be into a table in individual fields
Any ideas on how to split this string up after or as it comes into SQL?
MartyUse Data Transformation Services.
AMB
"M.Smith" wrote:
> Hi,
> I am importing a batch of comma delimited files. The process reads several
> files and then moves them to an archive folder.
> The process reads each line of each file into a temp db, but it does not
> parse the line out onto fields. It just dumps the whole line into the tabl
e.
> What I need is to be able to take that data and parse it out and insert it
> into my new table, either AFTER the text has been imported or AS it is
> imported.
> Here is an example of the data that is being imported:
> FLD1---
> 9876543.00, "Mr. John Doe, Jr.","John","Doe","1234","MyCity","MyState"
> So I really need that to be into a table in individual fields
> Any ideas on how to split this string up after or as it comes into SQL?
> Marty
>
>|||Any more explanation on this response? Is there a reason you said use
DTS...you kind of left me hanging...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6F2C2699-F892-49DD-8CE9-6F90551299C2@.microsoft.com...
> Use Data Transformation Services.
>
> AMB
> "M.Smith" wrote:
>|||With DTS, you can specify a source file with certain attributes and a
destination and any transformations that you want. For instance, you can
make a Text File (Source) object and tell it that you have a comma-delimited
file. Then you can make a SQL Server object and point it at your database.
Than you can define a Data Pump that connects the two and performs any
transformations that you want on the data. You cound throw together a simpl
e
one in less that ten minutes.
Chris
"M.Smith" wrote:
> Any more explanation on this response? Is there a reason you said use
> DTS...you kind of left me hanging...
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:6F2C2699-F892-49DD-8CE9-6F90551299C2@.microsoft.com...
>
>
I am importing a batch of comma delimited files. The process reads several
files and then moves them to an archive folder.
The process reads each line of each file into a temp db, but it does not
parse the line out onto fields. It just dumps the whole line into the table.
What I need is to be able to take that data and parse it out and insert it
into my new table, either AFTER the text has been imported or AS it is
imported.
Here is an example of the data that is being imported:
FLD1---
9876543.00, "Mr. John Doe, Jr.","John","Doe","1234","MyCity","MyState"
So I really need that to be into a table in individual fields
Any ideas on how to split this string up after or as it comes into SQL?
MartyUse Data Transformation Services.
AMB
"M.Smith" wrote:
> Hi,
> I am importing a batch of comma delimited files. The process reads several
> files and then moves them to an archive folder.
> The process reads each line of each file into a temp db, but it does not
> parse the line out onto fields. It just dumps the whole line into the tabl
e.
> What I need is to be able to take that data and parse it out and insert it
> into my new table, either AFTER the text has been imported or AS it is
> imported.
> Here is an example of the data that is being imported:
> FLD1---
> 9876543.00, "Mr. John Doe, Jr.","John","Doe","1234","MyCity","MyState"
> So I really need that to be into a table in individual fields
> Any ideas on how to split this string up after or as it comes into SQL?
> Marty
>
>|||Any more explanation on this response? Is there a reason you said use
DTS...you kind of left me hanging...
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:6F2C2699-F892-49DD-8CE9-6F90551299C2@.microsoft.com...
> Use Data Transformation Services.
>
> AMB
> "M.Smith" wrote:
>|||With DTS, you can specify a source file with certain attributes and a
destination and any transformations that you want. For instance, you can
make a Text File (Source) object and tell it that you have a comma-delimited
file. Then you can make a SQL Server object and point it at your database.
Than you can define a Data Pump that connects the two and performs any
transformations that you want on the data. You cound throw together a simpl
e
one in less that ten minutes.
Chris
"M.Smith" wrote:
> Any more explanation on this response? Is there a reason you said use
> DTS...you kind of left me hanging...
>
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:6F2C2699-F892-49DD-8CE9-6F90551299C2@.microsoft.com...
>
>
Monday, March 12, 2012
importing datetime data
My flat file I import to the table is set up as 2004/06/16 09:40:07.994 comma delimited, and i import this using DTS into a datetime field. but when I run a query on the table, the only thing I see is 2004/06/16 showing up, how come I can't see the time?
Thx for your help!What tool are you using to query the table? It may be set to display DATETIME values as only the date (this is common if using the regional settings on the client with an ODBC based tool).
-PatP
Thx for your help!What tool are you using to query the table? It may be set to display DATETIME values as only the date (this is common if using the regional settings on the client with an ODBC based tool).
-PatP
Friday, February 24, 2012
Importing Comma Delimited Data.
Hello,
I am trying to import files (hundreds) with Comma Delimited Data, because
some columns within the data contain comma's themselves those fields have
been double qouted.
Small example.
1234, hello John, mega
2345, " Hello Andrew, Marie", tall
I have tried to use Bulk insert with a Formatfile in xml and suggested by
Uri Dimant a formatted file.
But with the bulk insert, the field with the comma breaks into two sepperate
fields and this generetes
sometimes an error because the fields are not in sync.
With the formatted file the lines without quotes do not work correctly. I
tried to read the help files on this, but could not find a solution to the
problem.
In total it's about millions of rows, in hundreds of files, for a number off
tables.
Can anybody give advise how to solve this, or where to look in the help
system?
Thanks for your time and attention,
Ben Brugman> Can anybody give advise how to solve this, or where to look in the help
> system?
SSIS will better handle enclosed fields. You can use the import data wizard
from SQL Server Management Studio to generate the package. Right-click the
database in object explorer and select Tasks-->Import Data.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:32e3c$473aef81$53557893$24038@.cache
100.multikabel.net...
> Hello,
> I am trying to import files (hundreds) with Comma Delimited Data, because
> some columns within the data contain comma's themselves those fields have
> been double qouted.
> Small example.
> 1234, hello John, mega
> 2345, " Hello Andrew, Marie", tall
> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> Uri Dimant a formatted file.
> But with the bulk insert, the field with the comma breaks into two
> sepperate fields and this generetes
> sometimes an error because the fields are not in sync.
> With the formatted file the lines without quotes do not work correctly. I
> tried to read the help files on this, but could not find a solution to the
> problem.
> In total it's about millions of rows, in hundreds of files, for a number
> off tables.
> Can anybody give advise how to solve this, or where to look in the help
> system?
> Thanks for your time and attention,
> Ben Brugman
>|||Thanks Dan,
At the moment the target system does not have SSIS installed.
(I'll will ask for SSIS to be installed on the target system.)
Two questions:
Is there another solution, because I should think that normal import
procedures like bulk insert should be able to handle Comma delimited files?
(To my knowledge my situation is fairly common).
Because I allready tried two differend methods, which I could not get to
work I am a bit apprihencive to try a third method, is SSIS going to work
for this kind of situations, or is this something I just have to try?
This is actually my first project really working with SQLserver 2005. With
the previous version I normally succeeded with importing everything I wanted
to import, so at the moment I am feeling a bit stupid. Spending time on the
methods I could not get to work is rather frustrating,
Thanks for your attention and advise.
Ben Brugman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
> SSIS will better handle enclosed fields. You can use the import data
> wizard from SQL Server Management Studio to generate the package.
> Right-click the database in object explorer and select Tasks-->Import
> Data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:32e3c$473aef81$53557893$24038@.cache
100.multikabel.net...
>|||> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
BCP and BULK INSERT do a good job of parsing files as long as field
terminators do not occur in the data. For more advanced parsing, SSIS is a
better choice.
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
I understand your frustration. However, I can tell you that I've
successfully used SSIS to import many CSV files in a loop using a ForEach
task that executes a DataFlow for each file. There is some learning curve
if you've never used SSIS before so you might find an examples helpful. An
example of this technique at http://www.sqlis.com/55.aspx. Just be sure to
specify quotes (") as the text qualifier for the flat file connection of the
data flow task.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:8b4e8$473af645$53557893$23893@.cache
120.multikabel.net...
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I
> wanted to import, so at the moment I am feeling a bit stupid. Spending
> time on the methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>|||BULK INSERT and BCP are designed for speed not versatility in handling
different formats. If you don't want to try SSIS, another solution I often
use is to pre-process the files into an even simpler format before feeding
them to BCP/BULK INSERT.
Linchi
"ben brugman" wrote:
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited files
?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I want
ed
> to import, so at the moment I am feeling a bit stupid. Spending time on th
e
> methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>
I am trying to import files (hundreds) with Comma Delimited Data, because
some columns within the data contain comma's themselves those fields have
been double qouted.
Small example.
1234, hello John, mega
2345, " Hello Andrew, Marie", tall
I have tried to use Bulk insert with a Formatfile in xml and suggested by
Uri Dimant a formatted file.
But with the bulk insert, the field with the comma breaks into two sepperate
fields and this generetes
sometimes an error because the fields are not in sync.
With the formatted file the lines without quotes do not work correctly. I
tried to read the help files on this, but could not find a solution to the
problem.
In total it's about millions of rows, in hundreds of files, for a number off
tables.
Can anybody give advise how to solve this, or where to look in the help
system?
Thanks for your time and attention,
Ben Brugman> Can anybody give advise how to solve this, or where to look in the help
> system?
SSIS will better handle enclosed fields. You can use the import data wizard
from SQL Server Management Studio to generate the package. Right-click the
database in object explorer and select Tasks-->Import Data.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:32e3c$473aef81$53557893$24038@.cache
100.multikabel.net...
> Hello,
> I am trying to import files (hundreds) with Comma Delimited Data, because
> some columns within the data contain comma's themselves those fields have
> been double qouted.
> Small example.
> 1234, hello John, mega
> 2345, " Hello Andrew, Marie", tall
> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> Uri Dimant a formatted file.
> But with the bulk insert, the field with the comma breaks into two
> sepperate fields and this generetes
> sometimes an error because the fields are not in sync.
> With the formatted file the lines without quotes do not work correctly. I
> tried to read the help files on this, but could not find a solution to the
> problem.
> In total it's about millions of rows, in hundreds of files, for a number
> off tables.
> Can anybody give advise how to solve this, or where to look in the help
> system?
> Thanks for your time and attention,
> Ben Brugman
>|||Thanks Dan,
At the moment the target system does not have SSIS installed.
(I'll will ask for SSIS to be installed on the target system.)
Two questions:
Is there another solution, because I should think that normal import
procedures like bulk insert should be able to handle Comma delimited files?
(To my knowledge my situation is fairly common).
Because I allready tried two differend methods, which I could not get to
work I am a bit apprihencive to try a third method, is SSIS going to work
for this kind of situations, or is this something I just have to try?
This is actually my first project really working with SQLserver 2005. With
the previous version I normally succeeded with importing everything I wanted
to import, so at the moment I am feeling a bit stupid. Spending time on the
methods I could not get to work is rather frustrating,
Thanks for your attention and advise.
Ben Brugman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
> SSIS will better handle enclosed fields. You can use the import data
> wizard from SQL Server Management Studio to generate the package.
> Right-click the database in object explorer and select Tasks-->Import
> Data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:32e3c$473aef81$53557893$24038@.cache
100.multikabel.net...
>|||> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
BCP and BULK INSERT do a good job of parsing files as long as field
terminators do not occur in the data. For more advanced parsing, SSIS is a
better choice.
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
I understand your frustration. However, I can tell you that I've
successfully used SSIS to import many CSV files in a loop using a ForEach
task that executes a DataFlow for each file. There is some learning curve
if you've never used SSIS before so you might find an examples helpful. An
example of this technique at http://www.sqlis.com/55.aspx. Just be sure to
specify quotes (") as the text qualifier for the flat file connection of the
data flow task.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:8b4e8$473af645$53557893$23893@.cache
120.multikabel.net...
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I
> wanted to import, so at the moment I am feeling a bit stupid. Spending
> time on the methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>|||BULK INSERT and BCP are designed for speed not versatility in handling
different formats. If you don't want to try SSIS, another solution I often
use is to pre-process the files into an even simpler format before feeding
them to BCP/BULK INSERT.
Linchi
"ben brugman" wrote:
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited files
?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I want
ed
> to import, so at the moment I am feeling a bit stupid. Spending time on th
e
> methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>
Importing Comma Delimited Data.
Hello,
I am trying to import files (hundreds) with Comma Delimited Data, because
some columns within the data contain comma's themselves those fields have
been double qouted.
Small example.
1234, hello John, mega
2345, " Hello Andrew, Marie", tall
I have tried to use Bulk insert with a Formatfile in xml and suggested by
Uri Dimant a formatted file.
But with the bulk insert, the field with the comma breaks into two sepperate
fields and this generetes
sometimes an error because the fields are not in sync.
With the formatted file the lines without quotes do not work correctly. I
tried to read the help files on this, but could not find a solution to the
problem.
In total it's about millions of rows, in hundreds of files, for a number off
tables.
Can anybody give advise how to solve this, or where to look in the help
system?
Thanks for your time and attention,
Ben Brugman> Can anybody give advise how to solve this, or where to look in the help
> system?
SSIS will better handle enclosed fields. You can use the import data wizard
from SQL Server Management Studio to generate the package. Right-click the
database in object explorer and select Tasks-->Import Data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
> Hello,
> I am trying to import files (hundreds) with Comma Delimited Data, because
> some columns within the data contain comma's themselves those fields have
> been double qouted.
> Small example.
> 1234, hello John, mega
> 2345, " Hello Andrew, Marie", tall
> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> Uri Dimant a formatted file.
> But with the bulk insert, the field with the comma breaks into two
> sepperate fields and this generetes
> sometimes an error because the fields are not in sync.
> With the formatted file the lines without quotes do not work correctly. I
> tried to read the help files on this, but could not find a solution to the
> problem.
> In total it's about millions of rows, in hundreds of files, for a number
> off tables.
> Can anybody give advise how to solve this, or where to look in the help
> system?
> Thanks for your time and attention,
> Ben Brugman
>|||Thanks Dan,
At the moment the target system does not have SSIS installed.
(I'll will ask for SSIS to be installed on the target system.)
Two questions:
Is there another solution, because I should think that normal import
procedures like bulk insert should be able to handle Comma delimited files?
(To my knowledge my situation is fairly common).
Because I allready tried two differend methods, which I could not get to
work I am a bit apprihencive to try a third method, is SSIS going to work
for this kind of situations, or is this something I just have to try?
This is actually my first project really working with SQLserver 2005. With
the previous version I normally succeeded with importing everything I wanted
to import, so at the moment I am feeling a bit stupid. Spending time on the
methods I could not get to work is rather frustrating,
Thanks for your attention and advise.
Ben Brugman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
> SSIS will better handle enclosed fields. You can use the import data
> wizard from SQL Server Management Studio to generate the package.
> Right-click the database in object explorer and select Tasks-->Import
> Data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> Hello,
>> I am trying to import files (hundreds) with Comma Delimited Data, because
>> some columns within the data contain comma's themselves those fields have
>> been double qouted.
>> Small example.
>> 1234, hello John, mega
>> 2345, " Hello Andrew, Marie", tall
>> I have tried to use Bulk insert with a Formatfile in xml and suggested by
>> Uri Dimant a formatted file.
>> But with the bulk insert, the field with the comma breaks into two
>> sepperate fields and this generetes
>> sometimes an error because the fields are not in sync.
>> With the formatted file the lines without quotes do not work correctly. I
>> tried to read the help files on this, but could not find a solution to
>> the problem.
>> In total it's about millions of rows, in hundreds of files, for a number
>> off tables.
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> Thanks for your time and attention,
>> Ben Brugman
>|||> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
BCP and BULK INSERT do a good job of parsing files as long as field
terminators do not occur in the data. For more advanced parsing, SSIS is a
better choice.
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
I understand your frustration. However, I can tell you that I've
successfully used SSIS to import many CSV files in a loop using a ForEach
task that executes a DataFlow for each file. There is some learning curve
if you've never used SSIS before so you might find an examples helpful. An
example of this technique at http://www.sqlis.com/55.aspx. Just be sure to
specify quotes (") as the text qualifier for the flat file connection of the
data flow task.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:8b4e8$473af645$53557893$23893@.cache120.multikabel.net...
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I
> wanted to import, so at the moment I am feeling a bit stupid. Spending
> time on the methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> SSIS will better handle enclosed fields. You can use the import data
>> wizard from SQL Server Management Studio to generate the package.
>> Right-click the database in object explorer and select Tasks-->Import
>> Data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "ben brugman" <ben@.niethier.nl> wrote in message
>> news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> Hello,
>> I am trying to import files (hundreds) with Comma Delimited Data,
>> because some columns within the data contain comma's themselves those
>> fields have been double qouted.
>> Small example.
>> 1234, hello John, mega
>> 2345, " Hello Andrew, Marie", tall
>> I have tried to use Bulk insert with a Formatfile in xml and suggested
>> by Uri Dimant a formatted file.
>> But with the bulk insert, the field with the comma breaks into two
>> sepperate fields and this generetes
>> sometimes an error because the fields are not in sync.
>> With the formatted file the lines without quotes do not work correctly.
>> I tried to read the help files on this, but could not find a solution to
>> the problem.
>> In total it's about millions of rows, in hundreds of files, for a number
>> off tables.
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> Thanks for your time and attention,
>> Ben Brugman
>>
>|||BULK INSERT and BCP are designed for speed not versatility in handling
different formats. If you don't want to try SSIS, another solution I often
use is to pre-process the files into an even simpler format before feeding
them to BCP/BULK INSERT.
Linchi
"ben brugman" wrote:
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I wanted
> to import, so at the moment I am feeling a bit stupid. Spending time on the
> methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
> >> Can anybody give advise how to solve this, or where to look in the help
> >> system?
> >
> > SSIS will better handle enclosed fields. You can use the import data
> > wizard from SQL Server Management Studio to generate the package.
> > Right-click the database in object explorer and select Tasks-->Import
> > Data.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
> >> Hello,
> >>
> >> I am trying to import files (hundreds) with Comma Delimited Data, because
> >> some columns within the data contain comma's themselves those fields have
> >> been double qouted.
> >>
> >> Small example.
> >>
> >> 1234, hello John, mega
> >> 2345, " Hello Andrew, Marie", tall
> >>
> >> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> >> Uri Dimant a formatted file.
> >> But with the bulk insert, the field with the comma breaks into two
> >> sepperate fields and this generetes
> >> sometimes an error because the fields are not in sync.
> >>
> >> With the formatted file the lines without quotes do not work correctly. I
> >> tried to read the help files on this, but could not find a solution to
> >> the problem.
> >>
> >> In total it's about millions of rows, in hundreds of files, for a number
> >> off tables.
> >>
> >> Can anybody give advise how to solve this, or where to look in the help
> >> system?
> >>
> >> Thanks for your time and attention,
> >> Ben Brugman
> >>
> >
>|||All thanks for you participation,
Dan and Linchi, thanks for your comments.
At the moment we have adopted the solution of dropping the column which was
causing all the problems.
When we do need the column, we will probably end it with a not used symbol
or set of symbols, or quoting all strings for this column.
Thanks for you time and attention,
Ben
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> schreef in bericht
news:270A3E1B-6300-43F0-B30C-C27B8291912C@.microsoft.com...
> BULK INSERT and BCP are designed for speed not versatility in handling
> different formats. If you don't want to try SSIS, another solution I often
> use is to pre-process the files into an even simpler format before feeding
> them to BCP/BULK INSERT.
> Linchi
> "ben brugman" wrote:
>> Thanks Dan,
>> At the moment the target system does not have SSIS installed.
>> (I'll will ask for SSIS to be installed on the target system.)
>> Two questions:
>> Is there another solution, because I should think that normal import
>> procedures like bulk insert should be able to handle Comma delimited
>> files?
>> (To my knowledge my situation is fairly common).
>> Because I allready tried two differend methods, which I could not get to
>> work I am a bit apprihencive to try a third method, is SSIS going to work
>> for this kind of situations, or is this something I just have to try?
>> This is actually my first project really working with SQLserver 2005.
>> With
>> the previous version I normally succeeded with importing everything I
>> wanted
>> to import, so at the moment I am feeling a bit stupid. Spending time on
>> the
>> methods I could not get to work is rather frustrating,
>> Thanks for your attention and advise.
>> Ben Brugman
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
>> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> >> Can anybody give advise how to solve this, or where to look in the
>> >> help
>> >> system?
>> >
>> > SSIS will better handle enclosed fields. You can use the import data
>> > wizard from SQL Server Management Studio to generate the package.
>> > Right-click the database in object explorer and select Tasks-->Import
>> > Data.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "ben brugman" <ben@.niethier.nl> wrote in message
>> > news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> >> Hello,
>> >>
>> >> I am trying to import files (hundreds) with Comma Delimited Data,
>> >> because
>> >> some columns within the data contain comma's themselves those fields
>> >> have
>> >> been double qouted.
>> >>
>> >> Small example.
>> >>
>> >> 1234, hello John, mega
>> >> 2345, " Hello Andrew, Marie", tall
>> >>
>> >> I have tried to use Bulk insert with a Formatfile in xml and suggested
>> >> by
>> >> Uri Dimant a formatted file.
>> >> But with the bulk insert, the field with the comma breaks into two
>> >> sepperate fields and this generetes
>> >> sometimes an error because the fields are not in sync.
>> >>
>> >> With the formatted file the lines without quotes do not work
>> >> correctly. I
>> >> tried to read the help files on this, but could not find a solution to
>> >> the problem.
>> >>
>> >> In total it's about millions of rows, in hundreds of files, for a
>> >> number
>> >> off tables.
>> >>
>> >> Can anybody give advise how to solve this, or where to look in the
>> >> help
>> >> system?
>> >>
>> >> Thanks for your time and attention,
>> >> Ben Brugman
>> >>
>> >
>>
I am trying to import files (hundreds) with Comma Delimited Data, because
some columns within the data contain comma's themselves those fields have
been double qouted.
Small example.
1234, hello John, mega
2345, " Hello Andrew, Marie", tall
I have tried to use Bulk insert with a Formatfile in xml and suggested by
Uri Dimant a formatted file.
But with the bulk insert, the field with the comma breaks into two sepperate
fields and this generetes
sometimes an error because the fields are not in sync.
With the formatted file the lines without quotes do not work correctly. I
tried to read the help files on this, but could not find a solution to the
problem.
In total it's about millions of rows, in hundreds of files, for a number off
tables.
Can anybody give advise how to solve this, or where to look in the help
system?
Thanks for your time and attention,
Ben Brugman> Can anybody give advise how to solve this, or where to look in the help
> system?
SSIS will better handle enclosed fields. You can use the import data wizard
from SQL Server Management Studio to generate the package. Right-click the
database in object explorer and select Tasks-->Import Data.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
> Hello,
> I am trying to import files (hundreds) with Comma Delimited Data, because
> some columns within the data contain comma's themselves those fields have
> been double qouted.
> Small example.
> 1234, hello John, mega
> 2345, " Hello Andrew, Marie", tall
> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> Uri Dimant a formatted file.
> But with the bulk insert, the field with the comma breaks into two
> sepperate fields and this generetes
> sometimes an error because the fields are not in sync.
> With the formatted file the lines without quotes do not work correctly. I
> tried to read the help files on this, but could not find a solution to the
> problem.
> In total it's about millions of rows, in hundreds of files, for a number
> off tables.
> Can anybody give advise how to solve this, or where to look in the help
> system?
> Thanks for your time and attention,
> Ben Brugman
>|||Thanks Dan,
At the moment the target system does not have SSIS installed.
(I'll will ask for SSIS to be installed on the target system.)
Two questions:
Is there another solution, because I should think that normal import
procedures like bulk insert should be able to handle Comma delimited files?
(To my knowledge my situation is fairly common).
Because I allready tried two differend methods, which I could not get to
work I am a bit apprihencive to try a third method, is SSIS going to work
for this kind of situations, or is this something I just have to try?
This is actually my first project really working with SQLserver 2005. With
the previous version I normally succeeded with importing everything I wanted
to import, so at the moment I am feeling a bit stupid. Spending time on the
methods I could not get to work is rather frustrating,
Thanks for your attention and advise.
Ben Brugman
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
> SSIS will better handle enclosed fields. You can use the import data
> wizard from SQL Server Management Studio to generate the package.
> Right-click the database in object explorer and select Tasks-->Import
> Data.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> Hello,
>> I am trying to import files (hundreds) with Comma Delimited Data, because
>> some columns within the data contain comma's themselves those fields have
>> been double qouted.
>> Small example.
>> 1234, hello John, mega
>> 2345, " Hello Andrew, Marie", tall
>> I have tried to use Bulk insert with a Formatfile in xml and suggested by
>> Uri Dimant a formatted file.
>> But with the bulk insert, the field with the comma breaks into two
>> sepperate fields and this generetes
>> sometimes an error because the fields are not in sync.
>> With the formatted file the lines without quotes do not work correctly. I
>> tried to read the help files on this, but could not find a solution to
>> the problem.
>> In total it's about millions of rows, in hundreds of files, for a number
>> off tables.
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> Thanks for your time and attention,
>> Ben Brugman
>|||> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
BCP and BULK INSERT do a good job of parsing files as long as field
terminators do not occur in the data. For more advanced parsing, SSIS is a
better choice.
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
I understand your frustration. However, I can tell you that I've
successfully used SSIS to import many CSV files in a loop using a ForEach
task that executes a DataFlow for each file. There is some learning curve
if you've never used SSIS before so you might find an examples helpful. An
example of this technique at http://www.sqlis.com/55.aspx. Just be sure to
specify quotes (") as the text qualifier for the flat file connection of the
data flow task.
Hope this helps.
Dan Guzman
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:8b4e8$473af645$53557893$23893@.cache120.multikabel.net...
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited
> files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I
> wanted to import, so at the moment I am feeling a bit stupid. Spending
> time on the methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> SSIS will better handle enclosed fields. You can use the import data
>> wizard from SQL Server Management Studio to generate the package.
>> Right-click the database in object explorer and select Tasks-->Import
>> Data.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "ben brugman" <ben@.niethier.nl> wrote in message
>> news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> Hello,
>> I am trying to import files (hundreds) with Comma Delimited Data,
>> because some columns within the data contain comma's themselves those
>> fields have been double qouted.
>> Small example.
>> 1234, hello John, mega
>> 2345, " Hello Andrew, Marie", tall
>> I have tried to use Bulk insert with a Formatfile in xml and suggested
>> by Uri Dimant a formatted file.
>> But with the bulk insert, the field with the comma breaks into two
>> sepperate fields and this generetes
>> sometimes an error because the fields are not in sync.
>> With the formatted file the lines without quotes do not work correctly.
>> I tried to read the help files on this, but could not find a solution to
>> the problem.
>> In total it's about millions of rows, in hundreds of files, for a number
>> off tables.
>> Can anybody give advise how to solve this, or where to look in the help
>> system?
>> Thanks for your time and attention,
>> Ben Brugman
>>
>|||BULK INSERT and BCP are designed for speed not versatility in handling
different formats. If you don't want to try SSIS, another solution I often
use is to pre-process the files into an even simpler format before feeding
them to BCP/BULK INSERT.
Linchi
"ben brugman" wrote:
> Thanks Dan,
> At the moment the target system does not have SSIS installed.
> (I'll will ask for SSIS to be installed on the target system.)
> Two questions:
> Is there another solution, because I should think that normal import
> procedures like bulk insert should be able to handle Comma delimited files?
> (To my knowledge my situation is fairly common).
> Because I allready tried two differend methods, which I could not get to
> work I am a bit apprihencive to try a third method, is SSIS going to work
> for this kind of situations, or is this something I just have to try?
> This is actually my first project really working with SQLserver 2005. With
> the previous version I normally succeeded with importing everything I wanted
> to import, so at the moment I am feeling a bit stupid. Spending time on the
> methods I could not get to work is rather frustrating,
> Thanks for your attention and advise.
> Ben Brugman
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
> >> Can anybody give advise how to solve this, or where to look in the help
> >> system?
> >
> > SSIS will better handle enclosed fields. You can use the import data
> > wizard from SQL Server Management Studio to generate the package.
> > Right-click the database in object explorer and select Tasks-->Import
> > Data.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "ben brugman" <ben@.niethier.nl> wrote in message
> > news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
> >> Hello,
> >>
> >> I am trying to import files (hundreds) with Comma Delimited Data, because
> >> some columns within the data contain comma's themselves those fields have
> >> been double qouted.
> >>
> >> Small example.
> >>
> >> 1234, hello John, mega
> >> 2345, " Hello Andrew, Marie", tall
> >>
> >> I have tried to use Bulk insert with a Formatfile in xml and suggested by
> >> Uri Dimant a formatted file.
> >> But with the bulk insert, the field with the comma breaks into two
> >> sepperate fields and this generetes
> >> sometimes an error because the fields are not in sync.
> >>
> >> With the formatted file the lines without quotes do not work correctly. I
> >> tried to read the help files on this, but could not find a solution to
> >> the problem.
> >>
> >> In total it's about millions of rows, in hundreds of files, for a number
> >> off tables.
> >>
> >> Can anybody give advise how to solve this, or where to look in the help
> >> system?
> >>
> >> Thanks for your time and attention,
> >> Ben Brugman
> >>
> >
>|||All thanks for you participation,
Dan and Linchi, thanks for your comments.
At the moment we have adopted the solution of dropping the column which was
causing all the problems.
When we do need the column, we will probably end it with a not used symbol
or set of symbols, or quoting all strings for this column.
Thanks for you time and attention,
Ben
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> schreef in bericht
news:270A3E1B-6300-43F0-B30C-C27B8291912C@.microsoft.com...
> BULK INSERT and BCP are designed for speed not versatility in handling
> different formats. If you don't want to try SSIS, another solution I often
> use is to pre-process the files into an even simpler format before feeding
> them to BCP/BULK INSERT.
> Linchi
> "ben brugman" wrote:
>> Thanks Dan,
>> At the moment the target system does not have SSIS installed.
>> (I'll will ask for SSIS to be installed on the target system.)
>> Two questions:
>> Is there another solution, because I should think that normal import
>> procedures like bulk insert should be able to handle Comma delimited
>> files?
>> (To my knowledge my situation is fairly common).
>> Because I allready tried two differend methods, which I could not get to
>> work I am a bit apprihencive to try a third method, is SSIS going to work
>> for this kind of situations, or is this something I just have to try?
>> This is actually my first project really working with SQLserver 2005.
>> With
>> the previous version I normally succeeded with importing everything I
>> wanted
>> to import, so at the moment I am feeling a bit stupid. Spending time on
>> the
>> methods I could not get to work is rather frustrating,
>> Thanks for your attention and advise.
>> Ben Brugman
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> schreef in bericht
>> news:%2369Uz5rJIHA.280@.TK2MSFTNGP03.phx.gbl...
>> >> Can anybody give advise how to solve this, or where to look in the
>> >> help
>> >> system?
>> >
>> > SSIS will better handle enclosed fields. You can use the import data
>> > wizard from SQL Server Management Studio to generate the package.
>> > Right-click the database in object explorer and select Tasks-->Import
>> > Data.
>> >
>> > --
>> > Hope this helps.
>> >
>> > Dan Guzman
>> > SQL Server MVP
>> >
>> > "ben brugman" <ben@.niethier.nl> wrote in message
>> > news:32e3c$473aef81$53557893$24038@.cache100.multikabel.net...
>> >> Hello,
>> >>
>> >> I am trying to import files (hundreds) with Comma Delimited Data,
>> >> because
>> >> some columns within the data contain comma's themselves those fields
>> >> have
>> >> been double qouted.
>> >>
>> >> Small example.
>> >>
>> >> 1234, hello John, mega
>> >> 2345, " Hello Andrew, Marie", tall
>> >>
>> >> I have tried to use Bulk insert with a Formatfile in xml and suggested
>> >> by
>> >> Uri Dimant a formatted file.
>> >> But with the bulk insert, the field with the comma breaks into two
>> >> sepperate fields and this generetes
>> >> sometimes an error because the fields are not in sync.
>> >>
>> >> With the formatted file the lines without quotes do not work
>> >> correctly. I
>> >> tried to read the help files on this, but could not find a solution to
>> >> the problem.
>> >>
>> >> In total it's about millions of rows, in hundreds of files, for a
>> >> number
>> >> off tables.
>> >>
>> >> Can anybody give advise how to solve this, or where to look in the
>> >> help
>> >> system?
>> >>
>> >> Thanks for your time and attention,
>> >> Ben Brugman
>> >>
>> >
>>
Subscribe to:
Posts (Atom)