Results 1 to 5 of 5

Thread: Need help converting code for use with CSV to Excel file

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jun 2015
    Posts
    117

    Need help converting code for use with CSV to Excel file

    I need to be able to use the following code with an Excel file. Currently it is set-up to read from a csv file. It reads from the first 2 columns inside the csv file (which is actually just a converted Excel file). I tried changing the openFileDialog1.Filter to open Excel files, but it does not open up Excel files. I would have done it myself but I am not sure if I need to use the StreamReader function or not. Any help guidance would be appreciated.

    Code:
    Dim openFileDialog1 As New OpenFileDialog()
    
            openFileDialog1.Title = "Load Parts from List"
            'openFileDialog1.Filter = "CSV files (*.csv)|*.csv|All files (*.*)|*.*"
            openFileDialog1.Filter = "Excel Worksheets|*.xlsx"
            openFileDialog1.FilterIndex = 1
            openFileDialog1.RestoreDirectory = True
            If Directory.Exists(strLastPath) Then
                openFileDialog1.InitialDirectory = strLastPath
            Else
                openFileDialog1.InitialDirectory = "C:\"
            End If
    
            If openFileDialog1.ShowDialog() = DialogResult.OK Then
                strOutputFolder = openFileDialog1.FileName
                tmp = InStrRev(strOutputFolder, "\")
                strOutputFolder = Left(strOutputFolder, tmp)
            Else
                'user pressed 'cancel', exit the subroutine
                strOutputFolder = 0
                Exit Sub
    
            End If
    
            Dim sr As StreamReader = New StreamReader(openFileDialog1.FileName)
            Dim line As String
            Dim partslist(0) As String
    
            Try
                line = sr.ReadLine()
                While Not line Is Nothing
                    Dim delim As Char() = {","c}
                    Dim strings As String() = line.Split(delim)
                    partname = (strings(0))
                    revision = (strings(1))
                    If revision = 0 Then getRev()
                    ReDim Preserve partslist(i)
                    partslist(i) = "@DB/" & partname & "/" & revision
                    i = i + 1
                    line = sr.ReadLine()
                End While
            Catch e As Exception
                MsgBox("error reading csv file " & vbCrLf & vbCrLf & e.GetBaseException.ToString)
                Exit Sub
            Finally
                sr.Close()
            End Try

  2. #2
    PowerPoster
    Join Date
    Oct 2010
    Posts
    2,141

    Re: Need help converting code for use with CSV to Excel file

    What makes you believe that that code has anything to do with reading a formatted Excel workbook file?

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Need help converting code for use with CSV to Excel file

    a csv file, whether created by excel or not, is just a text file
    mostly they open in excel by default, but can easily be opened in notepad or other text file editors

    i do not use .net, so can only guess, that to dimension a dynamic array, you should not have a boundry
    Code:
    Dim partslist() As String
    parsing data from csv files can be difficult if the data can contain commas, as it will split in additional parts

    at the end of the shown code, you do not do anything with the data in the array, so not sure how you can tell if the code is working, unless you get error, but you have not specified

    in vba i would just use file i/o to read the csv file, but not sure about code for that in .net

    but it does not open up Excel files.
    if you want to open the file in excel, to view that would be very different
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  4. #4
    Don't Panic! Ecniv's Avatar
    Join Date
    Nov 2000
    Location
    Amsterdam...
    Posts
    5,343

    Re: Need help converting code for use with CSV to Excel file

    From looking at your code, you've changed the file dialog to show excel files, but the file format of an excel file is not anywhere near the same as a csv (character separated variable) file.

    so the code to read the first two columns will fail.

    If it's a text file (just outputted from excel) the code shouldn't have a problem, so I think you're looking at an excel file. Which in vb.net you'll need to open an excel object and open the file through that. No idea there though - better if a mod moved it to the vb.net forums

    BOFH Now, BOFH Past, Information on duplicates

    Feeling like a fly on the inside of a closed window (Thunk!)
    If I post a lot, it is because I am bored at work! ;D Or stuck...
    * Anything I post can be only my opinion. Advice etc is up to you to persue...

  5. #5
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: Need help converting code for use with CSV to Excel file

    CSV - comma separated values - a comma appears between entries in this file

    you could use tabs or any other separator if you push the format your way

    simplest capture from csv to excel is ensure commas are tabs and change the file extension to xlt it will be read directly into excel

    there are too many ways to skin this particular cat and the solution depends on local machine and operator preference.

    here to help

    code if needed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width