-
May 3rd, 2016, 11:56 AM
#1
Thread Starter
Lively Member
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
-
May 3rd, 2016, 03:09 PM
#2
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?
-
May 3rd, 2016, 04:05 PM
#3
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
-
May 4th, 2016, 02:28 PM
#4
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
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...
-
May 12th, 2016, 04:59 AM
#5
Frenzied Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|