Bulk Insert Problems using FromatFile

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Bulk Insert Problems using FromatFile

Author

Topic

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-14 : 10:57:36

Hi

I am trying to import a csv file created from excel. All the fields in the file have double quotes around them and fields are separated by commas. Although some fields have commas inside the quotes. Basically I want to bulk import the data and remove the double quotes and leave any commas within the fields alone(or is it possible to strip these out?).
Also the first field is a number and a zero is being added to the front of value 8973 because it is not the same length as the others?

Example Data From File:

"08973","john,smith","555-555"
"12345","pete,scott","3333-333"
"56777","jo brown","3343-7878"
"33333","james,may","93939-999"
....etc

Format File:
9.0
30
1 SQLCHAR 0 100 ",\"" 1 Field1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\"," 2 Field2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 ",\"" 3 Field3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\"," 4 Field4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 ",\"" 5 Field5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "\"," 6 Field6 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 ",\"" 7 Field7 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "\"," 8 Field8 SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 ",\"" 9 Field9 SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "\"," 10 Field10 SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 ",\"" 11 Field11 SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "\"," 12 Field12 SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 ",\"" 13 Field13 SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "\"," 14 Field14 SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 ",\"" 15 Field15 SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "\"," 16 Field16 SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 ",\"" 17 Field17 SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "\"," 18 Field18 SQL_Latin1_General_CP1_CI_AS
19 SQLCHAR 0 100 ",\"" 19 Field19 SQL_Latin1_General_CP1_CI_AS
20 SQLCHAR 0 100 "\"," 20 Field20 SQL_Latin1_General_CP1_CI_AS
21 SQLCHAR 0 100 ",\"" 21 Field21 SQL_Latin1_General_CP1_CI_AS
22 SQLCHAR 0 100 "\"," 22 Field22 SQL_Latin1_General_CP1_CI_AS
23 SQLCHAR 0 100 ",\"" 23 Field23 SQL_Latin1_General_CP1_CI_AS
24 SQLCHAR 0 100 "\"," 24 Field24 SQL_Latin1_General_CP1_CI_AS
25 SQLCHAR 0 100 ",\"" 25 Field25 SQL_Latin1_General_CP1_CI_AS
26 SQLCHAR 0 100 "\"," 26 Field26 SQL_Latin1_General_CP1_CI_AS
27 SQLCHAR 0 100 ",\"" 27 Field27 SQL_Latin1_General_CP1_CI_AS
28 SQLCHAR 0 100 "\"," 28 Field28 SQL_Latin1_General_CP1_CI_AS
29 SQLCHAR 0 100 ",\"" 29 Field29 SQL_Latin1_General_CP1_CI_AS
30 SQLCHAR 0 100 "\n" 30 Field30 SQL_Latin1_General_CP1_CI_AS

The bulk insert code is as follows:
bulk INSERT dbo.Bulk3 FROM 'C:\testing.csv' WITH (FIRSTROW = 2, FORMATFILE='C:\FormatFile2.txt')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 11:00:10

can i ask what problem you're facing?

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-14 : 11:06:34

I am getting quotes left around some of the data.

e.g.

after bulk import data in table looks like
Field 1
"081233"

Field2
Joe smith

Field3
"58585-55"

it appears to happen every other field and the last field only as the last double quote e.g. lastfield"

I dont want the quotes at all.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 11:24:35

try like


Format File:
9.0
30
1 SQLCHAR 0 0 "\"" 0 Field1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "\",\"" 1 Field2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "\",\"" 2 Field2 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\"" 3 Field2 SQL_Latin1_General_CP1_CI_AS
...

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-14 : 11:32:10

Hi

I will give it a try,

Will i still need the \n on the last row?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-14 : 11:39:20

quote:
Originally posted by Looper

Hi

I will give it a try,

Will i still need the \n on the last row?

yup. to indicate end of last field

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-15 : 04:14:32

Hi

Thanks that worked. However I still have the problem with the following information. In the excel sheet I have a reference number 87654. But by when I bulk import it and view it in the table it has become 087654. Is there anyway I can prevent this from happening. I have no control over the format of the excel sheet as it is coming from an external source?

Cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-15 : 04:47:01

quote:
Originally posted by Looper

Hi

Thanks that worked. However I still have the problem with the following information. In the excel sheet I have a reference number 87654. But by when I bulk import it and view it in the table it has become 087654. Is there anyway I can prevent this from happening. I have no control over the format of the excel sheet as it is coming from an external source?

Cheers

try converting it int before importing

Looper
Yak Posting Veteran

68 Posts

Posted - 2010-01-15 : 06:22:10

How do I do that, using the formatfile? But the problem is that this field can have alph chars as well e.g. 8TA555 as well as 87664 etc.?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources