Format file - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Format file

Author

Topic

sek
Starting Member

27 Posts

Posted - 2009-06-30 : 23:36:38

How to generate a format file for a particular table???is there any wizard???in google i found some sites given info related to this.but its not clear.so anybody can tell me how to do this???

thanks in advance.

regards,
sekhar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-30 : 23:38:26

you can use BCP to perform a BCP OUT and don't specify the format file, It will prompt you for a filename to save the format file. The default will be bcp.fmt if you didn't specify any.

or just


bcp [DBNAME].[SCHEMA].[TABLENAME] format nul -T -n -f FormatFileName.fmt -SServerName

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 00:00:04

hi,
can you tell me the syntax for that?as i check there are so many commandline options in bcp.

thanks for giving reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 00:01:35

i just edited the post

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 00:14:40

hi khatan,
I am getting the following error when i execute bcp cpmmand.

C:\shared>bcp [TEST].[dbo].[Acct_Template_JUN24] format nul -T -n -f bcp.fmt -S
BPO73 -U sa -P sa
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requ
ested in login '[TEST]'. Login fails.

i am giving the correct credentials only.
where am i doing wrong???
in my server roles,role is marked as system adminstrators.

thanks for giving reply.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 00:16:58

You are using SQL authentication to login, remove the -T. It's for trusted connection.

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 00:27:28

Hi Khatan,
still i am getting the same problem.

C:\shared>bcp [TEST].[dbo].[Acct_Template_JUN24] format nul -n -f bcp.fmt -S BPO
73 -U sa -P sa
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requ
ested in login '[TEST]'. Login fails.

can you tell me what is my mistake??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 00:29:54

remove the []. think that's causing some confusion

bcp TEST.dbo.Acct_Template_JUN24 format nul -n -f bcp.fmt -S BPO -U sa -P sa

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 00:46:58

hi khatan,
you are correct.after removing brackets bcp file is generated.thanks for your help.

do you have any idea regarding bulk insert???
actually I need to import large date from csv file into a table.I am using bulk insert query.

my data is something like this.commas(,) are also there in the data.i need to import the data correctly with out quotes(")

"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","541448800000019637",55147,"Sibelgas Noord","Utility"

my bulk insert command is like below.

BULK INSERT Acct_Template_JUN30
FROM 'c:\shared\Acct_Template_JUN30'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '"",""',
ROWTERMINATOR = '\n' ,
FORMATFILE='c:\shared\bcp.fmt'
)
when i am executing i am getting the following error.
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 2 in format file 'c:\shared\bcp.fmt'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 00:57:09

oh . . it's from http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=128531

quote:
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 2 in format file 'c:\shared\bcp.fmt'.

The error says you have specified an invalid collation name. Did you modify the fmt file that was generated ?

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 01:03:29

Hi Khatan,
I am not modified the generated bcp file

my csv file column headers are like below.

"Client","Site Ref No","Site","Address 1","Address 2","Address 3","City","State","Zip Code","Country","Account","Account id","Vendor","Account Type"

my generated bcp file is like below.

8.0
14
1 SQLCHAR 2 255 "" 1 Client SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 255 "" 2 Site Ref No SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 255 "" 3 Site SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 255 "" 4 Address 1 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 255 "" 5 Address 2 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 255 "" 6 Address 3 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 255 "" 7 City SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 255 "" 8 State SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 255 "" 9 Zip Code SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 255 "" 10 Country SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 255 "" 11 Account SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 255 "" 12 Account id SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 255 "" 13 Vendor SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 2 255 "" 14 Account Type SQL_Latin1_General_CP1_CI_AS

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 01:06:51

is the collation of the 2 tables different ? You generated the fmt from Acct_Template_JUN24 but you use the fmt for bulk insert into Acct_Template_JUN30.

Why not generate the format file from Acct_Template_JUN30 ?

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 01:10:00

Hi khatan,I am forgotten to inform you that I am genarated format file for Acct_Template_JUN30 only.

here is my bulk insert query
BULK INSERT Acct_Template_JUN30
FROM 'c:\shared\Acct_Template_JUN30'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n' ,
FORMATFILE='c:\shared\bcp.fmt'
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 01:37:15

try without format file


BULK INSERT Acct_Template_JUN30
FROM 'c:\shared\Acct_Template_JUN30'
WITH
(
    FIRSTROW        = 2,
    MAXERRORS       = 0,
    FIELDTERMINATOR = '","',
    ROWTERMINATOR   = '\n' 
)

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 01:58:27

Hi khtan,
I alredy tried with that option.I got the following error.

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 13491, column 14 (Account Type).
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
The statement has been terminated.

Now i kept field separator and row separator option also in bcp command.

C:\shared>bcp TEST.dbo.Acct_Template_JUN30 format nul -n -f bcp1.fmt -t ""","""
-r "\n" -S BPO73 -U sa -P sa

my format file came like below.

8.0
14
1 SQLCHAR 2 255 "\",\"" 1 Client SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 2 255 "\",\"" 2 Site Ref No SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 2 255 "\",\"" 3 Site SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 2 255 "\",\"" 4 Address 1 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 2 255 "\",\"" 5 Address 2 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 2 255 "\",\"" 6 Address 3 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 2 255 "\",\"" 7 City SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 2 255 "\",\"" 8 State SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 2 255 "\",\"" 9 Zip Code SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 2 255 "\",\"" 10 Country SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 2 255 "\",\"" 11 Account SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 2 255 "\",\"" 12 Account id SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 2 255 "\",\"" 13 Vendor SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 2 255 "\r\n" 14 Account Type SQL_Latin1_General_CP1_CI_AS

now i changed my bulk insert like below.

BULK INSERT Acct_Template_JUN30
FROM 'c:\shared\Acct_Template_JUN30'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = '"\",\""',
ROWTERMINATOR = '"\r\n"' ,
FORMATFILE='c:\shared\bcp1.fmt'
)

i got the following error.

Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 2 in format file 'c:\shared\bcp1.fmt'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:04:57

can you post the DDL for the table Acct_Template_JUN30 ?

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 02:07:22

Hi Khtan,
below I am pasted the DDL for the table which was generated by the wizard.

CREATE TABLE [dbo].[Acct_Template_JUN30] (
[Client] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site Ref No] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Site] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address 1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address 2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address 3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip Code] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account id] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Vendor] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Account Type] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 02:17:01

Hi Khtan,
Actually i need to import data into the table.
my data in the csv file is like below.

"Client","Site Ref No","Site","Address 1","Address 2","Address 3","City","State","Zip Code","Country","Account","Account id","Vendor","Account Type"
"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","541448800000019637",55147,"Sibelgas Noord","Utility"
"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","",55880,"E.On Belgium N.V.","Supplier"
"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","541448811000075707",64753,"GfE Belgium","Utility"
"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","",64754,"Electrabel Solutions","Supplier"
"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","",94097,"E.On Belgium N.V.","Supplier"
"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","",96396,"Luminus","Supplier"
"3M Belgium N.V.","4672","Zwijndrecht, BEL (3M Belgium N.V. - Zwijndrecht, BEL)","Canadastraat 11 - Haven 1005","","","Zwijndrecht","BEL","2070","Belgium","541453198122805354",55148,"Elia","Utility"
"3M Belgium N.V.","4672","Zwijndrecht, BEL (3M Belgium N.V. - Zwijndrecht, BEL)","Canadastraat 11 - Haven 1005","","","Zwijndrecht","BEL","2070","Belgium","0380002",55149,"Fluxys","Utility"
"3M Belgium N.V.","4672","Zwijndrecht, BEL (3M Belgium N.V. - Zwijndrecht, BEL)","Canadastraat 11 - Haven 1005","","","Zwijndrecht","BEL","2070","Belgium","",55878,"Electrabel","Supplier"
"3M Belgium N.V.","4672","Zwijndrecht, BEL (3M Belgium N.V. - Zwijndrecht, BEL)","Canadastraat 11 - Haven 1005","","","Zwijndrecht","BEL","2070","Belgium","",55879,"Distrigas N.V.","Supplier"
"3M Belgium N.V.","4672","Zwijndrecht, BEL (3M Belgium N.V. - Zwijndrecht, BEL)","Canadastraat 11 - Haven 1005","","","Zwijndrecht","BEL","2070","Belgium","",65085,"Electrabel Solutions","Supplier"
"3M Belgium N.V.","4672","Zwijndrecht, BEL (3M Belgium N.V. - Zwijndrecht, BEL)","Canadastraat 11 - Haven 1005","","","Zwijndrecht","BEL","2070","Belgium","",70455,"Distrigas","Supplier"
"801 Rob S.A.","1168","Woluwe-Saint-Pierre, BEL (801 Rob S.A. - Woluwe-Saint-Pierre, BEL)","boulevard de la Woluwe 28 Bte 1","","","Woluwe-Saint-Pierre","BEL","1150","Belgium","541448911000015397",55339,"Sibelga","Utility"
"801 Rob S.A.","1168","Woluwe-Saint-Pierre, BEL (801 Rob S.A. - Woluwe-Saint-Pierre, BEL)","boulevard de la Woluwe 28 Bte 1","","","Woluwe-Saint-Pierre","BEL","1150","Belgium","541448911000025730",55340,"Sibelga","Utility"
"801 Rob S.A.","1168","Woluwe-Saint-Pierre, BEL (801 Rob S.A. - Woluwe-Saint-Pierre, BEL)","boulevard de la Woluwe 28 Bte 1","","","Woluwe-Saint-Pierre","BEL","1150","Belgium","",55651,"Alg Négoce S.A.","Supplier"
"801 Rob S.A.","1168","Woluwe-Saint-Pierre, BEL (801 Rob S.A. - Woluwe-Saint-Pierre, BEL)","boulevard de la Woluwe 28 Bte 1","","","Woluwe-Saint-Pierre","BEL","1150","Belgium","",55652,"E.On Belgium N.V.","Supplier"
"801 Rob S.A.","1168","Woluwe-Saint-Pierre, BEL (801 Rob S.A. - Woluwe-Saint-Pierre, BEL)","boulevard de la Woluwe 28 Bte 1","","","Woluwe-Saint-Pierre","BEL","1150","Belgium","",84000,"Luminus","Supplier"
"802 Retail Services Ath","18770","Ath, BEL (802 Retail Services Ath - Ath, BEL)","Chaussée de Mons 323","","","Ath","BEL","7800","Belgium","541449011000037890",55589,"IEH","Utility"
"802 Retail Services Ath","18770","Ath, BEL (802 Retail Services Ath - Ath, BEL)","Chaussée de Mons 323","","","Ath","BEL","7800","Belgium","",56316,"E.On Belgium N.V.","Supplier"
"802 Retail Services Ath","18796","Kain, BEL (806 Retail Services Kain - Kain, BEL)","Rue du Troisième Age 2","","","Kain","BEL","7540","Belgium","541449011000050677",55590,"IEH","Utility"

the data in the columns also having comma(,)

i want to insert the data properly without quotes(") into the table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:20:46

I have test with your table DDL, following bulk insert statement


BULK INSERT Acct_Template_JUN30
FROM 'c:\shared\Acct_Template_JUN30'
WITH
(
    FIRSTROW        = 2,
    MAXERRORS       = 0,
    FIELDTERMINATOR = '","',
    ROWTERMINATOR   = '\n' 
)

and copy and paste the following into notepad and save the file.

"3M Belgium N.V.","4671","Diegem, BEL (3M Belgium N.V. - Diegem, BEL)","Hermeslaan 7","","","Diegem","BEL","1831","Belgium","541448800000019637","55147","Sibelgas Noord","Utility"

Note the " in red was missing around 55147 in the sample text you provided.

and it works.

KH
[spoiler]Time is always against us[/spoiler]

sek
Starting Member

27 Posts

Posted - 2009-07-01 : 02:26:40

Hi khtan,
Actually my data will be like that only.that column data dont have quotes.some columns data in quotes and some dont have quotes.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-01 : 02:31:51

BCP or BULK INSERT works and expecting fixed delimiter. In your case, it is not fixed at all.

You could import the file into a staging table with single column and then parse the string from there into required column.

Or you can check out SSIS.

KH
[spoiler]Time is always against us[/spoiler]

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources