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

Kurt Dicus (kdicus (AT) dicus (DOT) com) writes:
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 (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

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 (AT) 127 (DOT) 0.0.1...

Kurt Dicus (kdicus (AT) dicus (DOT) com) writes:
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 (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