BCP Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

BCP Help

Author

Topic

Db_Newbie
Starting Member

3 Posts

Posted - 2005-08-04 : 13:47:16

I tried searching this forum for answers but couldn't find them. I am fairly new to databases and SQL.

I am working on a project to load text files into a Sybase server. I used the bcp function in a batch file to load the files in the database. The text files are in Unix format. After each row there are 2 blank rows. Is there a function within bcp to remove these blank rows?

When I select * from the table that I bcp into, this is what I get,

Name Address Telephone
John Doe 555 Main St 555-5555 /n /n
Jane Doe 555 Main st 555-5555 /n /n

The /n is actually a character (a rectangular box). How can I get rid of them? By the way, for now the files can't be fixed to exclude the blank rows.

Thanks in advance for your help.

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 14:39:05

Hi Db_Newbie, welcome to SQL Team!

I'm basing my answer on MS SQL Server because I don't know anything about how Sybase might now have become nowadays.

You might be able to set up a "formatting file" that specifies how BCP should interpret the data columns from the Text File, including the pair of line breaks, which might allow a clean import.

Failing that import into a Staging Table, Clean up the imported data, and then "Insert/Update" into the Live tables

Kristen

Db_Newbie
Starting Member

3 Posts

Posted - 2005-08-04 : 14:49:42

Thanks for the reply.

Can you give me a suggestion on cleaning up these empty rows in SQL? I can't seem to find any character functions that would delete them.

Thanks again for your help.

Kristen
Test

22859 Posts

Posted - 2005-08-04 : 14:54:46

SELECT MyColumn = LEFT(MyColumn, LEN(MyColumn)-1)
FROM MyTable
WHERE MyColumn LIKE '%' + CHAR(13)

You might find that the actual characters on the end of the column are:

char(13)
char(10)
or
char(13)+char(10) [In which case use LEN(MyColumn)-2

]

If that's not the case you shold be able to "see" the character using
SELECT ASCII(RIGHT(MyColumn, 1))

Kristen

Kristen

Db_Newbie
Starting Member

3 Posts

Posted - 2005-08-04 : 15:43:40

It worked.

Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources