none
how to convert .csv to .xls

    Pregunta

  • 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);


    lunes, 15 de febrero de 2010 13:03

Respuestas

  • 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
    • Marcado como respuesta eryang lunes, 22 de febrero de 2010 9:36
    martes, 16 de febrero de 2010 9:13
  • 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
    • Marcado como respuesta eryang lunes, 22 de febrero de 2010 9:35
    martes, 16 de febrero de 2010 10:02

Todas las respuestas

  • 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
    lunes, 15 de febrero de 2010 13:19
  • 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.
    lunes, 15 de febrero de 2010 13:49
  • Are there any suggestion for me?
    martes, 16 de febrero de 2010 8:57
  • 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
    • Marcado como respuesta eryang lunes, 22 de febrero de 2010 9:36
    martes, 16 de febrero de 2010 9:13
  • 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
    • Marcado como respuesta eryang lunes, 22 de febrero de 2010 9:35
    martes, 16 de febrero de 2010 10:02
  • 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
    martes, 28 de diciembre de 2010 11:55
  • 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
    martes, 28 de diciembre de 2010 12:02
  • 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;
    }

    martes, 08 de mayo de 2012 10:22