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\Uploa dFiles\EAN UPLOAD.csv '
WITH
(
FIELDTERMINATOR = ','
)
ALTER TABLE EANUPLOAD ADD FuturaBC int
GO
update EANUPLOAD
set EANUPLOAD.FuturaBC = FuturERS.dbo.ARTIKEL.ART_R EFNUMMER
FROM dbo.EANUPLOAD INNER JOIN FuturERS.dbo.ARTIKEL ON
dbo.EANUPLOAD.COLOUR = FuturERS.dbo.ARTIKEL.ART_E IGENSCHAFT
AND dbo.EANUPLOAD.[SIZE]= FuturERS.dbo.ARTIKEL.ART_E INHEIT
AND dbo.EANUPLOAD.SPITNO = FuturERS.dbo.ARTIKEL.ART_L FID_NUMMER
Go
Can any one help
Cheers
Si
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\Uploa
WITH
(
FIELDTERMINATOR = ','
)
ALTER TABLE EANUPLOAD ADD FuturaBC int
GO
update EANUPLOAD
set EANUPLOAD.FuturaBC = FuturERS.dbo.ARTIKEL.ART_R
FROM dbo.EANUPLOAD INNER JOIN FuturERS.dbo.ARTIKEL ON
dbo.EANUPLOAD.COLOUR = FuturERS.dbo.ARTIKEL.ART_E
AND dbo.EANUPLOAD.[SIZE]= FuturERS.dbo.ARTIKEL.ART_E
AND dbo.EANUPLOAD.SPITNO = FuturERS.dbo.ARTIKEL.ART_L
Go
Can any one help
Cheers
Si
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
output
1 NULL
2 Enter the file storage type of field EAN [char]:
Any Ideas??
Cheers
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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\Upload Files\EANC HECK.csv -Slocalhost -T -w'
Thanks for your help
Si
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\Upload
Thanks for your help
Si
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.