Solved

BCP and the Format File

Posted on 1998-09-10
2
685 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

691 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