Output table to csv - 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

SQL Server Development (2000)

Output table to csv

Author

Topic

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-16 : 09:04:42

i have a table that when i use bcp or dts it misses part of the records in the table if i do all 65026 rows.

If i just do it for a range of the missing records it works.

Any ideas how to export table to comma delimited in sql analyzer
instead

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-16 : 09:25:07

I am using BCP for 100K+ records, and that works excellent.
There must be something else you are overseeing.

Peter Larsson
Helsingborg, Sweden

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-16 : 11:01:16

The command i have is
bcp tracey.dbo.ET_CHRG out "g:\ET_CHRGD.txt" -c -t "," -r "\n" -Sfinancesqldev -T -e g:\errorlog.txt

Is there something i missing.
It worked a treat for ever

It has data in table of
001
008 records are 93 here but only does 5
016

When i export all 008 works great

Any other suggestions.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-16 : 15:47:05

i put the file onto http://www.stretchunlimited.com/tracey.zip if you like to see it
bak of one table called ET_CHRG
database called TRACEY

in here there are 90 rows
SELECT * FROM ET_CHRG
WHERE COL1 LIKE '"008i%"'

But when doing
BCP

ONLY does these
"008I0271",etc" to
"008I0355"
missing "008I0605"...for example

When view the text file created you do not see all records of 008I

bcp command
bcp TRACEY.DBO.ET_CHRGB out "c:\ET_CHRG.txt" -c -t "," -r "\n" -S servername -T -e c:\errorlog.txt

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-17 : 10:37:36

I put rowid on the table and opened in access and all records are exported...

I assuming some carriage return is missing on the records perhaps

Working on command to display this...

DECLARE @position int, @string char(100), @ASCII_CONCAT INT,
@ASCII_CHAR CHAR,
@ASCII_CONCAT_DISPLAY NVARCHAR(100),
@ASCII_CHAR_DISPLAY NVARCHAR(100)
-- Initialize the variables.
SET @position = 1
SET @string = '"00101001"'
SET @ASCII_CONCAT = ''
SET @ASCII_CONCAT_DISPLAY = ''
SET @ASCII_CHAR_DISPLAY = ''

WHILE @position <= LEN(@STRING)
BEGIN
SET @ASCII_CONCAT = ASCII(SUBSTRING(@string, @position, 1))
SET @ASCII_CHAR = CHAR(ASCII(SUBSTRING(@string, @position, 1)))
--PRINT @ASCII_CHAR
--PRINT @ASCII_CONCAT
SET @position = @position + 1
Set @ASCII_CONCAT_DISPLAY = @ASCII_CONCAT_DISPLAY + @ASCII_CONCAT
SET @ASCII_CHAR_DISPLAY = @ASCII_CHAR + ';' + @ASCII_CHAR_DISPLAY

--SET @ASCII_CHAR_DISPLAY = @ASCII_CHAR + ';' + @ASCII_CHAR_DISPLAY

--PRINT @ASCII_CONCAT_DISPLAY
--PRINT @ASCII_CHAR_DISPLAY
END
PRINT @ASCII_CONCAT_DISPLAY
PRINT @ASCII_CHAR_DISPLAY
SET NOCOUNT OFF
GO

Can't get the ascii for
Set @ASCII_CONCAT_DISPLAY = @ASCII_CONCAT_DISPLAY + @ASCII_CONCAT
to work it add the values together

so im getting 455 instead of 34;48 etc

I think thats how i going to determine if there is something not correct at the end of the columns ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-17 : 14:09:54

Have a look at this topic! http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79083
It filters out everything you don't want there.

Peter Larsson
Helsingborg, Sweden

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-03-18 : 07:05:00

Thanks i got it all working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources