Solved

BCP and the Format File

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Replace Carriage Returns for Excel Exports 17 49
Accessing SQL Server Instance from outside the network 29 97
string fuctions 4 25
T-SQL: "HAVING CASE" Clause 1 23
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

816 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

10 Experts available now in Live!

Get 1:1 Help Now