Solved

Loading data via BCP when there are missing columns in text file

Posted on 2012-03-29
2
819 Views
Last Modified: 2012-04-10
Hi,
I have number of tables with NULL column values allowed within them.

I have data files which I need to import into these tables using BCP. As expected, BCP needs matching columns with text data and surely not the case here.

I can see suggestion of using format file but it's not working for me.

I don't want to hand edit tonnes of files.I need to know if there is an easy way to create format files  using  BCP switches or any other alternative?

Thanks
0
Comment
Question by:crazywolf2010
[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 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 37785451
You should look at the format file for bcp.

http://msdn.microsoft.com/en-us/library/ms191479.aspx

Look at the section that tells you about column order.  You can map the field 4 in the file to field 6 in the table.

And here is the reference for skipping the field in the table with the format file.

http://msdn.microsoft.com/en-us/library/ms179250.aspx
0
 

Author Comment

by:crazywolf2010
ID: 37786282
Hi,
Do I have to hand code each of the fmt file?


Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Recursion 6 45
shrink table after huge delete 2 34
How to add a suffix to a value in a column based on the value in another column 4 36
HIghlights of SSIS? 3 42
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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