Link to home
Start Free TrialLog in
Avatar of brasso_42
brasso_42

asked on

TSQL To export to a CSV file

Hi

I need to import data from a CSV in to a table, alter the table, add some info and then export it back into a CSV again.  I can do all up to exporting it back into a CSV useing the Script below:-

Create Table EANUPLOAD
(EAN Varchar (20),
COLOUR varchar (8),
SIZE Varchar(8),
SPITNO Varchar (20))

go

Bulk insert BrassoLat.dbo.[EANUPLOAD]
From '\\BANKFUTURA\Futura\UploadFiles\EANUPLOAD.csv'
WITH
      (
         FIELDTERMINATOR = ','
      )

ALTER TABLE EANUPLOAD ADD FuturaBC int
GO

update EANUPLOAD

set EANUPLOAD.FuturaBC =  FuturERS.dbo.ARTIKEL.ART_REFNUMMER

FROM  dbo.EANUPLOAD INNER JOIN  FuturERS.dbo.ARTIKEL ON
dbo.EANUPLOAD.COLOUR = FuturERS.dbo.ARTIKEL.ART_EIGENSCHAFT
AND dbo.EANUPLOAD.[SIZE]= FuturERS.dbo.ARTIKEL.ART_EINHEIT
AND dbo.EANUPLOAD.SPITNO = FuturERS.dbo.ARTIKEL.ART_LFID_NUMMER

Go

Can any one help

Cheers

Si
Avatar of nmcdermaid
nmcdermaid

To export to CSV in T-SQL its probably easiest to use something like this:

EXEC master.dbo.xp_cmdshell 'BCP BrassoLat.dbo.EANUPLOAD OUT D:\EANUPLOAD.csv -Slocalhost -T'


which uses the xp_cmdshell (elevated rights required) stored procedure to run BCP.

BCP is a command line tool used tom import and export data.


The BCP command line may need a little work, your best bet is to open a command prompt and experiment with it and get it working, then substitute it into the xp_cmdshell statement.
Avatar of brasso_42

ASKER

Hi When I run this it comes up with

        output
1      NULL
2      Enter the file storage type of field EAN [char]:

Any Ideas??


Cheers
ASKER CERTIFIED SOLUTION
Avatar of nmcdermaid
nmcdermaid

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi

Geat That worked a treat .... well needed a bit of tweeking. Had to use FQDN and change the outupt type so it opened nicely in excel.  The script I used in the end was


EXEC master.dbo.xp_cmdshell 'BCP BrassoLat.dbo.EANUPLOAD OUT \\BANKFUTURA\Futura\UploadFiles\EANCHECK.csv -Slocalhost -T -w'


Thanks for your help


Si