I have a txt file I have to bcp in. The file contains fixed-position columns
of data but there is white-space in certain positions which I have to skip
over.  So I need a way to provide start position for some columns.  I do not
see a way to do this with format files. 

Example

CCSBLLPLN      00001 00000000ADDED
The "|" 's are added to indicate the start of each field column.  Note the
extra blank after the "00001' I have to skip over before picking-up the next
numeric field.

Thanks,

Mike

Hi Snake,

I'm sure someone can answer your question. 

However, I want to share with you a technique I use when I have to get data
into a specific format to address limitations of an import process (or just
to make it easier to import the data).  With fixed length columns, you can
sometimes eliminate blank columns using a good text editor that has column
edit capabilities.  Two editors I have used are UltraEdit and MultiEdit.  You
can download a trial version of both.  These editors have column edit
features where you can delete, insert, and fill columns as needed.  I'm sure
there are other editors you can search for as well (I am not recommending any
particular editor, just a feature of some program/text editors).  Some have
key stroke recording capabilities that allow you to create macros for fairly
complex data manipulation.  Anyway, the idea is to put the data into a format
that makes it easy to import.

I hope this idea is helpful.

Regards,

Jelle


Show quoteHide quote

I'd pre-process the file into the format with column delimiters. This has the
good side-effect of validating data as well. Any programming/scripting
language can do this very easily in one pass through the file.

Linchi

Show quoteHide quote

Snake (Sn***@discussions.microsoft.com) writes:
Specify a 0 for the target column for the "fields" you don't want to
import. For instance:

9.0
4
1 SQLCHAR 0 10 ""  1 col1 ""
2 SQLCHAR 0  5 ""  2 col2 ""
3 SQLCHAR 0  1 ""  0 ""   ""
4 SQLCHAR 0  0 "\r\n" 3 col3 ""

In this example the character in position 16 is skipped.

Profiler: Deadlock Graph - Associated ObjectID
Enterprise Manager displays nvarchar as <binary>
How to manage the transaction log
VS 2005 (BIDS) deploys the wrong data source connection string
BCP Error 08001, Nativeerror 2
Appending Data via BCP
No management Studio
SQL Server 2005 Error
>>Maintenance Plan - backup
Where is the Mgmt Studio ??