locked
convert csv to xlsx

    Question

  • Hi,

    i'm trying to convert a csv file to xlsx but using the folowing code:

     

    Dim xlApp As Object

            Dim newfilename As String

            xlApp = CreateObject("excel.application")

            xlApp.Visible = False

            xlApp.DisplayAlerts = False

            newFileName = "C:\test.xlsx"

            xlApp.Workbooks.Open("C:\rute.csv")

            xlApp.ActiveWorkbook.SaveAs(newFileName)

            xlApp.ActiveWorkbook.Close(0)

            xlApp.Quit()

            xlApp = Nothing

     

    id deos what it shal do but unfortunettly all the data is placed in the first column, (in the csv file the columns are delimited via " ; " char. opening the csv file using double clik opens it fine

    how to fix this? am i doing the convert corectly in the first place? Thanks 

    Monday, September 12, 2011 8:39 AM

Answers

  • Hi Egrimisu,

    Here is a workround to achieve your goal:

    1. Change the extention to "txt" by program or manually.

    2. Use the following code:

        Public Sub ConvertCSVToExcel(Fromcsv As String, Toxlsx As String)
            Dim Exl As New Excel.Application()
            Try
                Dim wb1 As Excel.Workbook = Exl.Workbooks.Open(Fromcsv, Format:=4)
                wb1.SaveAs(Toxlsx, FileFormat:=XlFileFormat.xlOpenXMLWorkbook)
                wb1.Close()
                Exl.Quit()
            Catch ex As Exception
                Exl.DisplayAlerts = False
                Exl.Quit()
                Console.WriteLine(ex.Message)
            End Try
        End Sub
    

    This is because:

    File containing data sets separated by commas, where each new line represents a new database row, and each database row has one or more fields separated by a comma; can be organized into cells by a spreadsheet program or inserted into a database.

    When the extension is "csv", it always use commas as the delimiter.

    Format

    Optional Object. If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.

    Value

    Delimiter

    1

    Tabs

    2

    Commas

    3

    Spaces

    4

    Semicolons

    5

    Nothing

    6

    Custom character (see the Delimiter argument)


     I hope this will be helpful.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 14, 2011 3:39 AM
    Moderator
  • This task can actually be accomplished without Excel:

            Dim ConnectionString As String
    
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=C:\Users\...\Documents\My Database\Excel\ImportedTextFiles.xlsx;Extended Properties=""Excel 12.0 Xml"""
    
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            ExcelConnection.Open()
    
            Dim SQLString As String = "SELECT * INTO [SheetName] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[SemiColonDelimited.txt]"
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLString, ExcelConnection)
            ExcelCommand.ExecuteNonQuery()
    
            ExcelConnection.Close()
    


    You will need a schema.ini file to identify the column delimiter as a semi-colon:

    [SemiColonDelimited.txt]
    ColNameHeader=True
    CharacterSet=ANSI
    Format=Delimited(;)
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 16, 2011 8:28 PM

All replies

  • See below, it might help

    http://www.access-programmers.co.uk/forums/showthread.php?t=143526

    Public Sub ConvertCSVtoXL(strCSVPath As String)
    
    Dim appExcel As Excel.Application
    Dim Pathfile As String
    
    Dim objExcel As Excel.Application
    Dim objXsheet As Excel.Worksheet
    
    Set objExcel = New Excel.Application
    objExcel.Workbooks.Open FileName:=strCSVPath
    
    Pathfile = Left(strCSVPath, Len(strCSVPath) - 3) & "xls"
    With appExcel
    .ActiveWorkbook.SaveAs FileName:=Pathfile, FileFormat:=xlNormal
    End With
    
    
    MsgBox "File '" & strCSVPath & "' has been converted to excel under the same " & _
    "filename with an XLS extension"
    
    End Sub 
    



    Please mark those posts as answer which answers your question. Faraz
    Monday, September 12, 2011 8:56 AM
  • Hi and thanks,

    unfortunettly for me the above method produce the same result as my script, simply convert the extension form csv to xls, i believe i need to tell excell that the csv file i open is endeed csv and has de column delimited with ;

    still searching the web but haven't find nothing yet.

    Monday, September 12, 2011 10:58 AM
  • Hi Egrimisu,

    Welcome to the MSDN Forum.

    The saveas method should work.

    Please take a look at the following code:

        Public Sub ConvertCSVToExcel(From As String, Toxlsx As String)
            Dim Exl As New Excel.Application()
            Try
                Dim wb1 As Excel.Workbook = Exl.Workbooks.Open(From)
                wb1.SaveAs(Toxlsx, FileFormat:=XlFileFormat.xlOpenXMLWorkbook)
                wb1.Close()
                Exl.Quit()
            Catch ex As Exception
                Exl.DisplayAlerts = False
                Exl.Quit()
                Console.WriteLine(ex.Message)
            End Try
        End Sub
    

    It converts a to 132bytes CSV file to a 9.2KB excel file.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 13, 2011 9:48 AM
    Moderator
  • Hi and thanks Mike,

    This was the line that i was lacking:

    SaveAs(Toxlsx, FileFormat:=XlFileFormat.xlOpenXMLWorkbook)

    now it save the file in xlsx format but the csv file was delimited with ; and in the xlsx file all the data is in the firs column. How shall i tell excel that the delimiter is ";" and not the default character witch i believe in my region is ","


    Thanks.

    Tuesday, September 13, 2011 11:33 AM
  • Hi,

    you have to use the optional parameters of the open method. Take a look at the docu: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.workbooks.open(v=office.11).aspx especially at the format argument.


    Hannes

    If you have got questions about this, just ask.

    In a perfect world,
    users would never enter data in the wrong form,
    files they choose to open would always exist
    and code would never have bugs.

    C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/
    Tuesday, September 13, 2011 11:56 AM
  • Hi Egrimisu,

    Here is a workround to achieve your goal:

    1. Change the extention to "txt" by program or manually.

    2. Use the following code:

        Public Sub ConvertCSVToExcel(Fromcsv As String, Toxlsx As String)
            Dim Exl As New Excel.Application()
            Try
                Dim wb1 As Excel.Workbook = Exl.Workbooks.Open(Fromcsv, Format:=4)
                wb1.SaveAs(Toxlsx, FileFormat:=XlFileFormat.xlOpenXMLWorkbook)
                wb1.Close()
                Exl.Quit()
            Catch ex As Exception
                Exl.DisplayAlerts = False
                Exl.Quit()
                Console.WriteLine(ex.Message)
            End Try
        End Sub
    

    This is because:

    File containing data sets separated by commas, where each new line represents a new database row, and each database row has one or more fields separated by a comma; can be organized into cells by a spreadsheet program or inserted into a database.

    When the extension is "csv", it always use commas as the delimiter.

    Format

    Optional Object. If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.

    Value

    Delimiter

    1

    Tabs

    2

    Commas

    3

    Spaces

    4

    Semicolons

    5

    Nothing

    6

    Custom character (see the Delimiter argument)


     I hope this will be helpful.

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 14, 2011 3:39 AM
    Moderator
  • hy mike thanks for the tip "When the extension is "csv", it always use commas as the delimiter."

     

    yesterday i have tried with format:=4 but didn't worked because the filename had csv extension, after changing it to text it worked gourgesly.

    Thanks Mike, there's real help in this forum, i will stick to it :)

    Wednesday, September 14, 2011 5:57 AM
  • And to Add something to Mike's message. The used delimiter is depending from your system setting.

    I don't know what it is for Faraz and Mike, but Hannes and I are living in a culture (not the same mother tongue) where the decimal separator is a comma. Therefore the CSV delimiter is a semicolon

     


    Success
    Cor
    Wednesday, September 14, 2011 6:23 AM
  • yes i realized that, changing regional settings changes the delimiter interpretation of the csv file. for eng is , and for romanian(my case) is ;
    Wednesday, September 14, 2011 6:25 AM
  • hy mike thanks for the tip "When the extension is "csv", it always use commas as the delimiter."

     

    yesterday i have tried with format:=4 but didn't worked because the filename had csv extension, after changing it to text it worked gourgesly.

    Thanks Mike, there's real help in this forum, i will stick to it :)


    Hi Egrimisu,

    You are welcome.

    I have noticed that " If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used." So I changed the extension to "txt", I believe that when the file extension is "csv", the method ignored this parameter.

    Hi Cor,

    Thank you for your helpful message. I realized this right now.

    Thank you,

    Best regards,


    Mike Feng [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, September 14, 2011 6:39 AM
    Moderator
  • This task can actually be accomplished without Excel:

            Dim ConnectionString As String
    
            ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                "Data Source=C:\Users\...\Documents\My Database\Excel\ImportedTextFiles.xlsx;Extended Properties=""Excel 12.0 Xml"""
    
            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)
            ExcelConnection.Open()
    
            Dim SQLString As String = "SELECT * INTO [SheetName] FROM [Text;DATABASE=C:\Documents and Settings\...\My Documents\My Database\Text].[SemiColonDelimited.txt]"
    
            Dim ExcelCommand As New System.Data.OleDb.OleDbCommand(SQLString, ExcelConnection)
            ExcelCommand.ExecuteNonQuery()
    
            ExcelConnection.Close()
    


    You will need a schema.ini file to identify the column delimiter as a semi-colon:

    [SemiColonDelimited.txt]
    ColNameHeader=True
    CharacterSet=ANSI
    Format=Delimited(;)
    

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 16, 2011 8:28 PM