Solved

BCP and the Format File

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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

17 Experts available now in Live!

Get 1:1 Help Now