?
Solved

BCP and the Format File

Posted on 1998-09-10
2
Medium Priority
?
723 Views
Last Modified: 2012-08-13
I am trying to dump a fixed-width data file into a SQL Database.  I know I must use bcp but I am unable to find any info on how to develop the format file (i.e. *.fmt).  Can someone please help?  Remember the flat file is defined by a fixed width.  Thanks...Michael P.  mprichar@pharmacy.com
0
Comment
Question by:MichaelRotech
2 Comments
 
LVL 2

Accepted Solution

by:
jbiswas earned 80 total points
ID: 1089993
when you do a bcp if you omit the -c clause it is gonna ask you for the format of every field. You can enter the field specifics and then generate the .fmt file. Once you have generated the format..save it as a .fmt file..the bcp operation starts..and on successful completion you are all set. If the operation fails massage the format file to accept your data. It is a bit cryptic but you should be able to figure it out with a little effort. Later, once you have altered the format file you can metion the format file on the command line with a -f switch.
So initially you would type:
bcp db_name..table_name in datafile_name -Uuser_name -Ppassword
-Sserver_name
This prompts you for the format(datatype, delimiter etc) of every field. Do not forget to give a end_of_line character \n for the last field.
Once done it prompts you if you wanna save this file ..say yes and save it in some directory.. then the bcp operation starts. If it fails..open the format file and play around with the file until you have a successful bcp in. Don't forget..once you have the format file(say xyz.fmt)..you can apply the format..with a
-fxyz.fmt switch added to the previous command.
0
 

Author Comment

by:MichaelRotech
ID: 1089994
Thank You...very much.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question