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

SSIS and Import/Export (2005)

xlstocsv

Author

Topic

h2sut
Starting Member

40 Posts

Posted - 2008-12-16 : 15:38:32

Hi does anyone have a script that i can run in a ssis package that will convert .xls to a .csv. I have to do this manually is there a script that will open the .xls file and save as a .csv in my package when it runs

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2008-12-16 : 16:29:43

How about an Excel Macro to do that?

Sub saveToCSV()
ActiveWorkbook.SaveAs Filename:="C:\fromXLS.csv", FileFormat:=xlCSV, CreateBackup:=False
End Sub

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-17 : 11:19:51

Here is a stored procedure which takes a file name (minus file extension) and imports to a table, exports to csv file then drops the table.
Work for both xls and xlsx but extension in proc needs adjusted.
SSIS is probably a better way but this was more fun.
Possible Improvements:
check filepath has trailing backslash.
check file does not have extension.
save to temp table rather than permanent table.

CREATE Procedure ExcelToCSV @file_name varchar(100), @file_path varchar (200)
AS
declare @sql varchar (8000), @csvFile_name varchar(100), @excelFileName varchar(100)
declare @SQLRead varchar(1000)
set @csvFile_name= @file_name +'.csv'
set @excelFileName= @file_name +'.xlsx'

set @SQLRead = 'SELECT * INTO excelTemp FROM OPENDATASOURCE
(''Microsoft.ACE.OLEDB.12.0'',
''Data Source='+ @file_path + @excelFileName
+ '; Extended Properties=''''Excel 12.0'''''')...[Sheet1$]'

exec(@SQLRead)

Select * from excelTemp

set @sql='exec master..xp_cmdshell ''bcp xxxx.dbo.TableName out ' + @file_path + @csvFile_name + ' /Uusername /Ppassword /S. -c -t, -T '''

exec(@sql)
Drop Table excelTemp

-------------------
Usage:

EXECUTE [dbo].[ExcelToCSV] 'Book1', 'C:\temp\'

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-18 : 05:46:13

I have modified procedure to convert all excel files (xls and xlsx) in a folder. Just pass in foldername

Usage: EXECUTE [dbo].[ExcelToCSV] 'C:\temp\test'

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[ExcelToCSV] @folder_path varchar (200)

--Saves all Excel files in a directory as CSV files
AS
declare @sql varchar (8000), @csvFile_name varchar(100), @excelFileName varchar(100)
declare @SQLRead varchar(1000), @sqlDir varchar(100)

if Right(@folder_path,1) <> '\' set @folder_path=@folder_path + '\'
--get folder contents
set @sqlDir = 'DIR /b ' + @folder_path
create table #Dir(RowID int identity,Thisfile varchar(2000))
insert into #Dir
EXEC XP_CMDSHELL @sqlDir
select * from #Dir
where right(Thisfile,4)='.xls' or right(Thisfile,5)='.xlsx'

--Cursorless loop each file
declare @tabcount int,@file varchar(100)
set @tabcount = @@rowcount

--count down all the rowids to loop all files in folder
WHILE @tabcount > 0
BEGIN
SELECT @file = Thisfile
FROM #Dir
WHERE Rowid = @tabcount

--Create CSV file
set @excelFileName= @file
If right(@file,4)='.xls'
SET @file = Left(@file,len(@file)-4)
If right(@file,5)='.xlsx'
SET @file = Left(@file,len(@file)-5)

set @csvFile_name= @file +'.csv'

set @SQLRead = 'SELECT * INTO excelTemp FROM OPENDATASOURCE
(''Microsoft.ACE.OLEDB.12.0'',
''Data Source='+ @folder_path + @excelFileName
+ '; Extended Properties=''''Excel 12.0;HDR=NO'''''')...[Sheet1$]'
print(@SQLRead)
exec(@SQLRead)
declare @Rows int
set @Rows= (Select count(*) from excelTemp)
If @Rows >0
Begin
set @csvFile_name =Replace(@csvFile_name,' ','')
set @sql='exec master..xp_cmdshell ''bcp dbo.excelTemp out "'+ @folder_path + @csvFile_name + '" -Uusername -Ppassword -S. -c -t, -T '''
print(@sql)
exec(@sql)
End
Truncate Table excelTemp
Drop Table excelTemp

SET @tabcount = @tabcount - 1

END
Truncate Table #Dir
drop table #Dir

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources