Bulk Insert and XML format files

Hi all,
I have a flat file that isn't as flat as I'd like it to be :)
The rule for the data insert into the flat file is that the column widths
are set (say to 8 characters for example) but may be longer if the data is
longer than 8 characters (no data loss is paramount). The columns are then
single space separated.
This is legacy code that is not changeable.
If the data for the 8 character column is then only 5 characters long, it
pads the remaining 3 characters with spaces, and adds the single space colum
n
termination character after it.
Could someone tell me if this is configurable through a format file? (xml)
The data types I have seen are either fixed width (CharFixed) or character
terminated (CharTerm) - the above solution requires a mix of the two...
Thanks in advance (and for reading this far)Perhaps start by bulk inserting the file into a staging table with one
varchar(8000) column, and then write some pain the butt T-SQL that uses the
case and string functions to parse and re-insert it into the production
table.
insert into ProductionTable
select
case mid(col,9,1)
when ' ' then mid(col,1,8)
else mid(col,9,12)
end as Column1,
. . .
from
StagingTable
"sbparsons" <sbparsons@.discussions.microsoft.com> wrote in message
news:12E07EC6-2B0C-4893-976D-DE9CA373FAE5@.microsoft.com...
> Hi all,
> I have a flat file that isn't as flat as I'd like it to be :)
> The rule for the data insert into the flat file is that the column widths
> are set (say to 8 characters for example) but may be longer if the data is
> longer than 8 characters (no data loss is paramount). The columns are then
> single space separated.
> This is legacy code that is not changeable.
> If the data for the 8 character column is then only 5 characters long, it
> pads the remaining 3 characters with spaces, and adds the single space
> column
> termination character after it.
> Could someone tell me if this is configurable through a format file? (xml)
> The data types I have seen are either fixed width (CharFixed) or character
> terminated (CharTerm) - the above solution requires a mix of the two...
> Thanks in advance (and for reading this far)|||Thanks JT
That has crossed my mind, as has using a coding language and implementing a
search-and-replace for multiple spaces (replacing with a single space) and
re-saving the file prior to import, but speed is of the essence here.
The joys of inheriting legacy code hand-offs!
"JT" wrote:

> Perhaps start by bulk inserting the file into a staging table with one
> varchar(8000) column, and then write some pain the butt T-SQL that uses th
e
> case and string functions to parse and re-insert it into the production
> table.
> insert into ProductionTable
> select
> case mid(col,9,1)
> when ' ' then mid(col,1,8)
> else mid(col,9,12)
> end as Column1,
> . . .
> from
> StagingTable
>
> "sbparsons" <sbparsons@.discussions.microsoft.com> wrote in message
> news:12E07EC6-2B0C-4893-976D-DE9CA373FAE5@.microsoft.com...
>
>