Conversion CSV to XLSX with VB.NET : Wrong delimiter

Question

I would like to convert an .CSV file to a .XLSX.

So far i have this code

    Public Sub CreateExcelFromCsvFile(ByVal strFolderPath As String)
       Dim oExcelFile As ObjectOpen Excel application object
       Try
          oExcelFile = GetObject(, "Excel.Application")
       Catch
          oExcelFile = CreateObject("Excel.Application")
       End Try

       oExcelFile.Visible = False
       oExcelFile.Workbooks.Open(strFolderPath)

       ' Turn off message box so that we do not get any messages
       oExcelFile.DisplayAlerts = False

       ' Save the file as XLS file
       Dim adr As String = ""
       For i = 0 To btn_chemin_source.Tag.ToString.Split("\").Count - 2
          adr += btn_chemin_source.Tag.ToString.Split("\")(i) & "\"
       Next
       With oExcelFile.ActiveWorkbook
          .SaveAs(Filename:=adr & "RD.xlsx", FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook, CreateBackup:=False, Local:=True)
          .Close(False)
       End With

       ' Close the workbook
       'oExcelFile.ActiveWorkbook.Close(SaveChanges:=False)

       ' Turn the messages back on
       oExcelFile.DisplayAlerts = True

       ' Quit from Excel
       oExcelFile.Quit()

       ' Kill the variable
       oExcelFile = Nothing

End Sub

My problem is that even i searched some clues on google to choose the delimiter in "Panel Control > region" and use local=True in SaveAs method, VB still keep using the comma as the delimiter instead of a semicolon, and so my Excel file is unusable.

Do you have any hints about how to pick a chosen delimiter while saving an CSV to xlsx ? :)

Thank you very much for your time!

I'm having trouble understanding. It looks like you open the file and then save it as Excel XML. I don't think Excel XML uses delimiters, it uses XML. Do you mean delimiters for the CSV input, or are you intending to save it as CSV instead of XML? — tgolisch
You are going from CSV to xlsx, right? So your issue is when you open the csv file? Then look into OpenText method: oExcelFile.Workbooks.OpenText(Filename:=strFolderPath, Local:=True, Semicolon:=True). Maybe don't need both Local & Semicolon but worth a go. See what happens — MacroMarc
Hello tgolisch, and sorry for my bad english; yes I'm trying to convert a CSV files with demicolon delimiter to a .xlsx, but the problem is that vb.net pick the coma as delimiter to fill in the cells instead of the semicolon whatever I tried with the SaveAs method. @MacroMarc Thank you very much it worked perfectly well ! After looking individually the parameters, it appears that semicolon doesn't do anything, but Local solve the problem with the OpenText Method ! — Gary F

Gary F Gary F · Accepted Answer · 2018-10-23T11:34:44

So I don't know if there has to be an answer to mark the subject as solved but in case here is the solution found my MacroMarc in the comments :

Instead of saving the csv file as an excel with the saveAs function, use OpenText method with Local:=True; and finally SaveAs an xlsx with no specific parameters :

Instead of :

oExcelFile.Workbooks.Open(strFolderPath)
oExcelFile.ActiveWorkbook.SaveAs(Filename:=path, FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook, CreateBackup:=False, Local:=True)

Use this :

oExcelFile.Workbooks.OpenText(Filename:=strFolderPath, Local:=True)
oExcelFile.ActiveWorkbook.SaveAs(Filename:=path, FileFormat:=Excel.XlFileFormat.xlOpenXMLWorkbook, CreateBackup:=False)

Thank you once more for your help ! :)

Conversion CSV to XLSX with VB.NET : Wrong delimiter

1 Answers