January 22, 2013 at 4:25 pm
How to automate the process of converting csv file to excel file?
January 22, 2013 at 9:05 pm
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; Theyll drag you down to their level and beat you with experience
January 23, 2013 at 7:07 am
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 - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
January 24, 2013 at 3:13 pm
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
January 24, 2013 at 3:15 pm
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
January 25, 2013 at 12:50 am
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"?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 25, 2013 at 11:04 am
below data is in CSV file....
"test, test1","xyz name","some id"
January 25, 2013 at 11:07 am
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"
January 25, 2013 at 1:24 pm
So you get a csv file with a comma delimiter and a double quote text qualifier. What's the issue with SSIS?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 25, 2013 at 1:32 pm
column 1 is population into different columns instead of one column?
January 26, 2013 at 10:46 pm
Did you specify the double quotes as the text qualifier? if not it'll read the comma's inside the quotes...
example:
"test, test1,test2","xyz name","some id"
will read:
Col1 Col2 Col3 Col4 Col5
"test test1 test2" "xyz name" some id
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply