Solved

Need help with VB.NET solution - transitioning from access vba to vb.net.

Posted on 2012-04-12
6
329 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:stephenlecomptejr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 17

Accepted Solution

by:
nepaluz earned 500 total points
ID: 37840366
Using a textfieldparser with a check for the length of each line would solve the issue
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 = "Man"})
End With
Using xReader As New FileIO.TextFieldParser("PathToFile") With {.Delimiters = {","}}
    While Not xReader.EndOfData
        Dim Line = xReader.ReadFields()
        If Line.Count < 3 OrElse String.IsNullOrEmpty(Line(0).ToString) Then Continue While
        Dim xRow As DataRow = xTable.NewRow()
        xRow.ItemArray = Line.ToArray
        xTable.Rows.Add(xRow)
    End While
End Using

Open in new window

I have also taken the liberty toplace the contents in a datatable!
0
 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 37840891
Would there be a limit to rows putting it in a datatable?
The code above no where uses the textfieldparser as you suggest right?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37841693
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:
xRow.ItemArray = Line.ToArray

Open in new window

to
xRow.ItemArray = {Line(0).ToString, Line(1).ToString, Line(2).ToString}

Open in new window

to handle the situation where the line contains more than three fields.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:stephenlecomptejr
ID: 37853250
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(sFileName) With {.Delimiters = {","}}
            While Not xReader.EndOfData
                Dim Line = xReader.ReadFields()
                If Line.Count < 3 OrElse String.IsNullOrEmpty(Line(0).ToString) 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.FileSystemObject")
            objStreamNew = FSO.OpenTextFile(sFileNameAdj, 8, False, 0)
            'need to change strNewLine to grab each column of data table.
            objStreamNew.WriteLine(strNewLine)
            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).ToString) 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?
0
 
LVL 17

Expert Comment

by:nepaluz
ID: 37853301
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
0
 
LVL 1

Author Closing Comment

by:stephenlecomptejr
ID: 37853356
Thank you for help.  Please help me in the future - I'm sure to have plenty more!
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We had a requirement to extract data from a SharePoint 2010 Customer List into a CSV file and then place the CSV file into a directory on the network so that the file could be consumed by an AS400 system. I will share in Part 1 how to Extract the Da…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question