BCP Format File - Microsoft SQL Server

Rathtap

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?

Jul 20 '05 #1

Subscribe Reply

11021

Erland Sommarskog

Rathtap (am****@yahoo.c om) writes:

A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?

Don't know off hand. Can you post a sample of the input file, and a CREATE
TABLE statement for your table?

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #2

Rathtap

This is the table:
CREATE TABLE [dbo].[CT_RATE_TEMP] (
[code] [varchar] (20) COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NOT NULL ,
[CODERATE] [smallmoney] NULL ,
[CODERATE2] [smallmoney] NULL ,
[CODERATE3] [smallmoney] NULL ,
[CODERATE4] [smallmoney] NULL
) ON [PRIMARY]
GO

And here is the import file:
36415 25.00
52759 24.50
52760 12.77
52775 24.03
74285 16.88
74436 7.84
80003 7.58
80004 8.00
80005 8.92
80006 8.94
80007 9.32
80008 9.66
80009 9.90
80010 9.90
80011 10.08
80012 10.29
Here I would like Col1 of the file to map to the Code field and Col2
to CodeRate. CODERATE2,CODER ATE3 and CODERATE4 should be null.

On the other hand there may be the following file to import where all
fields match, then there is no problem:
52759 24.50 24.50 24.50
52760 12.77 12.77 12.77
52775 24.03 24.03 24.03
74285 16.88 16.88 16.88
74436 7.84 7.84 7.84
80003 7.58 7.58 7.58
80004 8.00 8.00 8.00
80005 8.92 8.92 8.92
80006 8.94 8.94 8.94
80007 9.32 9.32 9.32
80008 9.66 9.66 9.66
80009 9.90 9.90 9.90
80010 9.90 9.90 9.90
80011 10.08 10.08 10.08
80012 10.29 10.29 10.29
80016 12.05 12.05 12.05
80018 12.14 12.14 12.14
80019 12.61 12.61 12.61
Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************@1 27.0.0.1>...

Rathtap (am****@yahoo.c om) writes:
A format file provides a way to bulk copy data selectively from a data
file to an instance of SQL Server. This allows the transfer of data to
a table when there is a mismatch between fields in the data file and
columns in the table.
I take it this assumes the number of fields in the data file will
always be constant. What if it is not?
My table has two columns but my datafile may have 2 to 4 columns and I
want to always select only the first two. Is there a way to set up the
format file to accomplish that?

Don't know off hand. Can you post a sample of the input file, and a CREATE
TABLE statement for your table?

Jul 20 '05 #3

mgran

New Member

try adding default values to the null fields

Jul 24 '06 #4

Similar topics

1656

BCP Format File

by: Query Builder | last post by:

I am new to BCP. Can anyone help me understand this? I tried searching the BOL but it doesnt show much help on syntex. Lets say I have a simple table and want to out put the records into a file. Then with that output file, I want to create a format file using the BCP utillity and builk insert it into another table. Can someone explain me the syntex involved in this.. No need to go so elabrate.. Just give me a basic senario and just the...

Microsoft SQL Server

2944

ifstream and format issues

by: Pat | last post by:

Here is my problem that I am having with a current project. I went and backed up all my hard drives to DVD's. After backing up the drives, I used a batch file to get all the file names from the disk to a text file for future reference. I now have over 60 huge text files and would like to place the information into either a database or an excel document. I would like to read in from a text file and output the text file to an excel .cvs...

C / C++

3727

bcp with format file

by: KR | last post by:

I am trying to copy the data in excel file into a table using the bcp and this is the code that I have. However the bcp utility does not seem to create a format file, which I thought it should do. I am probably going about this all wrong so any help would be useful. exec master..xp_cmdshell '(FOR %i IN ("E:\WUTemp\*") DO (bcp #ProspectImportTest in "%i" -fE:\WUTemp\Prospect.fmt)' bulk insert #ProspectImportTest from 'E:\WUTemp\*."'...

Microsoft SQL Server

35636

changing the date format in vb.net

by: andreas | last post by:

When I copy a vb.net project using date formats from one PC with a windows date format f.e. dd/mm/yyyy to another PC having a format yy/mm/dd then I get errors. How can I change for a while in the project the date format in vb.code ( not in Windows) and how can I find out which date format the PC Windows is using. Thanks for any response

Visual Basic .NET

9718

format files for use with bulk insert

by: Ted | last post by:

I used bcp to produce the apended format file. How can it be modified to recognize the quotes that surround the text fields and not insert the quotes along with the text? Invariably, the first four columns have text surrounded by quotes and are terminated by tabs. If the first column has "abc", only abc ought to be inserted into that field in the table. Thanks

Microsoft SQL Server

2267

tools/options/advanced/default file format

by: leahf via AccessMonster.com | last post by:

I upgraded from Access97 to Access2002 last year. I just noticed that the default file format is Access 2000. Can that be the reason that I sometimes get "Microsoft Access has encountered..." when sending e-mails to a group of teachers? And why, when upgrading to 2002, would the default file format be for Access2000? Should I change the format to 2002 or could that cause other problems? --

Microsoft Access / VBA

2036

comp.databases.ms-access: 2002 file format problem

by: karthikla | last post by:

Hi Experts, Could you please help me on how to get access 2002 file format exactly while doing conversion from Access 97 file format by DBEngine.CompactDatabase() function? Code: Private Sub Command1_Click() DBEngine.CompactDatabase "c:\ss97.mdb", "c:\ss2002.mdb",

Microsoft Access / VBA

2024

Output using format

by: johnperl | last post by:

i am working on the script as below, i want an output according to the format. i am trying to put everything in one loop but cannot figure out the way to do so. could anyone please help me with this. &file_scan($orifile); sub file_scan { our $reqfile = $_; open(FILE, $reqfile) or die "Cannot open the file: $!\n";

Perl

5245

How to Write Text data in Binary Format

by: aagarwal8 | last post by:

Hi, I am trying to write the contents of a textbox to a file in binary format. My code looks like this... private void btnWriteToFile_Click(object sender, EventArgs e) { FileStream fs = File.Open(@"D:\test.dat", FileMode.OpenOrCreate, FileAccess.Write); BinaryWriter bw = new BinaryWriter(fs);

C# / C Sharp

3364

how to convert a video file in .flv format in php for linux hosting

by: sonu | last post by:

hey good morning ...... how to convert a video file in .flv format in php for linux hosting......is there any package whis provide this facility . Can i use ffmpeg for linux hosting project.......... plz help me.. thanks & regards Prabhat

PHP

9621

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9454

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...

Windows Server

10039

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

8937

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7463

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6717

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5484

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4012

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2852

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General