Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #556 > unrolled thread

Embedded <CRLF> in text file

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Embedded <CRLF> in text file "Kurt Dicus" <kdicus@dicus.com> - 2011-07-21 00:17 -0700
    Re: Embedded <CRLF> in text file Erland Sommarskog <esquel@sommarskog.se> - 2011-07-21 11:05 +0200
      Re: Embedded <CRLF> in text file "Kurt Dicus" <kdicus@dicus.com> - 2011-07-21 02:14 -0700

#556 — Embedded <CRLF> in text file

It's been years and years since I used a newsgroup.  Wow.  This brings back 
some memories!

Okay, my issue.

I have an output from a system that generates the following (snipped of 
relevant info).  The file is ANSI encoded.

<t> = tab
<c> = CRLF

This would be a perfect file!

ID<t>STATUS<t>DESCRIPTION<t>ACTIVE<c>
1<t>active<t>Test description short<t>true<c>
2<t>under construction<t>Test description 2<t>false<c>

What the file actually looks like.
ID<t>STATUS<t>DESCRIPTION<t>ACTIVE<c>
1<t>active<t>Test<c>   -- it is only the DESCRIPTION field that has the 
premature break
description<c>
long string<t>true<c>
2<t>under construction<t>Test description 2<t>false<c>

There are a limited set of values for the STATUS.  Let's just assume that 
active and under construction are the only two possible values.

Before you ask, no, I don't have any way to change the output from the 
source system -- unfortunately.

What do you think?  Should I just write a parsing script outside of SQL 
before the import that builds a correctly formatted file?

The logic might be something like...

There are four (and only four) different kinds of lines (excluding the field 
names)
(1) number<t>string<t>string<t>string<c>
(2) number<t>string<t>string<c>
(3) string<c>
(4) string<t>string<c>

The logic could be something like:
If (1), write line to output file.
If (2), write line to output file.  Look at next line.  If it is like (3) 
append to previous line and look at next line assuming previous line isn't 
complete yet.  If it is like (4), append to previous line and assume a 
completed line.
If (3), make sure previous line looks like (2) or (3).  If so, append to 
previous line.  If previous line is not like (2) or (3), flag an error.
If (4), make sure previous line is like (2) or (3).  If so, append to 
previous line.  If previous line is not like (2) or (3), flag an error.

Or, I could do the same basic thing within SQL.  What else?  I need the best 
idea.

Thank you to anyone (and everyone) who tries to help.

Kurt

[toc] | [next] | [standalone]

#559

Kurt Dicus (kdicus@dicus.com) writes:
> This would be a perfect file!
> 
> ID<t>STATUS<t>DESCRIPTION<t>ACTIVE<c>
> 1<t>active<t>Test description short<t>true<c>
> 2<t>under construction<t>Test description 2<t>false<c>
> 
> What the file actually looks like.
> ID<t>STATUS<t>DESCRIPTION<t>ACTIVE<c>
> 1<t>active<t>Test<c>   -- it is only the DESCRIPTION field that has the 
> premature break
> description<c>
> long string<t>true<c>
> 2<t>under construction<t>Test description 2<t>false<c>
 
Have you tried to bulk-load this misfit with BCP or BULK INSERT? I belive
that should work. They look for one field at a time, so when they look for 
the third field, they are looking for tab characters to terminate that 
field. The CRLF will be consumed as data.
-- 
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

[toc] | [prev] | [next] | [standalone]

#561

Unreal...  It worked.  How simple was that!!!

Outstanding!

Thank you so much.  I've been reading through tons of messages tonight.  It 
is CLEAR that you are a GOD on here.

Kurt



"Erland Sommarskog"  wrote in message 
news:Xns9F2970D5C97ECYazorman@127.0.0.1...

Kurt Dicus (kdicus@dicus.com) writes:
> This would be a perfect file!
>
> ID<t>STATUS<t>DESCRIPTION<t>ACTIVE<c>
> 1<t>active<t>Test description short<t>true<c>
> 2<t>under construction<t>Test description 2<t>false<c>
>
> What the file actually looks like.
> ID<t>STATUS<t>DESCRIPTION<t>ACTIVE<c>
> 1<t>active<t>Test<c>   -- it is only the DESCRIPTION field that has the
> premature break
> description<c>
> long string<t>true<c>
> 2<t>under construction<t>Test description 2<t>false<c>

Have you tried to bulk-load this misfit with BCP or BULK INSERT? I belive
that should work. They look for one field at a time, so when they look for
the third field, they are looking for tab characters to terminate that
field. The CRLF will be consumed as data.
-- 
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 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web