Changing filetype

[Tinnin]

I'm trying to change an excel file to csv but the methods I've used so far have corrupted the file.
I've tried renaming the file and using saveas from excel.

        Dim importsFileList As New ArrayList
        Dim sFile As String
        Dim extension As String
        Dim newExtension As String
        Dim file As IO.FileStream
        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook

        sFile = Dir$("C:\Users\filePath" & "\*.*", vbDirectory)
        Do Until sFile = vbNullString
            If sFile <> "." Then ' relative path meaning this directory
                If sFile <> ".." Then ' relative path meaning parent directory 
                    extension = IO.Path.GetExtension(sFile)
                    If extension = ".xls" Or extension = ".xlsx" Then
                        newExtension = IO.Path.ChangeExtension(sFile, ".csv")
                        oXL = CreateObject("Excel.Application")
                        oXL.Visible = False
                        oXL.UserControl = False
                        oWB = oXL.Workbooks.Open("C:\Users\filePath\" & sFile)
                        oWB.SaveAs("C:\Users\filePath\" & newExtension)
                        oWB.Close()
                        oXL.Quit()
                        oWB = Nothing
                        oXL = Nothing
                        'file = IO.File.Create("C:\Users\filePath\" & newExtension)
                        'file.Close()
                        'IO.File.Delete("C:\Users\filePath\" & sFile)
                        'My.Computer.FileSystem.RenameFile("C:\Users\filePath\" & sFile, newExtension)
                    End If
                End If
            End If
            sFile = Dir$()
        Loop

        sFile = Dir$("C:\Users\filePath" & "\*.*", vbDirectory)
        Do Until sFile = vbNullString
            If sFile <> "." Then ' relative path meaning this directory
                If sFile <> ".." Then ' relative path meaning parent directory 
                    extension = IO.Path.GetExtension(sFile)
                    If extension = ".csv" Then
                        importsFileList.Add(sFile)
                    End If
                End If
            End If
            sFile = Dir$()
        Loop

        For Each importedFile In importsFileList
            ImportFiles.Items.Add(importedFile)
        Next

Does anyone have any other suggestions?

[gusano79]

Changing the file extension doesn't do anything to the contents of the file. It looks like this is what's causing your "corruption"--you're asking Excel to load a CSV file, but its contents are still Excel format.

What you'll want to do is load the XLS or XLSX file as an Excel file (i.e., don't change the extension), then have Excel export it to CSV--have a look at SaveAs or SaveCopyAs.

[tinstaafl]

From my reading sFile has the .xls or .xlsx extension and newExtension is sFile with the .csv extension. Changing the format in the SaveAs statement works when I tried it:

oWB.SaveAs("C:\Users\filePath\" & newExtension, XlFileFormat.xlCSV)

[TnTinMN]

Tinnin,

Why are you going through all this work just to read in values from an Excel file? What does converting it to a CSV format gain you?

You could use the Excel interop to read the values directly.

Alternatively, you could use ADO.Net to read the files without any dependence on having Excel installed.

[Tinnin]

Great, thanks guys. I used your approach tinstaafl. I was missing the XlFileFormat.xlCSV argument.

TnTinMN: The files I get given to work with are in either .xls, .xlsx or .csv format. I import them into a database after some editing from the .csv format. So changing all files to this type before I begin processing them just seems like less work for me.