• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 773
  • Last Modified:

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

0
dlavar
Asked:
dlavar
  • 3
  • 2
  • 2
2 Solutions
 
wdosanjosCommented:
What's the error?
0
 
dlavarAuthor Commented:
Argument 'SourceArray' cannot be converted to type 'String'.

Sorry
0
 
wdosanjosCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 OzSoftware 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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now