none
how to convert .csv to .xls

    Question

  • hi,
    how can i convert a csv file to xls file.

    i have tried it like this but it doesnt work.

    Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();

    Excel.Workbook excelWorkbook = this.excelApp.Workbooks.Open("c:\\a.csv", 0,
                    false, 6, password, "", false, Excel.XlPlatform.xlWindows, ";", false,
                    false, 0, true, false, false);

    excelWorkbook.SaveAs("c:\\a.xls", Excel.XlFileFormat.xlWorkbookNormal,
                                 System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                 Excel.XlSaveAsAccessMode.xlNoChange,
                                 System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                 System.Reflection.Missing.Value);


    Monday, February 15, 2010 1:03 PM

Answers

  • yes. This is exactly what I'm trying to say you. You must specify also other parameters for csv conversion. The 'problem' is in this phase. Then, saving as excel is easy.
    Try to work with Excel, registering your actions in a macro. The code of the macro say you what to do in the vb.net code.

    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    • Marked as answer by eryang Monday, February 22, 2010 9:36 AM
    Tuesday, February 16, 2010 9:13 AM
  • Well. I've just worked for you in a little bit of free time.
    Don't use 'Open csv file', but 'Import data from txt file' (my Excel, at work, is in Italian, so I don't know the English menu caption).

    This is the VBA code of the macro I've recorded (excuse me for 'registered' word: my English is not so fluent :o))
    You must simply change the objects:

        ' You must have a workbook opened, and an active sheet
        ActiveWorkbook.Worksheets.Add
        ' you must get a QueryTable object
        Dim qt As QueryTable = _ 
                       ActiveSheet.QueryTables.Add( _
                       Connection:= "TEXT;C:\contacts.csv", _
                       Destination:=Range("A1"))
        With qt
            .Name = "contacts"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = xlWindows
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
            1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .Refresh BackgroundQuery:=False
        End With
    Then, you can use SaveAs without problems (I think and hope :o))
    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    • Marked as answer by eryang Monday, February 22, 2010 9:35 AM
    Tuesday, February 16, 2010 10:02 AM

All replies

  • IMHO, the SaveAs method works, but needs csv-specifics. I don't remeber, now, wher you must set them, but if you record a macro to import a csv file, you'll be requested to specify token-separator, string delimitator, etc.
    Then, you've a xls file.
    The same, when you save it back as xls. So, I'm pretty sure the SaveAs method has these specifics among its parameters.
    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    Monday, February 15, 2010 1:19 PM
  • my csv file delimiter charecter is ;.

    and when i convert like this it writes all columns values in the first(A) column and seperate the values by ; character.
    Monday, February 15, 2010 1:49 PM
  • Are there any suggestion for me?
    Tuesday, February 16, 2010 8:57 AM
  • yes. This is exactly what I'm trying to say you. You must specify also other parameters for csv conversion. The 'problem' is in this phase. Then, saving as excel is easy.
    Try to work with Excel, registering your actions in a macro. The code of the macro say you what to do in the vb.net code.

    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    • Marked as answer by eryang Monday, February 22, 2010 9:36 AM
    Tuesday, February 16, 2010 9:13 AM
  • Well. I've just worked for you in a little bit of free time.
    Don't use 'Open csv file', but 'Import data from txt file' (my Excel, at work, is in Italian, so I don't know the English menu caption).

    This is the VBA code of the macro I've recorded (excuse me for 'registered' word: my English is not so fluent :o))
    You must simply change the objects:

        ' You must have a workbook opened, and an active sheet
        ActiveWorkbook.Worksheets.Add
        ' you must get a QueryTable object
        Dim qt As QueryTable = _ 
                       ActiveSheet.QueryTables.Add( _
                       Connection:= "TEXT;C:\contacts.csv", _
                       Destination:=Range("A1"))
        With qt
            .Name = "contacts"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = xlWindows
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierNone
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = True
            .TextFileCommaDelimiter = False
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
            1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
            .Refresh BackgroundQuery:=False
        End With
    Then, you can use SaveAs without problems (I think and hope :o))
    please, mark this as answer if it is THE answer
    ----------------
    Diego Cattaruzza
    Microsoft MVP - Visual Basic: Development
    blog: http://community.visual-basic.it/Diego
    web site: http://www.visual-basic.it
    • Marked as answer by eryang Monday, February 22, 2010 9:35 AM
    Tuesday, February 16, 2010 10:02 AM
  • Interesting point. I agree i sometimes wish for simplier things. It seems that somethings are more complex or not so simple to use anymore. They try to make them work better and do more things but sometimes they make things worse

    Cara Beriklan di Internet Handphone STMIK AMIKOM menuju Research University
    Tuesday, December 28, 2010 11:55 AM
  • Interesting point. I agree i sometimes wish for simplier things. It seems that somethings are more complex or not so simple to use anymore. They try to make them work better and do more things but sometimes they make things worse

    Cara Beriklan di Internet Handphone STMIK AMIKOM menuju Research University
    Tuesday, December 28, 2010 12:02 PM
  • Hi,

    you can easily convert CSV to XLS using this C# Excel library.

    Here is a sample C# code:

    // Load specified file from XLS, XLSX, ODS or CSV format.
    switch (inputFileFormat)
    {
        case "xls":
            excelFile.LoadXls(inputFileName);
            break;
        case "xlsx":
            excelFile.LoadXlsx(inputFileName, XlsxOptions.None);
            break;
        case "ods":
            excelFile.LoadOds(inputFileName, OdsOptions.None);
            break;
        case "csv":
            excelFile.LoadCsv(inputFileName, CsvType.CommaDelimited);
            break;
    }
     
    // Save to specified file in XLS, XLSX, ODS, CSV or HTML format.
    switch (outputFileFormat)
    {
        case "xls":
            excelFile.SaveXls(outputFileName);
            break;
        case "xlsx":
            excelFile.SaveXlsx(outputFileName);
            break;
        case "ods":
            excelFile.SaveOds(outputFileName);
            break;
        case "csv":
            excelFile.SaveCsv(outputFileName, CsvType.CommaDelimited);
            break;
        case "html":
            excelFile.SaveHtml(outputFileName, null, true);
            break;
    }

    Tuesday, May 08, 2012 10:22 AM