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

Snake (Snake (AT) discussions (DOT) 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.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

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


"Snake" wrote:

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

"Snake" wrote:

Snake (Snake (AT) discussions (DOT) 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.




--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx