How to bcp in fixed position data while skipping certain positions

How to bcp in fixed position data while skipping certain positions

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

date: Thu, 25 Jun 2009 10:01:01 -0700 author: Snake

RE: How to bcp in fixed position data while skipping certain positions

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 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

date: Fri, 26 Jun 2009 06:19:01 -0700 author: Jelle

RE: How to bcp in fixed position data while skipping certain positions

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:

> 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

date: Fri, 26 Jun 2009 08:25:01 -0700 author: Linchi Shea

Re: How to bcp in fixed position data while skipping certain positions

Snake (Snake@discussions.microsoft.com) writes:
> 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. 
 
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@sommarskog.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/prodinfo/previousversions/books.mspx

date: Fri, 26 Jun 2009 14:56:59 -0700 author: Erland Sommarskog

Re: How to bcp in fixed position data while skipping certain positions

Snake (Snake@discussions.microsoft.com) writes:
> 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. 
 
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@sommarskog.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/prodinfo/previousversions/books.mspx

date: Thu, 25 Jun 2009 16:50:04 -0500 author: Erland Sommarskog