Importing Text File With DTS

Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It´s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can´t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn´t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> It´s a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I can´t define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldn´t be so happy with that)
> thanks in advance.|||I´ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i´ll try to translate it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> > Hello,
> > i'm trying to import a text file using DTS... I check the 'delimited'
> > checkbox to do it because the guy that sent me this file didn't padronized
> > very well the file.
> > It´s a large file, it has about to 70 columns to be imported, and
> > thousands
> > of rows.
> > The problem is that the file has been sent to me like this:
> > column_name1 column_name2 column_name3]
> > data1 data2 data3
> >
> > I can´t define a blankspace using the 'other' checkbox, because some of
> > the
> > columns have more than one blankspace in between.
> > So, can I delimit the columns using some kind of wildcards or do I have to
> > manually padronize all the files?(I wouldn´t be so happy with that)
> > thanks in advance.
>
>sql

Importing Text File With DTS

Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It′s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can′t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn′t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.t
xt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> Its a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I cant define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldnt be so happy with that)
> thanks in advance.|||I′ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i′ll try to translate
it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:

> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*
.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
>
>

Importing tab-delimited file into SQL Server 2005

I use SQL server 2005...
I have a tab delimited file which I want to import into my SQL server database.

My sql server table setup is:

CountryID int (autogenerated, identity specification)
CountryName nvarchar(40)
CountryAbbreviation nvarchar(3)

In my tab delimted file I have two columns:
CountryName and CountryAbbreviation

How can I best solve this?

If you use one version of SQL SERVER 2005 other than Express, you can use data import/Export Wizard to get the job done.

1.Right click on your database, select Task menu, click Import Data, the Wizard will start;

2.Click NEXT, you need choose a Data source. In your case, you choose the Flat File Source from the Data Source drowdown menu, then Browse to your text file. If you text file includes column names, you need check the checkbox infront of "Column names in the first data row"; click next;

3.You will see part of your data file in two clolumns, click Next;

4. Destination data base, use SQL Native Client (Default) as destination data source, pick your database from the Databasr dropdownlist, click next;

5.In this window, you will see both source and destination. You need pick your table from the destination column, click Edit button for mapping, make sure you match your source column with your destination column. (you cannot touch your identity field here) click next;

5. click next again, then click Finish. Data should be imported to your destination table along with their new ids.

I hope I make this process clear enough to follow.

Let me know if you are running into issues.

|||Great! explanation was clear enough :)
It all works now!

Importing strings.

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

Importing strings.

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

---

Hello Uri,
I tried your script, as I understood it.
But I can't get it to work as I would like.
I am working in MicroSoft SQL Server =Management Studio (MSSSMS ?).
Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the last =character in the last column.
- It can not cope with none quoted =strings, I get error from that.
It did cope correctly with a comma in a =string.
Below a detailled result, mainly your =code and example.
The word 'voorbeeld' is just the Dutch =word for 'example'.
Maybe I misunderstood your example, so =I have included the files
as I understood them to =be.
Did you try using READ method in =.NET to get the file.?I do not =understand this question, so I assume the anwser is no.
I copied the example from the message =in Outlook Express.
Am I doing something completely =wrong.
Ben Brugman
The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 =varchar(30),c2 varchar(30), c3 varchar(30))
delete dbo.voorbeeld_tb1
bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" 1 =c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post Material""D","MOVE","Misc Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; c2  =; = c3-- -- --"A &=nbsp; &n=bsp; ISSUE &n=bsp; &nb=sp; Misc Material Issue""B &=nbsp; &n=bsp; MOVE &nb=sp; &nbs=p; Misc Material Move""C &n=bsp; &nb=sp; POST &nb=sp; &nbs=p; Misc Post Material""D &nbs=p;  =; MOVE &nb=sp; &nbs=p; Misc Material Move""A &n=bsp; &nb=sp; ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--
"Uri Dimant" =wrote in message news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...> Ben> =Remove it.> 8.0> 3> 1 SQLCHAR 0 30 "\",\"" 1 c1 =""> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""> 3 SQLCHAR 0 30 "\r\n" 3 =c3 ""> > > Did you try using READ =method in .NET to get the file.?> > "ben brugman" = =wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...> Hello Uri,>> =Thanks for the quick reply,> I'll try to understand and use your method.>> In your example does the fmt_file.fmt =start with the line starting with 4 > or the line starting with =8?> Tthe text file is the file with the 4 lines with the A/B/C/D examples I > assume?> Not all my fields are double quoted, =not all strings are double quoted > (only the one's containing a =comma), is this a problem?> You have three fields, but I see four =definitions, how does this work?>> Sorry to ask so many =questions, but I did try to read up on the XML format > (not succesfully =yet), so switching to another format is no problem, but > I'll like to =understand the 'definitions' so that I do not run into new > problems, which I can not solve by myself.>> Also =next to get the system working (with your example), I would like to > =know how to work in the XML format and still be able to insert double => quoted strings when not all strings are double =quoted.>> Thanks for your time and attention,> Ben Brugman>>> "Uri Dimant" =wrote in message > news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...> Ben> With =bulk insert I am trying to insert data.> Data is comma delimited.> And strings containing comma's are double =quoted.>>>> =I did not create a XML file ,howere this works for just great>>> create table =dbo.tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))>> go>> /*>> "A","ISSUE","Misc Material Issue">> "B","MOVE","Misc Material Move">> ="C","POST","Misc Post Material">> "D","MOVE","Misc Material =Move">> =8.0>> 4>> 1 SQLCHAR 0 0 "\"" 0 first_quote "">> 2 SQLCHAR 0 30 "\",\"" 1 c1 "">> 3 SQLCHAR 0 30 "\",\"" 2 c2 "">> 4 SQLCHAR 0 30 "\r\n" 3 c3 "">> */>> =bulk insert dbo.tb1>> from 'c:\txt_file.txt'>> with (formatfile=3D'c:\fmt_file.fmt')>> go>> select =*>> from dbo.tb>> =go>> drop table dbo.tb>>>>>> "ben brugman" =wrote in message > news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...> =Hello,>> With bulk insert I am trying to insert data.> Data is =comma delimited.> And strings containing comma's are double =quoted.>> See below for the =command and the format.>> I am doing something =wrong because strings with> comma's in them get split over =more fields.>> This should be simple =but haven't found the answer yet.> What should I change =in de command, or in the format file?>> =Thanks in advance,> Ben Brugman>> The command I use from =the 'Query Analyser' in 2005.>> BULK =INSERT IMP FROM 'D:\folder\import.txt'> =WITH (FORMATFILE =3D 'D:\folder\format.xml' =);>> The format file:>> > xmlns=3D"" > xmlns:xsi=3D"">> >>>>=; COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> > => > >>&nbs=p; > >> > >>>>=;>>>>>=>> > >

---

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

---

Ben
You are right. We got double quotes in =the table. However you can use REPLACE function to get rid of them.
I mean .READ method to read XML file in =the VB.NET
"ben brugman" wrote in =message news:uOJQ%23%23gJ=IHA.4196@.TK2MSFTNGP04.phx.gbl...
Hello Uri,

I tried your script, as I understood it.
But I can't get it to work as I would = like.
I am working in MicroSoft SQL Server =Management Studio (MSSSMS ?).

Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the last =character in the last column.
- It can not cope with none quoted =strings, I get error from that.

It did cope correctly with a comma in =a string.
Below a detailled result, mainly your =code and example.
The word 'voorbeeld' is just the =Dutch word for 'example'.

Maybe I misunderstood your example, =so I have included the files
as I understood them to =be.

Did you try using READ method =in .NET to get the file.?I do not =understand this question, so I assume the anwser is no.
I copied the example from the message =in Outlook Express.

Am I doing something completely wrong.

Ben Brugman


The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 =varchar(30),c2 varchar(30), c3 varchar(30))

delete dbo.voorbeeld_tb1

bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" 1 =c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post Material""D","MOVE","Misc =Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; =c2  =; = c3-- -- =--"A &=nbsp; &n=bsp; =ISSUE &n=bsp; &nb=sp; Misc Material =Issue""B &=nbsp; &n=bsp; =MOVE &nb=sp; &nbs=p; Misc Material =Move""C &n=bsp; &nb=sp; =POST &nb=sp; &nbs=p; Misc Post =Material""D &nbs=p;  =; =MOVE &nb=sp; &nbs=p; Misc Material =Move""A &n=bsp; &nb=sp; =ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--


"Uri Dimant" =wrote in message news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...> =Ben> Remove it.> 8.0> 3> 1 SQLCHAR 0 30 "\",\"" 1 =c1 ""> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""> 3 SQLCHAR 0 30 = "\r\n" 3 c3 ""> > > Did you try =using READ method in .NET to get the file.?> > "ben =brugman" =wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...> Hello =Uri,>> Thanks for the quick reply,> I'll try to understand and use your method.>> In your example does the fmt_file.fmt =start with the line starting with 4 > or the line starting with 8?> Tthe text file is the file with the 4 lines with the =A/B/C/D examples I > assume?> Not all my fields are =double quoted, not all strings are double quoted > (only the one's = containing a comma), is this a problem?> You have three =fields, but I see four definitions, how does this work?>> =Sorry to ask so many questions, but I did try to read up on the XML format => (not succesfully yet), so switching to another format is no problem, =but > I'll like to understand the 'definitions' so that I do =not run into new > problems, which I can not solve by myself.>> Also next to get the system working =(with your example), I would like to > know how to work in the XML =format and still be able to insert double > quoted strings when not =all strings are double quoted.>> Thanks for your =time and attention,> Ben Brugman>>> ="Uri Dimant" =wrote in message > news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...> Ben> With bulk insert =I am trying to insert data.> Data is comma delimited.> And strings containing comma's are =double =quoted.>>>> =I did not create a XML file ,howere this works for just great>>> create table =dbo.tb1(c1 varchar(30),c2 varchar(30), c3 =varchar(30))>> go>> /*>> "A","ISSUE","Misc Material Issue">> "B","MOVE","Misc Material Move">> "C","POST","Misc Post Material">> "D","MOVE","Misc Material Move">> 8.0>> 4>> =1 SQLCHAR 0 0 "\"" 0 first_quote "">> 2 =SQLCHAR 0 30 "\",\"" 1 c1 "">> 3 SQLCHAR 0 30 ="\",\"" 2 c2 "">> 4 SQLCHAR 0 30 "\r\n" 3 c3 "">> */>> =bulk insert dbo.tb1>> from 'c:\txt_file.txt'>> with (formatfile=3D'c:\fmt_file.fmt')>> go>> select =*>> from dbo.tb>> =go>> drop table =dbo.tb>>>>>> "ben brugman" =wrote in message > news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...> =Hello,>> With bulk insert I am trying to insert data.> Data =is comma delimited.> And strings containing comma's are =double quoted.>> See below for the =command and the format.>> I am doing =something wrong because strings with> comma's in them get split =over more fields.>> This should be simple =but haven't found the answer yet.> What should I change =in de command, or in the format =file?>> Thanks in advance,> Ben Brugman>> The command I use from =the 'Query Analyser' in 2005.>> BULK =INSERT IMP FROM 'D:\folder\import.txt'> =WITH (FORMATFILE =3D 'D:\folder\format.xml' );>> The format file:>> > xmlns=3D"" > xmlns:xsi=3D"">> =>>>>=; =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> > > > >>&nbs=p; > >> > =>>>>=;>>>>>=>> > >

---

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

---

Hello Uri,
Yes the double quotes can be removed =with a REPLACE, specific if they are the first or last character.
But the main problem is still that not =all strings are quoted and this import loses track when a string is
not quoted. I get error messages. And =sometimes it 'jumbles' up the columns.
Also I tried to to continue with the =xml definition file, no succes there either.
The import files are delivered to me, =but in total there are hundreds of import files. So editing them is not realy an option.
As far as I remember this wasn't a =problem in SQL-server 2000 using DTS, but am not sure of that.
Thanks again for your time and attention.
ben
"Uri Dimant" schreef in =bericht news:%232ikj8pJIHA.=4592@.TK2MSFTNGP02.phx.gbl...
Ben
You are right. We got double quotes =in the table. However you can use REPLACE function to get rid of them.

I mean .READ method to read XML file =in the VB.NET


"ben brugman" wrote in =message news:uOJQ%23%23gJ=IHA.4196@.TK2MSFTNGP04.phx.gbl...
Hello Uri,

I tried your script, as I =understood it.
But I can't get it to work as I =would like.
I am working in MicroSoft SQL =Server Management Studio (MSSSMS ?).

Problems:
- There is a double quote as first =character in the first column.
- There is a double quote as the =last character in the last column.
- It can not cope with none quoted =strings, I get error from that.

It did cope correctly with a comma =in a string.
Below a detailled result, mainly =your code and example.
The word 'voorbeeld' is just the =Dutch word for 'example'.

Maybe I misunderstood your example, =so I have included the files
as I understood them to =be.

Did you try using READ method =in .NET to get the file.?I =do not understand this question, so I assume the anwser is no.
I copied the example from the =message in Outlook Express.

Am I doing something completely wrong.

Ben Brugman


The command I used :
---=--
create table dbo.voorbeeld_tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))

delete =dbo.voorbeeld_tb1

bulk insert =dbo.voorbeeld_tb1
from ='c:\voorbeeld_content.txt'
with (formatfile=3D'c:\voorbeeld_format.fmt')
go
---=--
The used format file c:\voorbeeld_format.fmt
---=--
8.031 SQLCHAR 0 30 "\",\"" =1 c1 ""2 SQLCHAR 0 30 "\",\"" 2 c2 ""3 SQLCHAR 0 30 ="\r\n" 3 c3 ""
---=--
the content file =c:\voorbeeld_content.txt :
---=--"A","ISSUE","Misc Material =Issue""B","MOVE","Misc Material Move""C","POST","Misc Post =Material""D","MOVE","Misc Material Move""A","ISSUE","Misc , extra"
---=--
select * from dbo.voorbeeld_tb1 Delivers
---=--
c1 &n=bsp; &nb=sp; =c2  =; = c3-- -- =--"A &=nbsp; &n=bsp; =ISSUE &n=bsp; &nb=sp; Misc Material =Issue""B &=nbsp; &n=bsp; =MOVE &nb=sp; &nbs=p; Misc Material =Move""C &n=bsp; &nb=sp; =POST &nb=sp; &nbs=p; Misc Post =Material""D &nbs=p;  =; =MOVE &nb=sp; &nbs=p; Misc Material =Move""A &n=bsp; &nb=sp; =ISSUE &n=bsp; &nb=sp; Misc , extra"
(5 row(s) affected)
---=--


"Uri Dimant" =wrote in message news:uHujJudJIHA.1184@.TK2MSFTNGP04.phx.gbl...> Ben> Remove it.> 8.0> 3> 1 SQLCHAR 0 30 "\",\"" 1 =c1 ""> 2 SQLCHAR 0 30 "\",\"" 2 c2 ""> 3 SQLCHAR 0 =30 "\r\n" 3 c3 ""> > > Did you =try using READ method in .NET to get the file.?> > "ben =brugman" =wrote in message > news:eU6e6cdJIHA.3848@.TK2MSFTNGP05.phx.gbl...> Hello Uri,>> Thanks =for the quick reply,> I'll try to understand and use your method.>> In your example does the =fmt_file.fmt start with the line starting with 4 > or the line starting with = 8?> Tthe text file is the file with the 4 lines with the =A/B/C/D examples I > assume?> Not all my fields are =double quoted, not all strings are double quoted > (only the =one's containing a comma), is this a problem?> You have three =fields, but I see four definitions, how does this =work?>> Sorry to ask so many questions, but I did try to read up on the XML =format > (not succesfully yet), so switching to another format =is no problem, but > I'll like to understand the 'definitions' =so that I do not run into new > problems, which I can not solve =by myself.>> Also next to get the system working =(with your example), I would like to > know how to work in the =XML format and still be able to insert double > quoted =strings when not all strings are double quoted.>> Thanks =for your time and attention,> Ben Brugman>>> "Uri Dimant" = =wrote in message > news:uOk10OdJIHA.3848@.TK2MSFTNGP05.phx.gbl...> Ben> With bulk =insert I am trying to insert data.> Data is comma delimited.> And strings containing comma's are =double =quoted.>>>> =I did not create a XML file ,howere this works for just great>>> create table dbo.tb1(c1 varchar(30),c2 varchar(30), c3 varchar(30))>> go>> =/*>> "A","ISSUE","Misc Material Issue">> "B","MOVE","Misc Material Move">> "C","POST","Misc Post Material">> "D","MOVE","Misc Material Move">> 8.0>> =4>> 1 SQLCHAR 0 0 "\"" 0 first_quote "">> 2 =SQLCHAR 0 30 "\",\"" 1 c1 "">> 3 SQLCHAR 0 30 ="\",\"" 2 c2 "">> 4 SQLCHAR 0 30 "\r\n" 3 c3 "">> =*/>> bulk insert dbo.tb1>> from 'c:\txt_file.txt'>> with (formatfile=3D'c:\fmt_file.fmt')>> go>> select =*>> from dbo.tb>> go>> drop table =dbo.tb>>>>>> "ben brugman" =wrote in message > news:e08l0FdJIHA.1620@.TK2MSFTNGP03.phx.gbl...> Hello,>> With bulk insert I am =trying to insert data.> Data is comma delimited.> And strings containing comma's are =double quoted.>> See below for the =command and the format.>> I am doing =something wrong because strings with> comma's in them get =split over more fields.>> This =should be simple but haven't found the answer yet.> What =should I change in de command, or in the format file?>> Thanks in advance,> Ben Brugman>> The command I use =from the 'Query Analyser' in 2005.>> =BULK INSERT IMP FROM ='D:\folder\import.txt'> WITH (FORMATFILE =3D 'D:\folder\format.xml' );>> The format file:>> > xmlns=3D"" > xmlns:xsi=3D"">> =>>>>=; =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>> = =COLLATION=3D"SQL_Latin1_General_CP1_CI_AS"/>>&g=t;>> > > > >>&nbs=p; > >> > =>>>>=;>>>>>=>> > >

---

--=_NextPart_000_005A_01C826BC.39421B80--

Importing strings.

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

Importing from xls that has all data in first column

I have a spreadsheet that has all of the data in the first column. The data is delimited by asterisks and there are no column headings. My goal is to import the data into a SQL table. I get the error message "External table is not in the expected format" when I try to select the name of the Excel sheet in the Excel Source Editor. I tried reading using a flat file connection instead of Excel connection, but that didn't work.

In Excel, I can use the Text to Columns feature to convert the data to columns in the spreadsheet. But I have to go into the file manually to do that. Is there a way in SSIS to do the same thing automatically? Or is there a way to save the XLS as a TXT file? Then, I could just use Flat File Connection to read the delimited data.

Thanks.

cpat

cpat wrote:

I have a spreadsheet that has all of the data in the first column. The data is delimited by asterisks and there are no column headings. My goal is to import the data into a SQL table. I get the error message "External table is not in the expected format" when I try to select the name of the Excel sheet in the Excel Source Editor. I tried reading using a flat file connection instead of Excel connection, but that didn't work.

In Excel, I can use the Text to Columns feature to convert the data to columns in the spreadsheet. But I have to go into the file manually to do that. Is there a way in SSIS to do the same thing automatically? Or is there a way to save the XLS as a TXT file? Then, I could just use Flat File Connection to read the delimited data.

Thanks.

cpat

You should read the data in as a single column and then use the Derived Column component to split that column up into all the required columns.

-Jamie

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

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

Importing Data??

I have a process that calls a proc that BCP's a delimited file into a table. Well the SOX police say a header and footer must be added to the file. Needless to say this screws my BCP process.

Does anyone know how to strip a header and footer record from a text file using transact sql or have any other suggestions to strip the records?Well, -F will allow you to bypass the headers... If you can magically devine the number of rows, then -L will allow you to bypass the footers.

If that doesn't suffice, you can always use brute force! Either specify a format file and a batch size of 1 row (really ugly performance), or BCP into a staging table, then only copy the rows of interest from the staging table to the production table.

The other option that will get the job done, but might infuriate the SOX-meisters would be to use the "Flintstone" method... Write one utility to apply the requisite header and footer, and a separate utility to remove the header and footer before using BCP to import the data!

-PatP|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...

bcp the whole damn thing in to a single column table varchar(8000)

Sounds like a mainframe file, so do you have record identifiers?

Like 'H', 'D', and 'T'?|||Yeah, well...I'm sure SOX is gonna ask you to interogate thos headers and trailers as well...Oh! You're no fun!
bcp the whole damn thing in to a single column table varchar(8000)Nothing quite like brute force! 'Tain't pretty, but it does get the job done.

-PatP|||What's funny is they don't care if the header and footer are interogated. I'm lobbying to remove the footer since I can eliminate the header with -F.

My record/row delimiter is {CR}{LF}. I've decided my worse case senario is to add an extra column to the file and BCP the file into a staging table that populates the target table with all records where the new column is null. You know, populate the new column with the header and footer data and leave them blank for all other records. Still too much damn work for such a small problem.|||Let me ask you...what other tool could you possibly need in your toolbox, when GOD made this wonderful thing called a sledge hammer?

Peter,

The Header and trailer thing has always been a pain...

Add an IDENTITY column to get the last and first row...

(OK Pat, release the hounds...)

Do your intergoation and save the stats...

The use a simple bcp, with your first row = 2 and your last row = COUNT(*)

Then perform the audits...

I also like to do an INSERT with parsing to the final destination...but I like what I sketched out above better...

OK, now the discussion about how the data may not get loaded to the table in the same manner that it's in the file...

Take it away Pat...

(did I piss him off too?)|||Nah, it takes LOTS more than that to irrigate me!

I'm actually fine with that idea... The staging table was actually in my first posting, and an identity column makes processing easy. The only thing that might make it a booger to process would be variable length columns with delimiters, although SQL Server can handle that too (at some performance penalty relative to the way that BCP would handle it).

I'm for whatever works, and the less effort needed to get there, the better I like it! I've only got time for so much schtuff, and I really don't want to do any more than I have to!

-PatP

Importing Data through DTS

Hi all,
I have a problem when I import a text delimited data into SQL Server. This happens only with the date. My date format is dd/mm/yyyy in both the text file and regional settings in Windows. I have created a DTS package and schedule the job to run daily. The
imported data in SQL Server show mm/dd/yyyy.
The funny part is when I execute the package directly from DTS, the data were imported without the problem. But when I execute the job in SQL Server Agent, the problem arises. Also this happens only early of the month, from 1st - 12th.
I would appreciate it if anyone can help me in resolving this problem.
ps : My data type for the date column is datetime.
when you execute the job in SQL Server Agent, it runs with the settings on the server.
when you execute the package directly from DTS, it runs with your local settings.
It suggests that the regional settings in Windows on the server are mm/dd/yyyy - which explains why it works until the 12th.
|||Hi Rubes,
I have checked on the server's regional settings, the date format is
'dd/MM/yyyy'.
However, the problem still occurs.
Uskaka
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Adding to my query, I have done the data transformation test and the result shows in format 'dd/MM/yyyy' but when inserted, the format goes 'MM/dd/yyyy'.