Solved

BCP and the Format File

Posted on 1998-09-10
2
654 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 20 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now