Need help exporting data to csv

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

General SQL Server Forums

New to SQL Server Programming

Need help exporting data to csv

Author

Topic

xmrkite
Starting Member

2 Posts

Posted - 2012-08-31 : 17:54:52

Hello, I need help exporting data to a csv file.

I am using sql server 2000 for an older program.

I found 2 scripts and modified them a little to get what I need done, but it's not producing the right results.

Script 1 is a bat file:

osql -Sfileserver\db -E -isp_customer_email.sql -ooutput.csv -s"," -n -h-1 -b -w 600

Script 2 is an sql file:

USE main_db
SET NOCOUNT ON
SELECT '''' + CAST(c.email AS VARCHAR(50)) + '''' As EMAIL ,
CAST('''' + c.billName + '''' AS VARCHAR) AS NAME,
CAST('''' + c.billAddress1 + '''' AS VARCHAR) As ADDRESS,
CAST('''' + c.billAddress2 + '''' AS VARCHAR) As ADDRESS2,
CAST(c.billCity AS VARCHAR) AS CITY,
CAST(c.billState AS VARCHAR) AS STATE,
CAST(c.billzip AS VARCHAR) AS ZIP FROM customers c
WHERE Exists(
SELECT 1 FROM (SELECT max(customerId) as ID, email AS EMAIL FROM customers
WHERE email IS NOT NULL AND CHARINDEX('@',email) > 0
GROUP BY email)temp WHERE c.customerId = temp.ID)

It's giving a huge file that opens fine in excel, but is not a standard csv file. It seems to have a ton of code when I open it up via my linux console in nano.

Furthermore, there are tons of spaces at the end of the data for each colum, so for example, in the email field, after the email address, there will be like 100 spaces, then the comma, then the next field.

I need this file to be a simple csv and in utf-8 and skip all those spaces if possible.

Thanks for any help

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2012-09-01 : 10:06:00

One option is to create a Powershell script - and use the Export-CSV cmdlet. The Export-CSV cmdlet creates a CSV file of the script output. The script needs to pipe the results to the Export-CSV cmdlet – with a filename parameter.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-02 : 22:53:28

how about a BCP utility!!

bcp Northwind.dbo.TableName out C:\folder\filename.csv /c -T /r\n

--------------------------
Joins are what RDBMS's do for a living

xmrkite
Starting Member

2 Posts

Posted - 2012-09-04 : 15:01:43

BCP Could work. I was hoping for a simple tweak to the script I am already using as this database has many tables and I'm not really comfortable running stuff on it that I don't fully understand.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-09-04 : 15:12:51

[code]to be on the safe side, create your own table and test on it before going any further.
Anyways, this command should make any alteration to your database!
bcp Northwind.dbo.TableName out C:\folder\filename.csv /c -T /r\n[/code]

--------------------------
Joins are what RDBMS's do for a living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources