locked
CSV To array

    Question

  •  

    I have an application that reads Verizon Wireless/ATT/Nextel Bills. I have all of the plan names stored in 3 Slect Case's. I would like to put all of the plan names and my short desctiptions into a CSV file. Now I would like to know how do I load a CSV into a Array. The CSV should look something like VENDER, SHORTDESCRIPTION, PLAN NAME. I just would like one file to update!

     

    I am just trying to make it easier for me for updating the program, I have plan vender plan name changes all of the time. Am I on the correct track on moving this out to a external file? here is my case statement that I use currently (i have one for each Vender):

     

    Dim PlansName As String

    Select Case Plans

    Case "BroadbandAccess $59.99 0607 $59.99 monthly access charge"

    PlansName = "High Speed EVDO"

    Case "BroadbandAccess for Business $59.99 1007 $59.99 monthly access charge"

    PlansName = "High Speed EVDO"

    Case "CORporate BroadbandAccess Unlimited America's Choice II $59.99"

    PlansName = "High Speed EVDO"

    Case "BroadbandAccess WIth Voice $59.99 0507 $59.99 monthly access charge"

    PlansName = "High Speed EVDO"

    Case "BroadbandAccess for B2b 5gb/$0.25 $59.99 0308 $59.99 monthly access charge"

    PlansName = "High Speed EVDO"

    Case "BroadbandAccess PRomo Unlimited $59.99 2y 0805 $59.99 monthly access charge"

    PlansName = "High Speed EVDO"

    Case "CORporate BroadbandAccess Unlimited America's Choice II $59.99 1yr 0805 $59.99 monthly access charge"

    PlansName = "High Speed EVDO"

    Case "Local for STate of CA FLAt Rate $0.065 Access $0 1y 1005 $.06 Per MInute"

    PlansName = "VER-Flat Rate - .065"

    Case "Local for STate of CA FLAt Rate $0.065 Access $0 1y 1005 $.06 Per MInute After ALLowance"

    PlansName = "VER-Flat Rate - .065"

    Case "Local for STate of CA FLAt Rate $0.065 Access $0 1y 1005 07/24 - 07/26 $.06 Per MInute After ALLowance"

    PlansName = "VER-Flat Rate - .065"

    Case "Local State of CA 100 Anytime Unl N&W $18.99 .00 Long Dist 1y 1005 $18.99 monthly access charge 100 monthly general allowance minutes $.45 per minute after allowance"

    PlansName = "NO Share 100 Minutes"

    Case "Local State of CA 200 Anytime Unl N&W $22.99 .00 Long Dist 1y 1005 $22.99 monthly access charge 200 monthly general allowance minutes $.45 per minute after allowance"

    PlansName = "NO Share 200 Minutes"

    Case "Local Share ST of CA 300 Anytime Unl N&W $28.99 .00 Long Dist 1y 1005 $28.99 monthly access charge 300 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 300 Minutes"

    Case "Local State of CA 400 Anytime Unl N&W&In Call $30.99 .00 Long Dist 1y 1005 $30.99 monthly access charge 400 monthly general allowance minutes $.40 per minute after allowance"

    PlansName = "NO Share 400 Minutes"

    Case "Local STate of CA 500 Anytime Unl N&W&IN CAll $35.99 .00 Long Dist 1y 1005 06/05 - 06/28 $35.99 monthly access charge 500 monthly general allowance minutes $.40 per minute after allowance"

    PlansName = "NO Share 500 Minutes"

    Case "Local Share ST of CA 400 Anytime Unl N&W&In $32.99 .00 Long Dist 1y 1005 $32.99 monthly access charge 400 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 400 Minutes"

    Case "Local Share ST of CA 400 Anytime Unl N&W&In $32.99 .00 Long Dist 1y 1005 $32.99 monthly access charge 400 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 400 Minutes"

    Case "Local Share ST of CA 500 Anytime Unl N&W&In $37.99 .00 Long Dist 1y 1005 $37.99 monthly access charge 500 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 500 Minutes"

    Case "Local Share ST of CA 600 Anytime Unl N&W&In $41.99 .00 Long Dist 1y 1005 $41.99 monthly access charge 600 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 600 Minutes"

    Case "Local State of CA 700 Anytime Unl N&W&In Call$43.99 .00 Long Dist 1y 1005 $43.99 monthly access charge 700 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "LOCAL Share 700"

    Case "Local State of CA 800 Anytime Unl N&W&In Call $47.99 .00 Long Dist 1y 1005 $47.99 monthly access charge 800 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 800 Minutes"

    Case "Local Share ST of CA 800 Anytime Unl N&W&In $49.99 .00 Long Dist 1y 1005 $49.99 monthly access charge 800 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 800 Minutes"

    Case "Local Share ST of CA 800 Anytime Unl N&W&In $49.99 .00 Long Dist 1y 1005 $49.99 monthly access charge 800 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Share 800 Min"

    Case "Local Share ST of CA 1200 Anytime Unl N&W&In $60.99 .00 Long Dist 1y 1005 $60.99 monthly access charge 1200 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Local Share 1200 Min"

    Case "Local State of CA 1200 Anytime Unl N&W&In Call$58.99 .00 Long Dist 1y 1005 $58.99 monthly access charge 1200 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "Local Share 1200 Min"

    Case "America's Ch II Sh ST of CA 400 Any Unl N&W&In $35.99 .00 Long Dist 1y 1005 $35.99 monthly access charge 400 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "America 400 Share"

    Case "America's Ch II ST of CA 800 Any Unl N&W&In $50.99 .00 Long Dist 1y 1005 $50.99 monthly access charge 800 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "America 800 Share"

    Case "America's Ch II Sh ST of CA 2000 Any Unl N&W&In $88.99 .00 Lng Dist 1y 1005 $88.99 monthly access charge 2000 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "America 2000 Share"

    Case "America's Ch II Sh ST of CA 800 Any Unl N&W&In $52.99 .00 Long Dist 1y 1005 $52.99 monthly access charge 800 monthly general allowance minutes $.35 per minute after allowance"

    PlansName = "America SHARED 800"

    Case "Business National Access Unlimited $55.00 1y 1203 $55.00 monthly access charge"

    PlansName = "DATA(Non Comm)"

    Case "Blackberry STate of CA Unlim MEgabyte $34.99 1y 1005 $34.99 monthly access charge $.06 per minute after allowance"

    PlansName = "BlackBerry(DATA Only)"

    Case "Nationwide for Bus Email+MSg 450 Any Unl PDA Unl MSg+N&W+IN $99.99 1107 $99.99 monthly access charge 450 monthly general allowance minutes $.25 per minute after allowance"

    PlansName = "NationWide: 450"

    Case "Nationwide for Bus Email 450 Share MIns Unl Bbry+N&W+IN $84.99 1107 10/20 - 10/26 $84.99 monthly access charge 450 monthly general allowance minutes $.25 per minute after allowance"

    PlansName = "BB 450min wdata"

    Case "CWC blk berry pda unlm data only $34.99 and pay by the min for voice on this plan at .06 cents min."

    PlansName = "BB zero min"

    Case Else

    PlansName = "VER Plan Not Found"

    End Select

    Friday, November 21, 2008 7:39 PM

Answers

  • I'm not sure how you want to structure your array but the following code will read in and parse the lines from a CSV file:

     

    Code Snippet

    Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("PathToFileGoesHere")

    TextFileReader.TextFieldType = FileIO.FieldType.Delimited

    TextFileReader.SetDelimiters(",")

    Dim CurrentRow As String()

    While Not TextFileReader.EndOfData

    Try

    CurrentRow = TextFileReader.ReadFields()

    Dim CurrentField As String

    For Each CurrentField In CurrentRow

    Console.Write(CurrentField)

    Next

    Catch ex As _

    Microsoft.VisualBasic.FileIO.MalformedLineException

    MsgBox("Line " & ex.Message & _

    "is not valid and will be skipped.")

    End Try

    Console.WriteLine()

    End While

     

     

    You can populate the array in the For...Next statement.

     

    Also, just a tip, you can combine the Case statements that all correspond to the same condition:

     

    Code Snippet

    Case "BroadbandAccess $59.99 0607 $59.99 monthly access charge", _

    "BroadbandAccess for Business $59.99 1007 $59.99 monthly access charge", _

    "BroadbandAccess for Business $59.99 1007 $59.99 monthly access charge", _

    "CORporate BroadbandAccess Unlimited America's Choice II $59.99", _

    "BroadbandAccess WIth Voice $59.99 0507 $59.99 monthly access charge", _

    "BroadbandAccess for B2b 5gb/$0.25 $59.99 0308 $59.99 monthly access charge", _

    "BroadbandAccess PRomo Unlimited $59.99 2y 0805 $59.99 monthly access charge", _

    "CORporate BroadbandAccess Unlimited America's Choice II $59.99 1yr 0805 $59.99 monthly access charge"

     

    PlansName = "High Speed EVDO"

     

     

    Friday, November 21, 2008 8:33 PM
  • Hi,

     

    There is a way you can simplify your logic here. Use a look up table, this could be the CSV file... here is an example....say your file was like this...

     

    "CWC blk berry pda unlm data only $34.99 and pay by the min for voice on this plan at .06 cents min.", "BB zero min"

     

    What you could do is read that into a hash table where the test "CWC blk...... " was the key, then instead of a huge case statement you could do a look up for the plans name....

     

    Dim lookup as New Dictionary(Of String, String)

    'read the data into lookup dictionary

     

    plansName = lookup(Plan)

     

    Those three lines above would replace the huge case statement you have.

     

    Saturday, November 22, 2008 4:07 PM

All replies

  • I'm not sure how you want to structure your array but the following code will read in and parse the lines from a CSV file:

     

    Code Snippet

    Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("PathToFileGoesHere")

    TextFileReader.TextFieldType = FileIO.FieldType.Delimited

    TextFileReader.SetDelimiters(",")

    Dim CurrentRow As String()

    While Not TextFileReader.EndOfData

    Try

    CurrentRow = TextFileReader.ReadFields()

    Dim CurrentField As String

    For Each CurrentField In CurrentRow

    Console.Write(CurrentField)

    Next

    Catch ex As _

    Microsoft.VisualBasic.FileIO.MalformedLineException

    MsgBox("Line " & ex.Message & _

    "is not valid and will be skipped.")

    End Try

    Console.WriteLine()

    End While

     

     

    You can populate the array in the For...Next statement.

     

    Also, just a tip, you can combine the Case statements that all correspond to the same condition:

     

    Code Snippet

    Case "BroadbandAccess $59.99 0607 $59.99 monthly access charge", _

    "BroadbandAccess for Business $59.99 1007 $59.99 monthly access charge", _

    "BroadbandAccess for Business $59.99 1007 $59.99 monthly access charge", _

    "CORporate BroadbandAccess Unlimited America's Choice II $59.99", _

    "BroadbandAccess WIth Voice $59.99 0507 $59.99 monthly access charge", _

    "BroadbandAccess for B2b 5gb/$0.25 $59.99 0308 $59.99 monthly access charge", _

    "BroadbandAccess PRomo Unlimited $59.99 2y 0805 $59.99 monthly access charge", _

    "CORporate BroadbandAccess Unlimited America's Choice II $59.99 1yr 0805 $59.99 monthly access charge"

     

    PlansName = "High Speed EVDO"

     

     

    Friday, November 21, 2008 8:33 PM
  • Hi,

     

    There is a way you can simplify your logic here. Use a look up table, this could be the CSV file... here is an example....say your file was like this...

     

    "CWC blk berry pda unlm data only $34.99 and pay by the min for voice on this plan at .06 cents min.", "BB zero min"

     

    What you could do is read that into a hash table where the test "CWC blk...... " was the key, then instead of a huge case statement you could do a look up for the plans name....

     

    Dim lookup as New Dictionary(Of String, String)

    'read the data into lookup dictionary

     

    plansName = lookup(Plan)

     

    Those three lines above would replace the huge case statement you have.

     

    Saturday, November 22, 2008 4:07 PM
  • I would put that data into a relational database. Much more efficient in my opinion for the task.

     

    Create two tables

     

    Plans [PlanRec, Plan]

    PlanDscriptions [PlanDescriptionRec,PlanRec, PlanDescription]

     

    Join the tables in a one to many relationship. One plan can have many plan descriptions.

     

    The rest will be gravy....

     

    Saturday, November 22, 2008 8:04 PM
  • yes I might do that, I really just want to make it simple and easy for people to update!! The sad thing is that the Cell phone companies change descriptions on a monthly bases - and the do stupid things like add or remove "$" infront of costs. I just hate to recompile my code for a description change..

     

    Monday, December 01, 2008 10:38 PM
  • Paul P Clement IV

     

     

    Could I use:

    Dim TextFileReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\TESTFILE.CSV")

    TextFileReader.TextFieldType = FileIO.FieldType.Delimited

    Dim contents = TextFileReader.ReadToEnd

    Dim aryContents() = contents.Split(",")

    TextFileReader.Close()

     

    I see your code above but I found this, what is the differance?

     

    thanks

    Monday, December 01, 2008 11:45 PM
  • Lost on how to do the array: here is what I have so far:

     

    TextFileReader.SetDelimiters(",")

    Dim CurrentRow As String()

    While Not TextFileReader.EndOfData

    PlanListNum = PlanListNum = 1

    Try

    CurrentRow = TextFileReader.ReadFields()

    Dim CurrentField As String

    For Each CurrentField In CurrentRow

    PlanListItem = PlanListItem + 1

    'MsgBox(CurrentField)

    PlanList(PlanListNum, PlanListNum) = CurrentField <--ERROR

    Next

     

    'Dim i As Integer = 0

    'For i = 0 To CurrentRow.Length - 1

    ' CurrentField = CurrentRow(i)

    ' MsgBox(CurrentField)

    'Next

    Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException

    MsgBox("Line " & ex.Message & "is not valid and will be skipped.")

    End Try

    ' Console.WriteLine()

    End While

     

    I get an an error on CurrentField

    Tuesday, December 02, 2008 12:04 AM
  • Which line of code and what is the error?

     

    Wednesday, December 03, 2008 2:01 PM