CSV some records is not on the same row

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 2005 Forums

Transact-SQL (2005)

CSV some records is not on the same row

Author

Topic

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 04:21:43

Hello

I exported data in csv format using bcp, but some records is not on the same row

example:

I14161;BELKIN;KD-509;32;45
I14177;BELKIN;910-000133 BK
;2;38
I14179;BELKIN;910-000166 BK
;1;75
I14180;BELKIN;910-000325

bcp command:

DECLARE @cmd VARCHAR(2048)

SET @cmd = 'bcp "SELECT * FROM [Aug 2010].dbo.Joind_tb_tb" queryout C:\ab\Output.csv -c -t";" -Sco-01 -T'
EXEC
master..xp_cmdshell @cmd

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 04:26:29

Perhaps there are bad data in the information? Such as line feed character?

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 05:00:29

I tried to export that only one record wich was in wrong format and get same result
this record in the database is:
I14218 RAZER RZ02-00340100-R3M1 2 40

in csv it's format is:

I14218;RAZER;RZ02-00340100-R3M1
;2;40

does it contain enything ,wich can cause this wrong format?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 05:03:21

There is probably a character with ASCII value 10 (or 13) at the end of "RZ02-00340100-R3M1".

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 05:17:41

I did experiment, i added same record in database by hand and the old record has three dot(...), but when i placed cursor in that record there is not eny space. The problem is that database is very big. Is any solution? How to correct this records?
[url=http://j.imagehost.org/view/0978/ts]

[/url]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 05:35:16

SELECT * FROM [Aug 2010].dbo.Joind_tb_tb
WHERE [Description] LIKE '%' + CHAR(10) + '%'
OR [Description] LIKE '%' + CHAR(13) + '%'
OR [Description] LIKE '%' + CHAR(160) + '%'
OR [Description] LIKE '%' + CHAR(9) + '%'

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 07:14:22

When i tried this code with bcp command
SET @cmd = 'bcp "SELECT * FROM [Aug 2010].dbo.Joind_tb_tb WHERE [Description] LIKE '%' + CHAR(10) + '%' OR [Description] LIKE '%' + CHAR(13) + '%' OR [Description] LIKE '%' + CHAR(160) + '%' OR [Description] LIKE '%' + CHAR(9) + '%'" queryout C:\ab\Output.csv -c -t";" -Sco-01 -T'

it get error:

Msg 402, Level 16, State 1, Line 3
The data types varchar and varchar are incompatible in the modulo operator.[/b]

Seems that this code need to change ' ' with something.
and how can i split this long bcp command like this?

SET @cmd = 'bcp "SELECT * FROM [Aug 2010].dbo.Joind_tb_tb
WHERE [Description] LIKE '%' + CHAR(10) + '%' OR
[Description] LIKE '%' + CHAR(13) + '%' OR [Description]
LIKE '%' + CHAR(160) + '%' OR [Description] LIKE '%' + CHAR(9)
+ '%'" queryout C:\ab\Output.csv -c -t";" -Sco-01 -T'[/b]

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 07:27:15

But it seems that query gives only records wich has "char(10)". How can i delete this "char(10)" or "char(13) for records?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 07:50:15

UPDATE [Aug 2010].dbo.Joind_tb_tb
SET [Description] = REPLACE([Description] , CHAR(13), ' ')

UPDATE [Aug 2010].dbo.Joind_tb_tb
SET [Description] = REPLACE([Description] , CHAR(10), ' ')

UPDATE [Aug 2010].dbo.Joind_tb_tb
SET [Description] = REPLACE([Description] , CHAR(32) + CHAR(32), ' ')

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 08:00:18

Peso
SET @cmd = 'bcp "SELECT fields,Description=replace(replace(description,char(10),''),char(13),'') FROM [Aug 2010].dbo.Joind_tb_tb" queryout C:\ab\Output.csv -c -t";" -Sco-01 -T'

but replace function does only for char(10) and char(13), how can i get 4 or more parameter replace function (for char(160)
,char(90) and for char(32))?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 08:33:33

Nest more replace functions commands...

Your best bet is to craete a view like this

CREATE VIEW dbo.vwAug2010
AS

SELECT	Fields,
	REPLACE(REPLACE([Description], CHAR(10), ''), CHAR(13), '') AS Description
FROM	[Aug 2010].dbo.Joind_tb_tb

And then use the view in your BCP command

SET @cmd = 'bcp "SELECT [Fields], [Description] FROM dbo.vwAug2010" queryout C:\ab\Output.csv -c -t; -Sco-01 -T'

Remember, a BCP command MUST be on a single line.

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 09:03:54

Sorry for disturb, i tried this code and get error:

CREATE VIEW dbo.vwAug2010
AS

SELECT	[Item No_],[Manufacturer Code],
REPLACE(REPLACE([Description], CHAR(10), ''), CHAR(13), '') AS Description ,
Qty,[Unit Price]	
FROM [Aug 2010].dbo.Joind_tb_tb

DECLARE @cmd VARCHAR(2048) 
SET @cmd = 'bcp "SELECT [Item No_],[Manufacturer Code],[Description],[Qty],[Unit Price] FROM dbo.vwAug2010" queryout C:\ab\Output.csv -c -t";"  -Sco-01 -T'
EXEC
master..xp_cmdshell @cmd

error:
Msg 156, Level 15, State 1, Procedure vwAug2010, Line 9
Incorrect syntax near the keyword 'DECLARE'.

I am executing bcp from local server and database is another server. this code will for wor this yes?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 09:16:12

You must now how to create a view?

Execute this piece of code in your query window for your central server


GO
CREATE VIEW dbo.vwAug2010
AS

SELECT  [Item No_],
        [Manufacturer Code],
        REPLACE(REPLACE([Description], CHAR(10), ''), CHAR(13), '') AS Description,
        Qty,
        [Unit Price]	
FROM    [Aug 2010].dbo.Joind_tb_tb
GO

Now the second piece of code is to replace your current BCP assignment.

DECLARE @cmd VARCHAR(2048)
SET @cmd = 'bcp "SELECT [Item No_], [Manufacturer Code], [Description], [Qty], [Unit Price] FROM dbo.vwAug2010" queryout C:\ab\Output.csv -c -t";" -Sco-01 -T'

EXEC master..xp_cmdshell @cmd

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 09:49:13

I created dbo.vwAug2010 on another server and i tried to export data in csv using bcp from local server, but executed with errors:
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '.dbo.vwAug2010'.
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.

What can i do with single quote '' in bcp how can i change it

DECLARE @cmd VARCHAR(2048)

SET @cmd = 'bcp "SELECT [Item No_],[Manufacturer Code],REPLACE(REPLACE(REPLACE(REPLACE(Description,CHAR(10),''),CHAR(13),''),CHAR(9),''),CHAR(160),''),Qty,[Unit Price] FROM Joind_tb_tb WHERE [Item No_]='I12762'" queryout C:\ab\Output.csv -c -t";" -Sco-01 -T'
EXEC
master..xp_cmdshell @cmd
because when i tried excute this get this error:
Get error:

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near 'I12762'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 10:07:51

DECLARE @cmd VARCHAR(2048)

SET @cmd = 'bcp "SELECT [Item No_], [Manufacturer Code], [Description], Qty, [Unit Price] FROM [Aug 2010].dbo.vwAug2010 WHERE [Item No_] = ''I12762''" queryout C:\ab\Output.csv -c -t";" -Sco-01 -T'
EXEC master..xp_cmdshell @cmd

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-16 : 10:26:17

Ok, thank you peso
at least i used this code
DECLARE @cmd VARCHAR(2048)

SET @cmd = 'bcp "SELECT [Item No_],[Manufacturer Code],REPLACE(REPLACE(REPLACE(REPLACE(Description,CHAR(10),''''),CHAR(13),''''),CHAR(9),''''),CHAR(160),''''),Qty,[Unit Price] FROM [Aug 2010].dbo.Joind_tb_tb" queryout C:\ab\Output.csv -c -t ";" -Sco-01 -T'
EXEC
master..xp_cmdshell @cmd

and now evrything is ok.

Last question about this problem: is this very bad way taht i used 4 replace function? The records quantity is about 11438

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-16 : 10:52:50

The code is much longer than needed. Why create a view if you're not going to use it?

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-17 : 03:09:13

Using view my bcp command looks like this:


DECLARE @cmd VARCHAR(2048) 

SET @cmd = 'bcp "SELECT [Item No_],[Manufacturer Code], [Description],Qty,[Unit Price] FROM [Aug 2010].dbo.vwAug2010" queryout C:\ab\Output.csv -c -t ";" -Sco-01 -T'
EXEC
master..xp_cmdshell @cmd

This is better yes?

sn34
Starting Member

36 Posts

Posted - 2010-08-17 : 03:18:31

This command
SET @cmd = 'bcp "SELECT [Item No_],[Manufacturer Code], [Description],Qty,[Unit Price] FROM [Aug 2010].dbo.vwAug2010" queryout C:\ab\Output.csv -c -t ";" -Sco-01 -T'
And this coomand is same yes?
SET @cmd = 'bcp "SELECT * FROM [Elit Ho 6 Aug 2010].dbo.vwAug2010" queryout C:\ab\Output.csv -c -t ";" -Sco-it-erp-01 -T'

Now this is better, thank you

DECLARE @cmd VARCHAR(2048) 
queryout C:\ab\Output.csv -c -t ";" -Sco-it-erp-01 -T'
SET @cmd = 'bcp "SELECT * FROM [Elit Ho 6 Aug 2010].dbo.vwAug2010" queryout C:\ab\Output.csv -c -t ";" -Sco-it-erp-01 -T'
EXEC
master..xp_cmdshell @cmd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources