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 

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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/

Answer 6

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.

Answer 7

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 :)

Answer 8

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

Answer 9

yes i realized that, changing regional settings changes the delimiter interpretation of the csv file. for eng is , and for romanian(my case) is ;

Answer 10

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.

Answer 11

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)