CSV to Excel

  • How to automate the process of converting csv file to excel file?

  • 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

  • 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

  • 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

  • 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

  • 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

  • below data is in CSV file....

    "test, test1","xyz name","some id"

  • 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"

  • 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

  • column 1 is population into different columns instead of one column?

  • 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