Save xls as csv in vb.net

I'm trying to save an xls file to a csv file and I'm getting the following error..
What does this mean?  And how can I fix it?
The code below is what i got from another user, so I'm new at this.
Sub ExcelToCSV()

        Dim File As String = System.IO.Path.GetFileName(FileUpload1.PostedFile.FileName)
        Dim strPath As String = "\\iddbsrvr01\wwwroot\OnlineCPS\"
        Dim strFileName As String
        strFileName = strPath + "Brands.csv"

        'Setup Connection String
        Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & strFileName & ";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes"""
        Dim objXConn As New OleDb.OleDbConnection(connectionString)

        ' use a SQL Select command to retrieve the data from the Excel Spreadsheet
        Dim sql As String = "SELECT * FROM [Sheet1$]"

        'Create and load DataTable.
        Dim dt As New DataTable()

        Using cn As New OleDb.OleDbConnection(connectionString)
            Dim da As New OleDb.OleDbDataAdapter(sql, cn)
            da.Fill(dt)
        End Using

        'Write out
        Dim writer As New IO.StreamWriter("\\iddbsrvr01\wwwroot\OnlineCPS\Brands.csv")

        For Each row As DataRow In dt.Rows
            writer.WriteLine(Join(row.ItemArray, ","))  'or ","
        Next

        writer.Close()
        writer.Dispose()


    End Sub

Open in new window

dlavarAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
The code you posted read from Brand.csv and writes to Brand.csv. That does not seem right.  Please refer to lines #6, #10, and #26.
0
 
wdosanjosCommented:
What's the error?
0
 
dlavarAuthor Commented:
Argument 'SourceArray' cannot be converted to type 'String'.

Sorry
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
Miguel OzSoftware EngineerCommented:
Replace line 29:
writer.WriteLine(Join(row.ItemArray, ","))
with
Dim str(row.ItemArray.Length) As String
row.ItemArray.CopyTo(str, 0)
writer.WriteLine(Join(str, ","))

Note: row.ItemArray is an array of objects not a string array. you need to convert to string array for join to work.
0
 
dlavarAuthor Commented:
I'm getting the following error..on the line that says  row.ItemArray.CopyTo(str, 0).


At least one element in the source array could not be cast down to the destination array type.  
0
 
Miguel OzConnect With a Mentor Software EngineerCommented:
Try:
Dim obj() As Object = row.ItemArray;
Dim str(obj.Length-1) As String
obj.CopyTo(str, 0)
writer.WriteLine(Join(str, ","))

0
 
dlavarAuthor Commented:
I've done some tweaking and got it working.  Thanks for your help
Sub Test() 'saves xls to csv file
        Dim file As String = "Y:\OnlineCPS\copy.xls"

        Dim WorksheetHasHeader As Boolean = True

        'Setup Connection String
        Dim connectionString As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & _
            file & "';Extended Properties='Excel 8.0;HDR=" & _
            IIf(WorksheetHasHeader = True, "Yes", "No") & ";IMEX=1'"


        'Column 1 in Excel is F1, column 2 is F2, etc...
        Dim SQL As String = "SELECT * FROM [Sheet1$]"

        'Create and load DataTable.
        Dim dt As New DataTable()
        Using cn As New OleDb.OleDbConnection(connectionString)
            Dim da As New OleDb.OleDbDataAdapter(SQL, cn)
            da.Fill(dt)
        End Using


        'Write out
        Dim writer As New IO.StreamWriter("Y:\OnlineCPS\output.csv")

        For Each row As DataRow In dt.Rows
            writer.WriteLine(Join(row.ItemArray, ""))  'or ","
        Next

        writer.Close()
        writer.Dispose()
    End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.