stephenlecomptejr
asked on
Need help with VB.NET solution - transitioning from access vba to vb.net.
I'm reading a csv file with File System Object and have to determine out of each column what's the greatest length of a strings value! So obviously I would need to go through the entire text file and store each row into memory - then cycle through. I do have the code to read the csv file using the file system object already - what I do not know how to do is to take the information and put it into an arraylist per each column? 1st column is qty, 2nd is description and 3rd is manufacturer.
What makes it confusing is as I do research it would seem I need to create a class preferably?
Why the access vba to vb.net statement above?
I used to do this in access vba by taking each and row and putting it into a table - then running a sql statement that sorted on the Len(column) in descending order. And the DAO recordset was easy to do to pull info - but I'm still learning vb.net and object oriented programming.
objStream = FSO.OpenTextFile(sFileName , 1, False, 0)
Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
Loop
objStream.Close()
objStream = Nothing
Any help is much appreciated.
What makes it confusing is as I do research it would seem I need to create a class preferably?
Why the access vba to vb.net statement above?
I used to do this in access vba by taking each and row and putting it into a table - then running a sql statement that sorted on the Len(column) in descending order. And the DAO recordset was easy to do to pull info - but I'm still learning vb.net and object oriented programming.
objStream = FSO.OpenTextFile(sFileName
Do While Not objStream.AtEndOfStream
strLine = objStream.ReadLine
Loop
objStream.Close()
objStream = Nothing
Any help is much appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The code DEFINITELY uses the TextFieldParser, parsing the file line by line delimited by a comma (,). If you wish to change the delimter, you can do this in the initialisation statement (on line 7 in the code above).
With regard to a limit to the rows, the code will read all lines in the file and if a line contains more that two fields, then it will add the contents to a datarow and then to the table. I do not have the file structure of the you are trying to read, but with hindsight I would change line 12 above from:
With regard to a limit to the rows, the code will read all lines in the file and if a line contains more that two fields, then it will add the contents to a datarow and then to the table. I do not have the file structure of the you are trying to read, but with hindsight I would change line 12 above from:
xRow.ItemArray = Line.ToArray
toxRow.ItemArray = {Line(0).ToString, Line(1).ToString, Line(2).ToString}
to handle the situation where the line contains more than three fields.
ASKER
I actually had 5 columns and was able to do it with this code:
<code>
Dim xTable As New DataTable
With xTable
.Columns.Add(New DataColumn With {.ColumnName = "Qty"})
.Columns.Add(New DataColumn With {.ColumnName = "Des"})
.Columns.Add(New DataColumn With {.ColumnName = "Des1"})
.Columns.Add(New DataColumn With {.ColumnName = "Des2"})
.Columns.Add(New DataColumn With {.ColumnName = "Des3"})
End With
Using xReader As New FileIO.TextFieldParser(sFi leName) With {.Delimiters = {","}}
While Not xReader.EndOfData
Dim Line = xReader.ReadFields()
If Line.Count < 3 OrElse String.IsNullOrEmpty(Line( 0).ToStrin g) Then Continue While
Dim xRow As DataRow = xTable.NewRow()
xRow.ItemArray = Line.ToArray
xTable.Rows.Add(xRow)
End While
End Using
'then rewrite the code below
'to reiterate through the data table
'and save as a new file.
For Each row As DataRow In xTable.Rows
strNewLine = row.Item("Qty") & vbTab & row.Item("Des") & vbTab & row.Item("Des1") & vbTab & row.Item("Des2") & vbTab & row.Item("Des3")
FSONew = CreateObject("Scripting.Fi leSystemOb ject")
objStreamNew = FSO.OpenTextFile(sFileName Adj, 8, False, 0)
'need to change strNewLine to grab each column of data table.
objStreamNew.WriteLine(str NewLine)
objStreamNew.Close()
objStreamNew = Nothing
FSONew = Nothing
Next row
</code>
I have 3 questions:
1.
The only piece of code I don't understand is this...
If Line.Count < 3 OrElse String.IsNullOrEmpty(Line( 0).ToStrin g) Then Continue While
Why would you continue if line.count < 3?
2.
Do I also need to change my above code to do as you have xRow.ItemArray = {Line(0).ToString, Line(1).ToString, Line(2).ToString}? Because it works well as it is?
3. How do I get code to actually show up as code? I thought adding <code> as I have it above would work?
<code>
Dim xTable As New DataTable
With xTable
.Columns.Add(New DataColumn With {.ColumnName = "Qty"})
.Columns.Add(New DataColumn With {.ColumnName = "Des"})
.Columns.Add(New DataColumn With {.ColumnName = "Des1"})
.Columns.Add(New DataColumn With {.ColumnName = "Des2"})
.Columns.Add(New DataColumn With {.ColumnName = "Des3"})
End With
Using xReader As New FileIO.TextFieldParser(sFi
While Not xReader.EndOfData
Dim Line = xReader.ReadFields()
If Line.Count < 3 OrElse String.IsNullOrEmpty(Line(
Dim xRow As DataRow = xTable.NewRow()
xRow.ItemArray = Line.ToArray
xTable.Rows.Add(xRow)
End While
End Using
'then rewrite the code below
'to reiterate through the data table
'and save as a new file.
For Each row As DataRow In xTable.Rows
strNewLine = row.Item("Qty") & vbTab & row.Item("Des") & vbTab & row.Item("Des1") & vbTab & row.Item("Des2") & vbTab & row.Item("Des3")
FSONew = CreateObject("Scripting.Fi
objStreamNew = FSO.OpenTextFile(sFileName
'need to change strNewLine to grab each column of data table.
objStreamNew.WriteLine(str
objStreamNew.Close()
objStreamNew = Nothing
FSONew = Nothing
Next row
</code>
I have 3 questions:
1.
The only piece of code I don't understand is this...
If Line.Count < 3 OrElse String.IsNullOrEmpty(Line(
Why would you continue if line.count < 3?
2.
Do I also need to change my above code to do as you have xRow.ItemArray = {Line(0).ToString, Line(1).ToString, Line(2).ToString}? Because it works well as it is?
3. How do I get code to actually show up as code? I thought adding <code> as I have it above would work?
1. I put thatthere because you had 3 fields of data initially, so ifthey were less then trying to assign them to the datarow would cause an error
2. You do not need to change it since I incorporated the test for line fields.
3. To get the code formating, add with using the [ ] not < > i.e square brackets not angled ones
2. You do not need to change it since I incorporated the test for line fields.
3. To get the code formating, add with using the [ ] not < > i.e square brackets not angled ones
ASKER
Thank you for help. Please help me in the future - I'm sure to have plenty more!
ASKER
The code above no where uses the textfieldparser as you suggest right?