BULK INSERT PROBLEM

Question

BULK INSERT PROBLEM

JP-BDPL

SSC Rookie

Points: 36
More actions
April 12, 2006 at 6:49 am

#96934

Hey ALL,
I am a new bee, and my question is related to BULK INSERT, I am having txt file with | as a delimiter…My file look like this..

‘B’ |12454 | ’CAINE’ | ’TOOL’ | 1989 |’AK’
‘A’|45896|’FURM’|’TOOL’|2001|’FG’

Now when I use BULK INSERT with this text file it runs very well, but I am getting ‘ in my sql table, so is there any way to remove ‘ in bulk insert.

I am using SQL Server 2000.

Please, please help me guys….

Thanks in advance

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Haven't used bulk insert but you could update the columns in the sql table using the 'REPLACE' function:

DECLARE @string VarChar(100)
SET @string = '‘B’ |12454 | ’CAINE’ | ’TOOL’ | 1989 |’AK’
‘A’|45896|’FURM’|’TOOL’|2001|’FG’'
SET @string = REPLACE(REPLACE(@string, '’', ''), '‘', '')
PRINT @string

**ASCII stupid question, get a stupid ANSI !!!**

Hans Lindgren SSChampion Points: 10496 More actions · Answer 2

Interesting problem!

DTS can do it, if you set the text delimiters to the character '

But it does not seem to be any native way inside BULK INSERT or BCP to do this. So maybe your BULK INSERT with an additional replace for every column could solve your problem. (Remember that MS Excel is your friend in applying similar operators to tables with a lot of columns...

Maybe someone else knows something more?

Regards,

Hans

David Burrows SSC Guru Points: 65144 More actions · Answer 3

Try using this fomat file

8.0

12

1 SQLCHAR 0 1 "‘" 0 x ""

2 SQLCHAR 0 10 "’" 1 col1 ""

3 SQLCHAR 0 10 "|" 0 x ""

4 SQLCHAR 0 4 "|" 2 col2 ""

5 SQLCHAR 0 10 "’" 0 x ""

6 SQLCHAR 0 10 "’" 3 col3 ""

7 SQLCHAR 0 10 "’" 0 x ""

8 SQLCHAR 0 10 "’" 4 col4 ""

9 SQLCHAR 0 10 "|" 0 x ""

10 SQLCHAR 0 4 "|" 5 col5 ""

11 SQLCHAR 0 10 "’" 0 x ""

12 SQLCHAR 0 10 "’\r\n" 6 col6 ""

I used the quote marks you posted not sure if they are formatted differently or the standard '

p.s. you will have to change the column widths appropriately

Far away is close at hand in the images of elsewhere.
Anon.

JP-BDPL SSC Rookie Points: 36 More actions · Answer 4

Thanks guys

Well..Thanks for your reply, but there are more than 20 txt files which should be copied in to main table every day, and it should be automatically, so this is some what complex solution...I heard that Service Pack in MS SQL 2000 or 2005 has solved this problem...Do any one know this ..If so please give your thoughts.

Thanks a lot for your concern.

Hans Lindgren SSChampion Points: 10496 More actions · Answer 5

I have never heard of this as a problem. SO I do not think this is fixed in a Service Pack.

Maybe it could be called a feature not thought of

Either your could build DTS packages (since they deal with this issue) that imports your file.

Or you could build (or download) some parser program that parses the text files and removes you apostrophes (just before or after the pipe character).

Or your could change the Export behaviour so your text files does not contain these artifacts...