bulk insert problem

29 Dec 2005 2:22 PM

Kevin

hi guys, I have a data file that I like to import to a table.
the column delimiter is @, row delimiter is \n, line feed.
But here is the problem, we have a column called "comment",
basically user can enter any characters to it, so if "comment" column
contains
@ character, then sql server think it's an extra column.   However, that's
why before @ character we put \ , it's \@ in comment column now.   BUT How
come it doesn't do escape for @ character?   Is it possible to create an
exception for delimiter in case if Sql server sees \@ , then it'll ignore it
and will not treat it as delimiter?

thanks

29 Dec 2005 2:57 PM

Andrew J. Kelly

Why not do a global replace of @ with some other sequence of characters that
is not already found in the file? Import the file and then do another
replace in the column to put the characters back to @.

--
Andrew J. Kelly SQL MVP

Show quote

"Kevin" <Ke***@discussions.microsoft.com> wrote in message
news:3D940ABE-6D54-48DA-9164-0D22787DB201@microsoft.com...
> hi guys, I have a data file that I like to import to a table.
> the column delimiter is @, row delimiter is \n, line feed.
> But here is the problem, we have a column called "comment",
> basically user can enter any characters to it, so if "comment" column
> contains
> @ character, then sql server think it's an extra column.   However,
> that's
> why before @ character we put \ , it's \@ in comment column now.   BUT
> How
> come it doesn't do escape for @ character?   Is it possible to create an
> exception for delimiter in case if Sql server sees \@ , then it'll ignore
> it
> and will not treat it as delimiter?
>
> thanks
>

29 Dec 2005 3:18 PM

Kevin

in this case, it's possible to write perl script to do global replacement in
windows?
This way I can make it automate process.

Show quote

"Andrew J. Kelly" wrote:

> Why not do a global replace of @ with some other sequence of characters that
> is not already found in the file? Import the file and then do another
> replace in the column to put the characters back to @.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Kevin" <Ke***@discussions.microsoft.com> wrote in message
> news:3D940ABE-6D54-48DA-9164-0D22787DB201@microsoft.com...
> > hi guys, I have a data file that I like to import to a table.
> > the column delimiter is @, row delimiter is \n, line feed.
> > But here is the problem, we have a column called "comment",
> > basically user can enter any characters to it, so if "comment" column
> > contains
> > @ character, then sql server think it's an extra column.   However,
> > that's
> > why before @ character we put \ , it's \@ in comment column now.   BUT
> > How
> > come it doesn't do escape for @ character?   Is it possible to create an
> > exception for delimiter in case if Sql server sees \@ , then it'll ignore
> > it
> > and will not treat it as delimiter?
> >
> > thanks
> >
>
>
>

29 Dec 2005 3:59 PM

Andrew J. Kelly

I thought this might have been a one time import but I don't see why you
can't use perl (or some other utility) to automate this.

--
Andrew J. Kelly SQL MVP

Show quote

"Kevin" <Ke***@discussions.microsoft.com> wrote in message
news:6D17EF5E-0150-4F38-80BB-91D6F086CA70@microsoft.com...
> in this case, it's possible to write perl script to do global replacement
> in
> windows?
> This way I can make it automate process.
>
>
>
> "Andrew J. Kelly" wrote:
>
>> Why not do a global replace of @ with some other sequence of characters
>> that
>> is not already found in the file? Import the file and then do another
>> replace in the column to put the characters back to @.
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>>
>> "Kevin" <Ke***@discussions.microsoft.com> wrote in message
>> news:3D940ABE-6D54-48DA-9164-0D22787DB201@microsoft.com...
>> > hi guys, I have a data file that I like to import to a table.
>> > the column delimiter is @, row delimiter is \n, line feed.
>> > But here is the problem, we have a column called "comment",
>> > basically user can enter any characters to it, so if "comment" column
>> > contains
>> > @ character, then sql server think it's an extra column.   However,
>> > that's
>> > why before @ character we put \ , it's \@ in comment column now.   BUT
>> > How
>> > come it doesn't do escape for @ character?   Is it possible to create
>> > an
>> > exception for delimiter in case if Sql server sees \@ , then it'll
>> > ignore
>> > it
>> > and will not treat it as delimiter?
>> >
>> > thanks
>> >
>>
>>
>>