bulk copy using format file 
Author Message
 bulk copy using format file

Hi,

I have a table
which i need to copy some data into. The data format is
comma delimited with quotes around strings.
Using the table below. At first I created a format file
using the correct types ie SQLINT but found the integers
were screwed and seemed to correct itself when I just used
SQLCHAR. Now if possible I would like to remove the quotes
that appear around strings after I have imported the data

Tia

Stu
Table :
CREATE TABLE [dbo].[ImportTest] (
        [idkey] [int] NOT NULL ,
        [testdesc1] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [idCountry] [int] NOT NULL ,
        [testDesc2] [varchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

Bcp format File :
8.0
4
1       SQLCHAR       0      
4       ","                     1     idKey         ""
2       SQLCHAR       0      
50      ","                     2     testdesc1    
SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0      
4       ","                     3     idcountry     ""
4       SQLCHAR       0      
50      "\r\n"                  4     testdesc2    
SQL_Latin1_General_CP1_CI_AS

SQL command
bulk insert cgcalcdb.dbo.importTest
FROM 'c:\temp\import.txt'
WITH (formatFile='c:\temp\bcpFormat')



Sat, 21 Jan 2006 13:52:17 GMT
 bulk copy using format file

if you want to update table data you will have to use update statement as
shown in the following example:
ex:
create table #t (i varchar(500))
insert into #t values('"vishal"')
insert into #t values('"vikram"')
insert into #t values('"vijay"')
--Update statement to replace double quotes.
update #t set i = replace (i, '"', '')

select * from #t

--
-Vishal


Quote:
> Hi,

> I have a table
> which i need to copy some data into. The data format is
> comma delimited with quotes around strings.
> Using the table below. At first I created a format file
> using the correct types ie SQLINT but found the integers
> were screwed and seemed to correct itself when I just used
> SQLCHAR. Now if possible I would like to remove the quotes
> that appear around strings after I have imported the data

> Tia

> Stu
> Table :
> CREATE TABLE [dbo].[ImportTest] (
> [idkey] [int] NOT NULL ,
> [testdesc1] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [idCountry] [int] NOT NULL ,
> [testDesc2] [varchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO

> Bcp format File :
> 8.0
> 4
> 1       SQLCHAR       0
> 4       ","                     1     idKey         ""
> 2       SQLCHAR       0
> 50      ","                     2     testdesc1
> SQL_Latin1_General_CP1_CI_AS
> 3       SQLCHAR       0
> 4       ","                     3     idcountry     ""
> 4       SQLCHAR       0
> 50      "\r\n"                  4     testdesc2
> SQL_Latin1_General_CP1_CI_AS

> SQL command
> bulk insert cgcalcdb.dbo.importTest
> FROM 'c:\temp\import.txt'
> WITH (formatFile='c:\temp\bcpFormat')



Sat, 21 Jan 2006 14:25:50 GMT
 bulk copy using format file
I had already thought of that. I was hoping I could do it
in one Bulk insert statement

Thanks

Quote:
>-----Original Message-----
>if you want to update table data you will have to use
update statement as
>shown in the following example:
>ex:
>create table #t (i varchar(500))
>insert into #t values('"vishal"')
>insert into #t values('"vikram"')
>insert into #t values('"vijay"')
>--Update statement to replace double quotes.
>update #t set i = replace (i, '"', '')

>select * from #t

>--
>-Vishal



>> Hi,

>> I have a table
>> which i need to copy some data into. The data format is
>> comma delimited with quotes around strings.
>> Using the table below. At first I created a format file
>> using the correct types ie SQLINT but found the integers
>> were screwed and seemed to correct itself when I just
used
>> SQLCHAR. Now if possible I would like to remove the
quotes
>> that appear around strings after I have imported the
data

>> Tia

>> Stu
>> Table :
>> CREATE TABLE [dbo].[ImportTest] (
>> [idkey] [int] NOT NULL ,
>> [testdesc1] [varchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>> [idCountry] [int] NOT NULL ,
>> [testDesc2] [varchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NOT NULL
>> ) ON [PRIMARY]
>> GO

>> Bcp format File :
>> 8.0
>> 4
>> 1       SQLCHAR       0
>> 4       ","                     1     idKey         ""
>> 2       SQLCHAR       0
>> 50      ","                     2     testdesc1
>> SQL_Latin1_General_CP1_CI_AS
>> 3       SQLCHAR       0
>> 4       ","                     3     idcountry     ""
>> 4       SQLCHAR       0
>> 50      "\r\n"                  4     testdesc2
>> SQL_Latin1_General_CP1_CI_AS

>> SQL command
>> bulk insert cgcalcdb.dbo.importTest
>> FROM 'c:\temp\import.txt'
>> WITH (formatFile='c:\temp\bcpFormat')

>.



Sat, 21 Jan 2006 14:43:44 GMT
 bulk copy using format file
Stu,

Quote:
> I have a table
> which i need to copy some data into. The data format is
> comma delimited with quotes around strings.
> Using the table below. At first I created a format file
> using the correct types ie SQLINT but found the integers
> were screwed and seemed to correct itself when I just used
> SQLCHAR. Now if possible I would like to remove the quotes
> that appear around strings after I have imported the data

You can eliminate the quotes in the format file.
All fields in a text file, even numeric fileds, should be SQLCHAR in
the format file.

8.0
4
1 SQLCHAR 0 4   ",\""    1 idKey     ""
2 SQLCHAR 0 50  "\","    2 testdesc1 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 4   ",\""    3 idcountry ""
4 SQLCHAR 0 50  "\"\r\n" 4 testdesc2 SQL_Latin1_General_CP1_CI_AS

Linda



Sat, 21 Jan 2006 15:53:27 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Bulk Copy: Format File Read Errors

2. Bulk inserts failing with errors 4866, and 7399 - Using format files

3. bulk insert using format file

4. having trouble loading a fixed length file using Bulk Copy (BCP)

5. Bulk copy from file using ^ as delimitier

6. BULK INSERT or BULK COPY

7. Bulk Insert / Bulk copy

8. BULK INSERT under bulk copy?

9. using bulk copy program

10. bulk copy using UNC

11. Using triggers when bulk-copying data ??


 
Powered by phpBB® Forum Software