Hello,
I use the following statement to insert a string of 35 characters into a
table (I am planning to figure out a suitable .FMT file for parsing this int
o
the right column definitions, but that is after I figure out this current
problem described below)
BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
(FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
MyTestTable is currently defined as
CREATE TABLE MyTestTable (Col1 CHAR(35))
and some sample test data from MyTextFile.txt is as follows
ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
(length of the data is always 36 bytes and the length of the filename is
always 14 bytes)
(As you can tell, the DDL and data is what I am using for this test)
This BULK INSERT works fine, but what I need to do is to get the name of the
file (MyTextFile.txt in this case) appended to the end of the column.
So, the table definition would change to
CREATE TABLE MyTestTable (Col1 CHAR(49))
I am clueless about how to get the name of the file (which will vary at
runtime) into each row of the MyTestTable that gets affected by this BULK
INSERT.
(I will be inserting multiple files - different file names - to the same
table one after the other and would like to have the filename stored in a
separate column or appended to the column - either way. When I build the .FM
T
file, I will split this into the appropriate columns and will change the
table definition.
Any suggestions would be appreciated. Please let me know if any further
details are needed.
Thanks!you could probably write a TSQL block to read filename before inserting the
data with bulk insert. and then insert data, and update data with appending
the file name to recently inserted records.
hth,
avnrao
http://avnrao.blogspot.com
"Bob" wrote:
> Hello,
> I use the following statement to insert a string of 35 characters into
a
> table (I am planning to figure out a suitable .FMT file for parsing this i
nto
> the right column definitions, but that is after I figure out this current
> problem described below)
> BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
> (FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
> MyTestTable is currently defined as
> CREATE TABLE MyTestTable (Col1 CHAR(35))
> and some sample test data from MyTextFile.txt is as follows
> ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
> 123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
> (length of the data is always 36 bytes and the length of the filename is
> always 14 bytes)
> (As you can tell, the DDL and data is what I am using for this test)
> This BULK INSERT works fine, but what I need to do is to get the name of t
he
> file (MyTextFile.txt in this case) appended to the end of the column.
> So, the table definition would change to
> CREATE TABLE MyTestTable (Col1 CHAR(49))
> I am clueless about how to get the name of the file (which will vary at
> runtime) into each row of the MyTestTable that gets affected by this BULK
> INSERT.
> (I will be inserting multiple files - different file names - to the same
> table one after the other and would like to have the filename stored in a
> separate column or appended to the column - either way. When I build the .
FMT
> file, I will split this into the appropriate columns and will change the
> table definition.
> Any suggestions would be appreciated. Please let me know if any further
> details are needed.
> Thanks!
>|||Bob,
I'm currently doing a migration which has between 40 and 50 text files as
the datasource from a mainframe. Their names can change so I use a batch
file to handle this. eg in my batch file,
Call dir with simple header, full filename options to list the files
required into :\temp\filelist.txt
Your file list should be a single column with the complete filepath and
filename
Upload the list to the server using bcp
Note: bcp into a view which has only one column, the filename
If you need more details post back. Basicallly, even if it's a bit
old-fashioned, DOS already has the commands for working with files. There's
always DTS but I'm not a fan ... ; )
Damien
"Bob" wrote:
> Hello,
> I use the following statement to insert a string of 35 characters into
a
> table (I am planning to figure out a suitable .FMT file for parsing this i
nto
> the right column definitions, but that is after I figure out this current
> problem described below)
> BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
> (FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
> MyTestTable is currently defined as
> CREATE TABLE MyTestTable (Col1 CHAR(35))
> and some sample test data from MyTextFile.txt is as follows
> ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
> 123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
> (length of the data is always 36 bytes and the length of the filename is
> always 14 bytes)
> (As you can tell, the DDL and data is what I am using for this test)
> This BULK INSERT works fine, but what I need to do is to get the name of t
he
> file (MyTextFile.txt in this case) appended to the end of the column.
> So, the table definition would change to
> CREATE TABLE MyTestTable (Col1 CHAR(49))
> I am clueless about how to get the name of the file (which will vary at
> runtime) into each row of the MyTestTable that gets affected by this BULK
> INSERT.
> (I will be inserting multiple files - different file names - to the same
> table one after the other and would like to have the filename stored in a
> separate column or appended to the column - either way. When I build the .
FMT
> file, I will split this into the appropriate columns and will change the
> table definition.
> Any suggestions would be appreciated. Please let me know if any further
> details are needed.
> Thanks!
>|||avnrao, thank you for your quick response. I had considered this option too,
but the problem is that this load process is supposed to run once a month an
d
with all the 100 files combined, I will have totally about 600 million rows
to process - I guess I was hoping that BULK INSERT would allow us to process
the filename along with it - Any other means of achieving this (instead of
Bulk Insert) would be welcome too.
Thanks!
"avnrao" wrote:
> you could probably write a TSQL block to read filename before inserting th
e
> data with bulk insert. and then insert data, and update data with appendin
g
> the file name to recently inserted records.
> hth,
> avnrao
> http://avnrao.blogspot.com
> "Bob" wrote:
>|||Damien,
I'm ok with using DOS commands too - anything that can get this done
(without too much of a performance hit). :)
I do have the list of filenames available (and I can get it into a SQL table
too), but the problem is how to update the filenames onto the MyTestTable...
I guess I didn't quite understand the suggestion - bcp into the view (and
this view should be for the MyTestTable table?) - but how would I be able to
associate the multiple records from the file with the filename that I am
BCPing?
Sorry if I misunderstood you... but could you clarify a little on this?
Thanks again!
"Damien" wrote:
> Bob,
> I'm currently doing a migration which has between 40 and 50 text files as
> the datasource from a mainframe. Their names can change so I use a batch
> file to handle this. eg in my batch file,
> Call dir with simple header, full filename options to list the files
> required into :\temp\filelist.txt
> Your file list should be a single column with the complete filepath and
> filename
> Upload the list to the server using bcp
> Note: bcp into a view which has only one column, the filename
> If you need more details post back. Basicallly, even if it's a bit
> old-fashioned, DOS already has the commands for working with files. There
's
> always DTS but I'm not a fan ... ; )
>
> Damien
>
>
> "Bob" wrote:
>|||Ah,
well I cheated a little bit here. I used SQL to write the batch file for
me, and I use osql to fire off an ALTER TABLE to set the default for the
column.
So, from Query Analyser, write a query which selects your records, but
create a bcp string. This script will create a meaninful looking batch file
but obviously you can't bcp into temp tables:
DROP TABLE #import_files
CREATE TABLE #import_files ( file_id INT UNIQUE IDENTITY NOT NULL, file_name
VARCHAR(30) NOT NULL )
DROP TABLE #raw_data
CREATE TABLE #raw_data ( record_id INT UNIQUE IDENTITY NOT NULL, file_id INT
NOT NULL, record CHAR(36) )
ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT -1 FOR
file_id
GO
SET NOCOUNT ON
INSERT INTO #import_files ( file_name ) VALUES ( 'test1.txt' )
INSERT INTO #import_files ( file_name ) VALUES ( 'test2.txt' )
SET NOCOUNT OFF
GO
DROP TABLE #batch_file
CREATE TABLE #batch_file ( file_id INT, sort_id INT, command VARCHAR( 500 )
)
GO
--
SET NOCOUNT ON
-- Section header
INSERT INTO #batch_file
SELECT file_id, 10, 'REM bcp file ' + CAST( file_id AS VARCHAR ) + ' - ' +
file_name
FROM #import_files
-- Drop the default
INSERT INTO #batch_file
SELECT file_id, 20, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppassword
-q"ALTER TABLE #raw_data DROP CONSTRAINT def_raw_data__file_id'
FROM #import_files
-- Set the default
INSERT INTO #batch_file
SELECT file_id, 30, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppassword
-q"ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT ' +
CAST( file_id AS CHAR ) + ' FOR file_id"'
FROM #import_files
-- bcp the file
INSERT INTO #batch_file
SELECT file_id, 40, 'bcp -iyou get the idea.txt ; )'
FROM #import_files
-- Make a gap
INSERT INTO #batch_file
SELECT file_id, 50, ''
FROM #import_files
SELECT command
FROM #batch_file
ORDER BY file_id, sort_id
SET NOCOUNT OFF
Now, save the results as a batch file, remove the dashes from the top and
away you go. I actually use a similar structure in the migration, only it's
a bit more complex, plus it's wrapped in a stored procedure and paramterized
so it's nice and flexible.
If it seems like a lot of hard work, then perhaps this isn't the solution
for you, but it's worked for me!
Let me know how you get on.
Damien|||Bob,
If you are moving to SQL Server 2005, you might consider using
the BULK rowset provider. Existing (in SQL Server 2000) text
providers might also work, but may not be as fast.
DECLARE @.f nvarchar(200)
SET @.f = 'c:\test\values.txt'
INSERT INTO MyTestTable
SELECT colFromTextFile + @.f
SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
FORMATFILE = 'c:\test\values.fmt') AS a;
Steve Kass
Drew University
Bob wrote:
>Hello,
> I use the following statement to insert a string of 35 characters into
a
>table (I am planning to figure out a suitable .FMT file for parsing this in
to
>the right column definitions, but that is after I figure out this current
>problem described below)
>BULK INSERT MyTestTable FROM '<UNCname-FileLocation>\MyTextFile.txt' WITH
>(FIELDTERMINATOR = '\0',ROWTERMINATOR = '\n')
>MyTestTable is currently defined as
>CREATE TABLE MyTestTable (Col1 CHAR(35))
>and some sample test data from MyTextFile.txt is as follows
>ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789
>123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
>(length of the data is always 36 bytes and the length of the filename is
>always 14 bytes)
>(As you can tell, the DDL and data is what I am using for this test)
>This BULK INSERT works fine, but what I need to do is to get the name of th
e
>file (MyTextFile.txt in this case) appended to the end of the column.
>So, the table definition would change to
>CREATE TABLE MyTestTable (Col1 CHAR(49))
>I am clueless about how to get the name of the file (which will vary at
>runtime) into each row of the MyTestTable that gets affected by this BULK
>INSERT.
>(I will be inserting multiple files - different file names - to the same
>table one after the other and would like to have the filename stored in a
>separate column or appended to the column - either way. When I build the .F
MT
>file, I will split this into the appropriate columns and will change the
>table definition.
>Any suggestions would be appreciated. Please let me know if any further
>details are needed.
>Thanks!
>
>|||Thanks Damien - yes, this would work (now, I just have to get the DBA to
approve of building and dropping the constraint - hopefully, he will be ok
with it).
Thanks again.
"Damien" wrote:
> Ah,
> well I cheated a little bit here. I used SQL to write the batch file for
> me, and I use osql to fire off an ALTER TABLE to set the default for the
> column.
> So, from Query Analyser, write a query which selects your records, but
> create a bcp string. This script will create a meaninful looking batch fi
le
> but obviously you can't bcp into temp tables:
> DROP TABLE #import_files
> CREATE TABLE #import_files ( file_id INT UNIQUE IDENTITY NOT NULL, file_na
me
> VARCHAR(30) NOT NULL )
> DROP TABLE #raw_data
> CREATE TABLE #raw_data ( record_id INT UNIQUE IDENTITY NOT NULL, file_id I
NT
> NOT NULL, record CHAR(36) )
> ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT -1 FOR
> file_id
> GO
>
> SET NOCOUNT ON
> INSERT INTO #import_files ( file_name ) VALUES ( 'test1.txt' )
> INSERT INTO #import_files ( file_name ) VALUES ( 'test2.txt' )
> SET NOCOUNT OFF
> GO
> DROP TABLE #batch_file
> CREATE TABLE #batch_file ( file_id INT, sort_id INT, command VARCHAR( 500
) )
> GO
> --
> SET NOCOUNT ON
> -- Section header
> INSERT INTO #batch_file
> SELECT file_id, 10, 'REM bcp file ' + CAST( file_id AS VARCHAR ) + ' - ' +
> file_name
> FROM #import_files
>
> -- Drop the default
> INSERT INTO #batch_file
> SELECT file_id, 20, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppasswor
d
> -q"ALTER TABLE #raw_data DROP CONSTRAINT def_raw_data__file_id'
> FROM #import_files
> -- Set the default
> INSERT INTO #batch_file
> SELECT file_id, 30, 'osql -Syourserver -dyourdatabase -Ulogin_id -Ppasswor
d
> -q"ALTER TABLE #raw_data ADD CONSTRAINT def_raw_data__file_id DEFAULT ' +
> CAST( file_id AS CHAR ) + ' FOR file_id"'
> FROM #import_files
> -- bcp the file
> INSERT INTO #batch_file
> SELECT file_id, 40, 'bcp -iyou get the idea.txt ; )'
> FROM #import_files
> -- Make a gap
> INSERT INTO #batch_file
> SELECT file_id, 50, ''
> FROM #import_files
>
> SELECT command
> FROM #batch_file
> ORDER BY file_id, sort_id
> SET NOCOUNT OFF
> Now, save the results as a batch file, remove the dashes from the top and
> away you go. I actually use a similar structure in the migration, only it
's
> a bit more complex, plus it's wrapped in a stored procedure and paramteriz
ed
> so it's nice and flexible.
> If it seems like a lot of hard work, then perhaps this isn't the solution
> for you, but it's worked for me!
> Let me know how you get on.
> Damien
>
>|||Steve,
Thanks for the update. At this time, the Co is not planning to move to
SQL Server 2005 (this project is expected to go live within a month), so I
guess I am stuck with 2000.
I haven't used text providers yet, so I don't fully understand the code. I
will go thru' BOL and assuming the performance drop isn't too much, I will
try to use this. Currently, I am able to push about 400 million rows into th
e
table (without the filename of course) in about an hour
Thanks again!
"Steve Kass" wrote:
> Bob,
> If you are moving to SQL Server 2005, you might consider using
> the BULK rowset provider. Existing (in SQL Server 2000) text
> providers might also work, but may not be as fast.
> DECLARE @.f nvarchar(200)
> SET @.f = 'c:\test\values.txt'
> INSERT INTO MyTestTable
> SELECT colFromTextFile + @.f
> SELECT a.* FROM OPENROWSET( BULK 'c:\test\values.txt',
> FORMATFILE = 'c:\test\values.fmt') AS a;
> Steve Kass
> Drew University
> Bob wrote:
>
>
Showing posts with label fmt. Show all posts
Showing posts with label fmt. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
BULK INSERT old BCP format files in SQL 2005
I am not able to BULK INSERT data files in SQL 2005 because it is
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//MishaYou can specify the format file with BULK INSERT statement.
Format description in BOL:
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name |
view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW =first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]
Q
"Misha" wrote:
> I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it says
> something about invalid xml character ...) I am aware of the new XML format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>
>
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//MishaYou can specify the format file with BULK INSERT statement.
Format description in BOL:
BULK INSERT
[ database_name . [ schema_name ] . | schema_name . ] [ table_name |
view_name ]
FROM 'data_file'
[ WITH
(
[ [ , ] BATCHSIZE = batch_size ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
[ [ , ] DATAFILETYPE = { 'char' | 'native'| 'widechar' | 'widenative' } ]
[ [ , ] FIELDTERMINATOR = 'field_terminator' ]
[ [ , ] FIRSTROW =first_row ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
[ [ , ] LASTROW = last_row ]
[ [ , ] MAXERRORS = max_errors ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH = rows_per_batch ]
[ [ , ] ROWTERMINATOR = 'row_terminator' ]
[ [ , ] TABLOCK ]
[ [ , ] ERRORFILE = 'file_name' ]
)]
Q
"Misha" wrote:
> I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it says
> something about invalid xml character ...) I am aware of the new XML format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>
>
Bulk Insert of comma seperated textfile with text qualifiers
I want to insert a text file with comma seperated fields. De text-field have
text qualifiers:
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text quali
fiers. In the example screen the data looked perfect. After the insert howev
er the text qualifiers where imported into the colimns as well. (The first c
olumn contained "field1" in
stead of field1)
Does anybody have suggestions on how to solve this?Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> have text qualifiers:
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
>|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to be
tter explain what I want, maybe you can than explain how your reply fits int
o that.
My goal is an import from a textfile. This textfile is comma-delimited with
text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within
this Task it is possible to define a format file (fmt) in which the wizard
writes information about the import-file. Following the wizard it will ask y
ou if the file is fixed len
gth or delimited. I choose delimited, choose the text qualifier and hit the
Next-key. Than I choose Comma as field seperator. In the example list the fi
le was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In
the destination column however you could see that the text qualifiers were
ignored. The double-quotes were in the fields and every comma in the file wa
s used as a field seperator|||Under [Transform Data Task Properties] -> [Options] tab specify [
;Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.[vbcol=seagreen]
> "Uri Dimant" wrote:
>
text-field[vbcol=seagreen]
text[vbcol=seagreen]
(The[vbcol=seagreen]|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Inser
t Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Ins
ert Task.
"Ramon" wrote:
> Under [Transform Data Task Properties] -> [Options] tab specify &#
91;Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ó???Yéì/ó???
éìá ×
> ??×?ó??è óì???àY??:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> with text qualifiers for the char-fields.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as fie
ld
> seperator. In the example list the file was correctly shown.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> text-field
> text
> (The
>
>|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.[vbcol=seagreen]
> "Ramon" wrote:
>
to[vbcol=seagreen]
fits[vbcol=seagreen]
the[vbcol=seagreen]
delimited,[vbcol=seagreen]
field[vbcol=seagreen]
errors.[vbcol=seagreen]
the[vbcol=seagreen]
In[vbcol=seagreen]
SQL-Server.[vbcol=seagreen]
with[vbcol=seagreen]
the[vbcol=seagreen]
well.[vbcol=seagreen]|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> / :
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> /
> :
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Insert Task is supposed to be much faster in loading the data.
> Insert Task.
try[vbcol=seagreen]
> to
> fits
comma-delimited[vbcol=seagreen]
DTS.[vbcol=seagreen]
> the
it[vbcol=seagreen]
> delimited,
> field
> errors.
qualifiers[vbcol=seagreen]
> the
Excel.[vbcol=seagreen]
> In
> SQL-Server.
> with
> the
> well.
>
text qualifiers:
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text quali
fiers. In the example screen the data looked perfect. After the insert howev
er the text qualifiers where imported into the colimns as well. (The first c
olumn contained "field1" in
stead of field1)
Does anybody have suggestions on how to solve this?Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> have text qualifiers:
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
>|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to be
tter explain what I want, maybe you can than explain how your reply fits int
o that.
My goal is an import from a textfile. This textfile is comma-delimited with
text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within
this Task it is possible to define a format file (fmt) in which the wizard
writes information about the import-file. Following the wizard it will ask y
ou if the file is fixed len
gth or delimited. I choose delimited, choose the text qualifier and hit the
Next-key. Than I choose Comma as field seperator. In the example list the fi
le was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In
the destination column however you could see that the text qualifiers were
ignored. The double-quotes were in the fields and every comma in the file wa
s used as a field seperator|||Under [Transform Data Task Properties] -> [Options] tab specify [
;Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.[vbcol=seagreen]
> "Uri Dimant" wrote:
>
text-field[vbcol=seagreen]
text[vbcol=seagreen]
(The[vbcol=seagreen]|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Inser
t Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Ins
ert Task.
"Ramon" wrote:
> Under [Transform Data Task Properties] -> [Options] tab specify &#
91;Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ó???Yéì/ó???
éìá ×
> ??×?ó??è óì???àY??:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> with text qualifiers for the char-fields.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as fie
ld
> seperator. In the example list the file was correctly shown.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> text-field
> text
> (The
>
>|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.[vbcol=seagreen]
> "Ramon" wrote:
>
to[vbcol=seagreen]
fits[vbcol=seagreen]
the[vbcol=seagreen]
delimited,[vbcol=seagreen]
field[vbcol=seagreen]
errors.[vbcol=seagreen]
the[vbcol=seagreen]
In[vbcol=seagreen]
SQL-Server.[vbcol=seagreen]
with[vbcol=seagreen]
the[vbcol=seagreen]
well.[vbcol=seagreen]|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> / :
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> /
> :
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Insert Task is supposed to be much faster in loading the data.
> Insert Task.
try[vbcol=seagreen]
> to
> fits
comma-delimited[vbcol=seagreen]
DTS.[vbcol=seagreen]
> the
it[vbcol=seagreen]
> delimited,
> field
> errors.
qualifiers[vbcol=seagreen]
> the
Excel.[vbcol=seagreen]
> In
> SQL-Server.
> with
> the
> well.
>
Bulk Insert of comma seperated textfile with text qualifiers
I want to insert a text file with comma seperated fields. De text-field have text qualifiers:
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text qualifiers. In the example screen the data looked perfect. After the insert however the text qualifiers where imported into the colimns as well. (The first column contained "field1" in
stead of field1)
Does anybody have suggestions on how to solve this?
Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?
|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> have text qualifiers:
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
>
|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to better explain what I want, maybe you can than explain how your reply fits into that.
My goal is an import from a textfile. This textfile is comma-delimited with text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within this Task it is possible to define a format file (fmt) in which the wizard writes information about the import-file. Following the wizard it will ask you if the file is fixed len
gth or delimited. I choose delimited, choose the text qualifier and hit the Next-key. Than I choose Comma as field seperator. In the example list the file was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In the destination column however you could see that the text qualifiers were ignored. The double-quotes were in the fields and every comma in the file was used as a field seperator
|||Under [Transform Data Task Properties] -> [Options] tab specify [Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.[vbcol=seagreen]
> "Uri Dimant" wrote:
text-field[vbcol=seagreen]
text[vbcol=seagreen]
(The[vbcol=seagreen]
|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Insert Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Insert Task.
"Ramon" wrote:
> Under [Transform Data Task Properties] -> [Options] tab specify [Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ó???Yéì/ó???Yéìá ×
> ??×?ó??è óì???àY??:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> with text qualifiers for the char-fields.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as field
> seperator. In the example list the file was correctly shown.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> text-field
> text
> (The
>
>
|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.[vbcol=seagreen]
> "Ramon" wrote:
to[vbcol=seagreen]
fits[vbcol=seagreen]
the[vbcol=seagreen]
delimited,[vbcol=seagreen]
field[vbcol=seagreen]
errors.[vbcol=seagreen]
the[vbcol=seagreen]
In[vbcol=seagreen]
SQL-Server.[vbcol=seagreen]
with[vbcol=seagreen]
the[vbcol=seagreen]
well.[vbcol=seagreen]
|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> / :
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).[vbcol=seagreen]
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> /
> :
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Insert Task is supposed to be much faster in loading the data.
> Insert Task.
try[vbcol=seagreen]
> to
> fits
comma-delimited[vbcol=seagreen]
DTS.[vbcol=seagreen]
> the
it[vbcol=seagreen]
> delimited,
> field
> errors.
qualifiers[vbcol=seagreen]
> the
Excel.
> In
> SQL-Server.
> with
> the
> well.
>
"field1","field2","field3"
I made a Bulk Insert with FMT file. I choose comma seperated with text qualifiers. In the example screen the data looked perfect. After the insert however the text qualifiers where imported into the colimns as well. (The first column contained "field1" in
stead of field1)
Does anybody have suggestions on how to solve this?
Frank
Try
create table ww
(
col1 int,
col2 varchar(50),
col3 varchar (50)
)
insert into ww values (47,'ReadyShip','(503)888-999')
insert into ww values (48,'MyShipper','(503)1212-454')
insert into ww values (49,'ReadyShip','(45)888-999')
insert into ww values (50,'MyShipper','(545)1212-454')
exec master..xp_cmdshell 'BCP northwind..ww IN
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> I want to insert a text file with comma seperated fields. De text-field
have text qualifiers:
> "field1","field2","field3"
> I made a Bulk Insert with FMT file. I choose comma seperated with text
qualifiers. In the example screen the data looked perfect. After the insert
however the text qualifiers where imported into the colimns as well. (The
first column contained "field1" instead of field1)
> Does anybody have suggestions on how to solve this?
|||Sorry
It should be
exec master..xp_cmdshell 'BCP northwind..ww OUT
d:\test1.txt -c -C850 -SServer -Usa -Pp'
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7owpFNZEHA.2516@.TK2MSFTNGP10.phx.gbl...
> Frank
> Try
> create table ww
> (
> col1 int,
> col2 varchar(50),
> col3 varchar (50)
> )
> insert into ww values (47,'ReadyShip','(503)888-999')
> insert into ww values (48,'MyShipper','(503)1212-454')
> insert into ww values (49,'ReadyShip','(45)888-999')
> insert into ww values (50,'MyShipper','(545)1212-454')
> exec master..xp_cmdshell 'BCP northwind..ww IN
> d:\test1.txt -c -C850 -SServer -Usa -Pp'
>
> "FrankSR" <FrankSR@.discussions.microsoft.com> wrote in message
> news:997A4290-7245-4F6B-84A6-74F10B2DF85C@.microsoft.com...
> have text qualifiers:
> qualifiers. In the example screen the data looked perfect. After the
insert
> however the text qualifiers where imported into the colimns as well. (The
> first column contained "field1" instead of field1)
>
|||Hi Uri,
I don't think I understand your reply to my question. I will firts try to better explain what I want, maybe you can than explain how your reply fits into that.
My goal is an import from a textfile. This textfile is comma-delimited with text qualifiers for the char-fields.
I tried to do this Import sing the Bulk Insert Task, privided by DTS. Within this Task it is possible to define a format file (fmt) in which the wizard writes information about the import-file. Following the wizard it will ask you if the file is fixed len
gth or delimited. I choose delimited, choose the text qualifier and hit the Next-key. Than I choose Comma as field seperator. In the example list the file was correctly shown.
When I executed the Bulk Insert Task it imported the file without errors. In the destination column however you could see that the text qualifiers were ignored. The double-quotes were in the fields and every comma in the file was used as a field seperator
|||Under [Transform Data Task Properties] -> [Options] tab specify [Text
qualifier] as [Double Quote {"}].
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> Hi Uri,
> I don't think I understand your reply to my question. I will firts try to
better explain what I want, maybe you can than explain how your reply fits
into that.
> My goal is an import from a textfile. This textfile is comma-delimited
with text qualifiers for the char-fields.
> I tried to do this Import sing the Bulk Insert Task, privided by DTS.
Within this Task it is possible to define a format file (fmt) in which the
wizard writes information about the import-file. Following the wizard it
will ask you if the file is fixed length or delimited. I choose delimited,
choose the text qualifier and hit the Next-key. Than I choose Comma as field
seperator. In the example list the file was correctly shown.
> When I executed the Bulk Insert Task it imported the file without errors.
In the destination column however you could see that the text qualifiers
were ignored. The double-quotes were in the fields and every comma in the
file was used as a field seperator.
> Obviously the above is not what I desired. I tested the same in Excel. In
Excel the Import went very well. To me it looks like a bug in SQL-Server.[vbcol=seagreen]
> "Uri Dimant" wrote:
text-field[vbcol=seagreen]
text[vbcol=seagreen]
(The[vbcol=seagreen]
|||Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk Insert Task is supposed to be much faster in loading the data.
As mentioned in my posts I did select the Text qualifier within the Bulk Insert Task.
"Ramon" wrote:
> Under [Transform Data Task Properties] -> [Options] tab specify [Text
> qualifier] as [Double Quote {"}].
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> ó???Yéì/ó???Yéìá ×
> ??×?ó??è óì???àY??:
> news:CF457249-CDFF-4223-A68A-11640CB759CF@.microsoft.com...
> better explain what I want, maybe you can than explain how your reply fits
> into that.
> with text qualifiers for the char-fields.
> Within this Task it is possible to define a format file (fmt) in which the
> wizard writes information about the import-file. Following the wizard it
> will ask you if the file is fixed length or delimited. I choose delimited,
> choose the text qualifier and hit the Next-key. Than I choose Comma as field
> seperator. In the example list the file was correctly shown.
> In the destination column however you could see that the text qualifiers
> were ignored. The double-quotes were in the fields and every comma in the
> file was used as a field seperator.
> Excel the Import went very well. To me it looks like a bug in SQL-Server.
> text-field
> text
> (The
>
>
|||Bulk Insert does not support behavior you required (but this is not a bug).
You could use more sophisticated Transform Data for this case.
Personally I load data by BULK INSERT command into the intermediate table
and then load formatted data into the target table.
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"freterink" <freterink@.discussions.microsoft.com> /
:
news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Ramon I'm using Bulk Insert task not the Data Transform Task. The Bulk
Insert Task is supposed to be much faster in loading the data.
> As mentioned in my posts I did select the Text qualifier within the Bulk
Insert Task.[vbcol=seagreen]
> "Ramon" wrote:
to[vbcol=seagreen]
fits[vbcol=seagreen]
the[vbcol=seagreen]
delimited,[vbcol=seagreen]
field[vbcol=seagreen]
errors.[vbcol=seagreen]
the[vbcol=seagreen]
In[vbcol=seagreen]
SQL-Server.[vbcol=seagreen]
with[vbcol=seagreen]
the[vbcol=seagreen]
well.[vbcol=seagreen]
|||You can also achieve your goal with Bulk Insert some tricky way:
* Suppose you have table:
CREATE TABLE [dbo].[Table1] (
[ColA] [varchar] (50) NOT NULL,
[ColB] [varchar] (50) NOT NULL,
[ColC] [varchar] (50) NOT NULL)
* and file to fe imported contents:
"Value11","Value12","Value13"
"Value21","Value22","Value23"
* then your format file contents should be:
7.0
4
1 SQLCHAR 0 50 "\"" 0 ColA
2 SQLCHAR 0 50 "\",\"" 1 ColA
3 SQLCHAR 0 50 "\",\"" 2 ColB
4 SQLCHAR 0 50 "\"\r\n" 3 ColC
Ramon @. Havana Club,
SQL Server 2000 MCDBA
"Ramon" <Nickolaychuk@.Mail.Ru> / :
news:urpv5MZZEHA.2444@.tk2msftngp13.phx.gbl...
> Bulk Insert does not support behavior you required (but this is not a
bug).[vbcol=seagreen]
> You could use more sophisticated Transform Data for this case.
> Personally I load data by BULK INSERT command into the intermediate table
> and then load formatted data into the target table.
>
> --
> Ramon @. Havana Club,
> SQL Server 2000 MCDBA
>
> "freterink" <freterink@.discussions.microsoft.com> /
> :
> news:2FEB94A7-BE3F-458D-9ECD-4A75BB87E041@.microsoft.com...
> Insert Task is supposed to be much faster in loading the data.
> Insert Task.
try[vbcol=seagreen]
> to
> fits
comma-delimited[vbcol=seagreen]
DTS.[vbcol=seagreen]
> the
it[vbcol=seagreen]
> delimited,
> field
> errors.
qualifiers[vbcol=seagreen]
> the
Excel.
> In
> SQL-Server.
> with
> the
> well.
>
BULK INSERT in 2005 does not understand old BCP format file (cretaed in 2000)
I am not able to BULK INSERT data files in SQL 2005 because it is
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//MishaMisha,
Could you please show the first 5 lines of the format file? Maybe seeing it
would give a clue.
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it
> says
> something about invalid xml character ...) I am aware of the new XML
> format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if
> I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>|||Here are the first 5 lines of my .fmt file:
--cut here--
8.0
26
1 SQLCHAR 0 4 " " 0 Timestamp ""
2 SQLCHAR 0 8000 " " 1 Company ""
--cut here--
The same lines in HEX:
00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48
8.0
26
1 SQLCH
00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D
AR 0 4 " " 0 Tim
00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C
estamp ""
2 SQL
00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09
CHAR 0 8000 " "
00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09
1 Company ""
3
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Misha,
> Could you please show the first 5 lines of the format file? Maybe seeing
> it would give a clue.
> RLF
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine if
>> I
>> use command line BCP utility which allows me to specify the format of the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>|||Misha,
I really do not know the answer. I also get XML errors on "invalid
character", but when I replace your separator character with a "," it all
works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
different errors, but I still get errors.)
It makes me wonder if the format file, when being read is turned into
internal XML and the XML does not allow your separator character (which is
0x07) to exist.
Can you readily change to another separator character?
FWIW,
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Here are the first 5 lines of my .fmt file:
> --cut here--
> 8.0
> 26
> 1 SQLCHAR 0 4 " " 0 Timestamp ""
> 2 SQLCHAR 0 8000 " " 1 Company ""
> --cut here--
> The same lines in HEX:
> 00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48 8.0
> 26
> 1 SQLCH
> 00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D AR 0 4 " " 0
> Tim
> 00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C estamp ""
> 2 SQL
> 00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09 CHAR 0 8000
> " " 00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09 1 Company
> ""
> 3
> //Misha
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Misha,
>> Could you please show the first 5 lines of the format file? Maybe seeing
>> it would give a clue.
>> RLF
>> "Misha" <mishka_mishka@.inbox.ru> wrote in message
>> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine
>> if I
>> use command line BCP utility which allows me to specify the format of
>> the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>>
>|||Thanks a lot for your help Russell,
I have switched the application onto the new XML format file format and it
works fine. Obviously MS has a bug in SQL 2005 with the old fmt files.
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23jgRkJeIHA.288@.TK2MSFTNGP02.phx.gbl...
> Misha,
> I really do not know the answer. I also get XML errors on "invalid
> character", but when I replace your separator character with a "," it all
> works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
> different errors, but I still get errors.)
> It makes me wonder if the format file, when being read is turned into
> internal XML and the XML does not allow your separator character (which is
> 0x07) to exist.
> Can you readily change to another separator character?
> FWIW,
> RLF
>
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Here are the first 5 lines of my .fmt file:
>> --cut here--
>> 8.0
>> 26
>> 1 SQLCHAR 0 4 " " 0 Timestamp ""
>> 2 SQLCHAR 0 8000 " " 1 Company ""
>> --cut here--
>> The same lines in HEX:
>> 00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48 8.0
>> 26
>> 1 SQLCH
>> 00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D AR 0 4 " " 0
>> Tim
>> 00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C estamp ""
>> 2 SQL
>> 00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09 CHAR 0 8000
>> " " 00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09 1
>> Company ""
>> 3
>> //Misha
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Misha,
>> Could you please show the first 5 lines of the format file? Maybe
>> seeing it would give a clue.
>> RLF
>> "Misha" <mishka_mishka@.inbox.ru> wrote in message
>> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine
>> if I
>> use command line BCP utility which allows me to specify the format of
>> the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>>
>>
>
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//MishaMisha,
Could you please show the first 5 lines of the format file? Maybe seeing it
would give a clue.
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it
> says
> something about invalid xml character ...) I am aware of the new XML
> format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if
> I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>|||Here are the first 5 lines of my .fmt file:
--cut here--
8.0
26
1 SQLCHAR 0 4 " " 0 Timestamp ""
2 SQLCHAR 0 8000 " " 1 Company ""
--cut here--
The same lines in HEX:
00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48
8.0
26
1 SQLCH
00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D
AR 0 4 " " 0 Tim
00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C
estamp ""
2 SQL
00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09
CHAR 0 8000 " "
00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09
1 Company ""
3
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Misha,
> Could you please show the first 5 lines of the format file? Maybe seeing
> it would give a clue.
> RLF
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine if
>> I
>> use command line BCP utility which allows me to specify the format of the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>|||Misha,
I really do not know the answer. I also get XML errors on "invalid
character", but when I replace your separator character with a "," it all
works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
different errors, but I still get errors.)
It makes me wonder if the format file, when being read is turned into
internal XML and the XML does not allow your separator character (which is
0x07) to exist.
Can you readily change to another separator character?
FWIW,
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Here are the first 5 lines of my .fmt file:
> --cut here--
> 8.0
> 26
> 1 SQLCHAR 0 4 " " 0 Timestamp ""
> 2 SQLCHAR 0 8000 " " 1 Company ""
> --cut here--
> The same lines in HEX:
> 00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48 8.0
> 26
> 1 SQLCH
> 00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D AR 0 4 " " 0
> Tim
> 00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C estamp ""
> 2 SQL
> 00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09 CHAR 0 8000
> " " 00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09 1 Company
> ""
> 3
> //Misha
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Misha,
>> Could you please show the first 5 lines of the format file? Maybe seeing
>> it would give a clue.
>> RLF
>> "Misha" <mishka_mishka@.inbox.ru> wrote in message
>> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine
>> if I
>> use command line BCP utility which allows me to specify the format of
>> the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>>
>|||Thanks a lot for your help Russell,
I have switched the application onto the new XML format file format and it
works fine. Obviously MS has a bug in SQL 2005 with the old fmt files.
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23jgRkJeIHA.288@.TK2MSFTNGP02.phx.gbl...
> Misha,
> I really do not know the answer. I also get XML errors on "invalid
> character", but when I replace your separator character with a "," it all
> works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
> different errors, but I still get errors.)
> It makes me wonder if the format file, when being read is turned into
> internal XML and the XML does not allow your separator character (which is
> 0x07) to exist.
> Can you readily change to another separator character?
> FWIW,
> RLF
>
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
>> Here are the first 5 lines of my .fmt file:
>> --cut here--
>> 8.0
>> 26
>> 1 SQLCHAR 0 4 " " 0 Timestamp ""
>> 2 SQLCHAR 0 8000 " " 1 Company ""
>> --cut here--
>> The same lines in HEX:
>> 00000000: 38 2E 30 0D 0A 32 36 0D ? 0A 31 09 53 51 4C 43 48 8.0
>> 26
>> 1 SQLCH
>> 00000010: 41 52 09 30 09 34 09 22 ? 07 22 09 30 09 54 69 6D AR 0 4 " " 0
>> Tim
>> 00000020: 65 73 74 61 6D 70 09 22 ? 22 0D 0A 32 09 53 51 4C estamp ""
>> 2 SQL
>> 00000030: 43 48 41 52 09 30 09 38 ? 30 30 30 09 22 07 22 09 CHAR 0 8000
>> " " 00000040: 31 09 43 6F 6D 70 61 6E ? 79 09 22 22 0D 0A 33 09 1
>> Company ""
>> 3
>> //Misha
>> "Russell Fields" <russellfields@.nomail.com> wrote in message
>> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>> Misha,
>> Could you please show the first 5 lines of the format file? Maybe
>> seeing it would give a clue.
>> RLF
>> "Misha" <mishka_mishka@.inbox.ru> wrote in message
>> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>>I am not able to BULK INSERT data files in SQL 2005 because it is
>> complaining about the format of the format description file (.fmt) (it
>> says
>> something about invalid xml character ...) I am aware of the new XML
>> format
>> definition and obviously by default SQL assumes that the format file
>> specified in the BULK INSERT is the new XML one. Everything works fine
>> if I
>> use command line BCP utility which allows me to specify the format of
>> the
>> format file. How do I specify it with BULK INSERT statement?
>> //Misha
>>
>>
>
BULK INSERT in 2005 does not understand old BCP format file (cretaed in 2000)
I am not able to BULK INSERT data files in SQL 2005 because it is
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//Misha
Misha,
Could you please show the first 5 lines of the format file? Maybe seeing it
would give a clue.
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it
> says
> something about invalid xml character ...) I am aware of the new XML
> format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if
> I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>
|||Here are the first 5 lines of my .fmt file:
--cut here--
8.0
26
1 SQLCHAR 0 4 " " 0 Timestamp ""
2 SQLCHAR 0 8000 " " 1 Company ""
--cut here--
The same lines in HEX:
00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48
8.0
26
1SQLCH
00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D
AR04" "0Tim
00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C
estamp""
2SQL
00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09
CHAR08000" "
00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09
1Company""
3
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Misha,
> Could you please show the first 5 lines of the format file? Maybe seeing
> it would give a clue.
> RLF
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||Misha,
I really do not know the answer. I also get XML errors on "invalid
character", but when I replace your separator character with a "," it all
works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
different errors, but I still get errors.)
It makes me wonder if the format file, when being read is turned into
internal XML and the XML does not allow your separator character (which is
0x07) to exist.
Can you readily change to another separator character?
FWIW,
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Here are the first 5 lines of my .fmt file:
> --cut here--
> 8.0
> 26
> 1 SQLCHAR 0 4 " " 0 Timestamp ""
> 2 SQLCHAR 0 8000 " " 1 Company ""
> --cut here--
> The same lines in HEX:
> 00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48 8.0
> 26
> 1 SQLCH
> 00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D AR 0 4 " " 0
> Tim
> 00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C estamp ""
> 2 SQL
> 00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09 CHAR 0 8000
> " " 00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09 1 Company
> ""
> 3
> //Misha
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks a lot for your help Russell,
I have switched the application onto the new XML format file format and it
works fine. Obviously MS has a bug in SQL 2005 with the old fmt files.
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23jgRkJeIHA.288@.TK2MSFTNGP02.phx.gbl...
> Misha,
> I really do not know the answer. I also get XML errors on "invalid
> character", but when I replace your separator character with a "," it all
> works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
> different errors, but I still get errors.)
> It makes me wonder if the format file, when being read is turned into
> internal XML and the XML does not allow your separator character (which is
> 0x07) to exist.
> Can you readily change to another separator character?
> FWIW,
> RLF
>
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
>
complaining about the format of the format description file (.fmt) (it says
something about invalid xml character ...) I am aware of the new XML format
definition and obviously by default SQL assumes that the format file
specified in the BULK INSERT is the new XML one. Everything works fine if I
use command line BCP utility which allows me to specify the format of the
format file. How do I specify it with BULK INSERT statement?
//Misha
Misha,
Could you please show the first 5 lines of the format file? Maybe seeing it
would give a clue.
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>I am not able to BULK INSERT data files in SQL 2005 because it is
> complaining about the format of the format description file (.fmt) (it
> says
> something about invalid xml character ...) I am aware of the new XML
> format
> definition and obviously by default SQL assumes that the format file
> specified in the BULK INSERT is the new XML one. Everything works fine if
> I
> use command line BCP utility which allows me to specify the format of the
> format file. How do I specify it with BULK INSERT statement?
> //Misha
>
|||Here are the first 5 lines of my .fmt file:
--cut here--
8.0
26
1 SQLCHAR 0 4 " " 0 Timestamp ""
2 SQLCHAR 0 8000 " " 1 Company ""
--cut here--
The same lines in HEX:
00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48
8.0
26
1SQLCH
00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D
AR04" "0Tim
00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C
estamp""
2SQL
00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09
CHAR08000" "
00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09
1Company""
3
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
> Misha,
> Could you please show the first 5 lines of the format file? Maybe seeing
> it would give a clue.
> RLF
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:eeqSdL8dIHA.4476@.TK2MSFTNGP06.phx.gbl...
>
|||Misha,
I really do not know the answer. I also get XML errors on "invalid
character", but when I replace your separator character with a "," it all
works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
different errors, but I still get errors.)
It makes me wonder if the format file, when being read is turned into
internal XML and the XML does not allow your separator character (which is
0x07) to exist.
Can you readily change to another separator character?
FWIW,
RLF
"Misha" <mishka_mishka@.inbox.ru> wrote in message
news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
> Here are the first 5 lines of my .fmt file:
> --cut here--
> 8.0
> 26
> 1 SQLCHAR 0 4 " " 0 Timestamp ""
> 2 SQLCHAR 0 8000 " " 1 Company ""
> --cut here--
> The same lines in HEX:
> 00000000: 38 2E 30 0D 0A 32 36 0D 0A 31 09 53 51 4C 43 48 8.0
> 26
> 1 SQLCH
> 00000010: 41 52 09 30 09 34 09 22 07 22 09 30 09 54 69 6D AR 0 4 " " 0
> Tim
> 00000020: 65 73 74 61 6D 70 09 22 22 0D 0A 32 09 53 51 4C estamp ""
> 2 SQL
> 00000030: 43 48 41 52 09 30 09 38 30 30 30 09 22 07 22 09 CHAR 0 8000
> " " 00000040: 31 09 43 6F 6D 70 61 6E 79 09 22 22 0D 0A 33 09 1 Company
> ""
> 3
> //Misha
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:%23vOhYw8dIHA.3756@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks a lot for your help Russell,
I have switched the application onto the new XML format file format and it
works fine. Obviously MS has a bug in SQL 2005 with the old fmt files.
//Misha
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23%23jgRkJeIHA.288@.TK2MSFTNGP02.phx.gbl...
> Misha,
> I really do not know the answer. I also get XML errors on "invalid
> character", but when I replace your separator character with a "," it all
> works. (I tried saving your .fmt file as both ANSI and UNICODE. I get
> different errors, but I still get errors.)
> It makes me wonder if the format file, when being read is turned into
> internal XML and the XML does not allow your separator character (which is
> 0x07) to exist.
> Can you readily change to another separator character?
> FWIW,
> RLF
>
> "Misha" <mishka_mishka@.inbox.ru> wrote in message
> news:%23VY38aEeIHA.5984@.TK2MSFTNGP06.phx.gbl...
>
Sunday, March 11, 2012
Bulk Insert from CSV - trouble with .FMT
I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)
This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.
8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS
I have tried '","' (single quote doublequote comma) to no avail.
I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.
Does anyone know how to resolve this?
You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:
9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""
This file works for data like this one, which I guess is the format of your data too:
1","abc","234
2","def","567
Thanks
Waseem
|||Thanks Waseem - That's just what I neeed to know.
|||Hi.I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:
"2,397", Type-A, EQTY,"2,392",John Smith
What can I do to resolve the comma problem, as well as that the " is not seen as data?|||
Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:
a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.
b) The terminator for 1st field would then be ", instead of a ,
c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,
Thanks
Waseem Basheer
Bulk Insert from CSV - trouble with .FMT
I need to bulk insert from multiple files which are comma-separated with quotes as delimiters around each column. I cannot use DTS because the filenames are variable (unless someone knows how to get DTS to read 'DIR *.csv' and then load each file ?)
This .fmt doesn't work because SQL sees "","" as "" - meaning no terminator - then ,"" where it expects whitespace.
8.0
6
1 SQLCHAR 0 1 "","" 3 Prefix SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 1 "","" 5 Forenames SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 1 "","" 4 Surname SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "","" 6 Job_Title SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 1 "","" 7 Org_Name SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 1 "","" 8 Address1 SQL_Latin1_General_CP1_CI_AS
I have tried '","' (single quote doublequote comma) to no avail.
I cannot use the obvious solution - bulk insert ... (with terminator = ' "," ') - as I need to insert all the data into specified columns of an existing table using different mappings. The .fmt file should be helping, but I cannot get past this issue.
Does anyone know how to resolve this?
You should use the escaped syntax for these special characters so in the field separator your " character should be given as \" just like the C language escape syntax. I am providing a sample format file here:
9.0
3
1 SQLCHAR 0 12 "\",\"" 1 c1 ""
2 SQLCHAR 0 20 "\",\"" 2 c2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 12 "\r\n" 3 c3 ""
This file works for data like this one, which I guess is the format of your data too:
1","abc","234
2","def","567
Thanks
Waseem
|||Thanks Waseem - That's just what I neeed to know.|||Hi.I have a similar problem where my columns are seperated by a comma. The thing is however, that some of my data contains comma's, and that they are then encapsulated by a " to show that it is data. The problem is that this is then seen as a new column.
SAMPLE DATA:
"2,397", Type-A, EQTY,"2,392",John Smith
What can I do to resolve the comma problem, as well as that the " is not seen as data?|||
Does every column value for a particular column have the same format? What I mean is that will in every row your data for 1st and 4th field be quoted or does this vary. If it changes by rows then you are asking for regular expression matching which bcp is not capable of. If the data format is the same, you could play some tricks, for example:
a) First quote could be considered a 1 byte field with no terminator and no corresponding server column.
b) The terminator for 1st field would then be ", instead of a ,
c) Similar treatment for the 4th column, e.g. terminator for EQTY field could be made ," instead of a , and tterminator for 2,392 could be ", instead of a ,
Thanks
Waseem Basheer
Saturday, February 25, 2012
BULK INSERT - escape " - .fmt
Hi All,
Simple table:
TABLE:
create table employee (
name char(20),
title char(120)
)
DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"
I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:
name title
-- --
Gustavo Marketing Assistant
Catherine Engineering Manager
NOT like this:
name title
-- --
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"
So, " should be escape.
Thank you
--
RegardsHello,
I've done this, it's almost finished. You can tray this:
/
***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add
ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@.gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@.hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@.verizon.net";
--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1
fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2
lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3
organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4
address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5
zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6
city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7
state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8
email Latin1_General_CI_AS
bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)
--(2 row(s) affected)
select*
from ImportTest
Dennis Eschen NULL NULL 94920 Alhambra CA
de@.hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA
vweber@.verizon.net";
/
***************************************************************************************************/
For me now most important is why didn't insert first row (data row,
not column definition)?
When I added quotation mark to column definition works fine, but I
can't modify .csv file, I received it from third part company :( is it
possible add all rows without modification .csv file?
Second, how remove quotation mark and semicolon from last column?
Regards,
anxcomp
Simple table:
TABLE:
create table employee (
name char(20),
title char(120)
)
DATAFILE(.csv):
name;title
Gustavo;"Marketing Assistant"
Catherine;"Engineering Manager"
I'd like use BULK INSERT command to insert this data. Tell me please,
how should look formatfile (.fmt) for this example (SQL 2005). I need
data after BULK INSERT looks like this:
name title
-- --
Gustavo Marketing Assistant
Catherine Engineering Manager
NOT like this:
name title
-- --
Gustavo "Marketing Assistant"
Catherine "Engineering Manager"
So, " should be escape.
Thank you
--
RegardsHello,
I've done this, it's almost finished. You can tray this:
/
***************************************************************************************************/
--table
CREATE TABLE [dbo].[ImportTest](
[fname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[lname] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[organization] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[address] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[zip] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[state] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[email] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
--data file (ImportTest.csv), REMEMBER at the end you HAVE TO add
ENTER
fname;lname;organization;address;zip;city;state;email;
Shirley;Birosik;;;90013;"Los Angeles";"CA";"sbirosik@.gmail.com";
Dennis;Eschen;;;94920;"Alhambra";"CA";"de@.hotmail.com";
Valerie;Chambers;;;91803;"Long Beach";"CA";"vweber@.verizon.net";
--fmt file(ImportTest.Fmt), add ENTER at the end
9.0
8
1 SQLCHAR 0 510 ";" 1
fname Latin1_General_CI_AS
2 SQLCHAR 0 510 ";" 2
lname Latin1_General_CI_AS
3 SQLCHAR 0 510 ";" 3
organization Latin1_General_CI_AS
4 SQLCHAR 0 510 ";" 4
address Latin1_General_CI_AS
5 SQLCHAR 0 510 ";\"" 5
zip Latin1_General_CI_AS
6 SQLCHAR 0 510 "\";\"" 6
city Latin1_General_CI_AS
7 SQLCHAR 0 510 "\";\"" 7
state Latin1_General_CI_AS
8 SQLCHAR 0 510 "\r\n" 8
email Latin1_General_CI_AS
bulk insert ImportTest
from 'd:\bulk\ImportTest.csv'
with (
formatfile = 'd:\bulk\ImportTest.Fmt',
firstrow = 2
)
--(2 row(s) affected)
select*
from ImportTest
Dennis Eschen NULL NULL 94920 Alhambra CA
de@.hotmail.com";
Valerie Chambers NULL NULL 91803 Long Beach CA
vweber@.verizon.net";
/
***************************************************************************************************/
For me now most important is why didn't insert first row (data row,
not column definition)?
When I added quotation mark to column definition works fine, but I
can't modify .csv file, I received it from third part company :( is it
possible add all rows without modification .csv file?
Second, how remove quotation mark and semicolon from last column?
Regards,
anxcomp
Subscribe to:
Posts (Atom)