Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

CSV to Excel Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 4:25 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:04 PM
Points: 99, Visits: 892
How to automate the process of converting csv file to excel file?
Post #1410294
Posted Tuesday, January 22, 2013 9:05 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 24, 2014 5:46 AM
Points: 4,031, Visits: 7,172
Crude, but this will work, just loop through a directory (if you are able to execute xp_cmdshell, if not I'm sure there's a powershell script lingering around)
DECLARE
@Filepath varchar(75),
@file varchar(25),
@Cmd varchar(150)
SET @file = 'test.csv'
SET @filepath = 'C:\'
SET @cmd = 'ren "' + @filepath + @file + '" "' + REPLACE(@file, 'csv', 'xls') + '"'
PRINT @cmd

EXEC xp_cmdshell @cmd, NO_OUTPUT

Why would you want to do this via TSQL?


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1410325
Posted Wednesday, January 23, 2013 7:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 24, 2014 9:12 AM
Points: 285, Visits: 504
MyDoggieJessie (1/22/2013)
Crude, but this will work, just loop through a directory (if you are able to execute xp_cmdshell, if not I'm sure there's a powershell script lingering around)
DECLARE
@Filepath varchar(75),
@file varchar(25),
@Cmd varchar(150)
SET @file = 'test.csv'
SET @filepath = 'C:\'
SET @cmd = 'ren "' + @filepath + @file + '" "' + REPLACE(@file, 'csv', 'xls') + '"'
PRINT @cmd

EXEC xp_cmdshell @cmd, NO_OUTPUT

Why would you want to do this via TSQL?


This will only rename the files and not convert the CSV to XLS binary file.
The question still remains why would you do it in SQL?
I think it would work with SSIS. To read the CSV files in and then spit out as Excel.
There are converters out in the net, just have to search for it.


---------------------------------------------------------------
Mike Hahn - Future MCM 2025
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa
Post #1410554
Posted Thursday, January 24, 2013 3:13 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:04 PM
Points: 99, Visits: 892
I couldnt load the data from CSV file to SQL Table in correct format, some reason your script is not converting to .xls file?

Thanks
Post #1411363
Posted Thursday, January 24, 2013 3:15 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:04 PM
Points: 99, Visits: 892
I tried with SSIS it didnt work. The data are separated with double and single codes, while loading from CSV table its not getting the data in right format.However, when u load you convert to .xls and load to sql server table it works perfect.

Thanks
Post #1411365
Posted Friday, January 25, 2013 12:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
Admingod (1/24/2013)
I tried with SSIS it didnt work. The data are separated with double and single codes, while loading from CSV table its not getting the data in right format.However, when u load you convert to .xls and load to sql server table it works perfect.

Thanks


SSIS should work. Can you explain a bit more about "data are separated with double and single codes"?




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1411504
Posted Friday, January 25, 2013 11:04 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:04 PM
Points: 99, Visits: 892
below data is in CSV file....

"test, test1","xyz name","some id"
Post #1411854
Posted Friday, January 25, 2013 11:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:04 PM
Points: 99, Visits: 892
more sample data....


below data is in CSV file....

"test, test1","xyz name","some id"
"test, test1,test2","xyz name","some id"
"test","xyz name","some id"
"test, test1,test2,test3","xyz name","some id"
Post #1411856
Posted Friday, January 25, 2013 1:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:28 AM
Points: 13,641, Visits: 11,517
So you get a csv file with a comma delimiter and a double quote text qualifier. What's the issue with SSIS?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1411919
Posted Friday, January 25, 2013 1:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, December 5, 2014 5:04 PM
Points: 99, Visits: 892
column 1 is population into different columns instead of one column?
Post #1411924
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse