Access VBA Parsing CSV Field and Creating new rows

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kageyone
    New Member
    • May 2007
    • 7
    #1

    Access VBA Parsing CSV Field and Creating new rows

    I have an access database with a table with two fields. They are 1. a GUID field and 2. a comma delimited set of values.

    I want to take this table and for each row I want to do the following:

    copy the GUID to a new rows field 1
    parse out the first value of the csv field and copy to a new rows field 2

    Continue to write new rows with the GUID and the next value in the csv field until all values have been written to new rows in a new table.

    Input example:
    GUID /CSV
    4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001, 001T, 002, 003, 004

    Desired output:
    GUID /SingleValue
    4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001
    4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001T
    4609F93EE2C8483 78BEB0BD7A26CF7 BD / 002
    4609F93EE2C8483 78BEB0BD7A26CF7 BD / 003
    4609F93EE2C8483 78BEB0BD7A26CF7 BD /004

    disregard the "/". I am just trying to distinguish the values from the GUID.

    I don't use Access very much but I am importing excel bulk uploads and the base table rows have been inserted. The GUID is from that table. Now I need to add rows to another foreign key related table.

    Thanks for any help you can give.

    kageyone
    Tags: None
    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834
      #2
      Originally posted by kageyone
      I have an access database with a table with two fields. They are 1. a GUID field and 2. a comma delimited set of values.

      I want to take this table and for each row I want to do the following:

      copy the GUID to a new rows field 1
      parse out the first value of the csv field and copy to a new rows field 2

      Continue to write new rows with the GUID and the next value in the csv field until all values have been written to new rows in a new table.

      Input example:
      GUID /CSV
      4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001, 001T, 002, 003, 004

      Desired output:
      GUID /SingleValue
      4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001
      4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001T
      4609F93EE2C8483 78BEB0BD7A26CF7 BD / 002
      4609F93EE2C8483 78BEB0BD7A26CF7 BD / 003
      4609F93EE2C8483 78BEB0BD7A26CF7 BD /004

      disregard the "/". I am just trying to distinguish the values from the GUID.

      I don't use Access very much but I am importing excel bulk uploads and the base table rows have been inserted. The GUID is from that table. Now I need to add rows to another foreign key related table.

      Thanks for any help you can give.

      kageyone
      Just subscribing, will have a look later.

        Comment

        • ADezii
          Recognized Expert Expert
          • Apr 2006
          • 8834
          #3
          Originally posted by kageyone
          I have an access database with a table with two fields. They are 1. a GUID field and 2. a comma delimited set of values.

          I want to take this table and for each row I want to do the following:

          copy the GUID to a new rows field 1
          parse out the first value of the csv field and copy to a new rows field 2

          Continue to write new rows with the GUID and the next value in the csv field until all values have been written to new rows in a new table.

          Input example:
          GUID /CSV
          4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001, 001T, 002, 003, 004

          Desired output:
          GUID /SingleValue
          4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001
          4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001T
          4609F93EE2C8483 78BEB0BD7A26CF7 BD / 002
          4609F93EE2C8483 78BEB0BD7A26CF7 BD / 003
          4609F93EE2C8483 78BEB0BD7A26CF7 BD /004

          disregard the "/". I am just trying to distinguish the values from the GUID.

          I don't use Access very much but I am importing excel bulk uploads and the base table rows have been inserted. The GUID is from that table. Now I need to add rows to another foreign key related table.

          Thanks for any help you can give.

          kageyone
          Assumptions:
          1. Your Table name is tblValues, and consists of the following Fields:
            1. [ID] - (AutoNumber) - {Primary Key}
            2. [Values ] - (TEXT 255)
            3. Sample tblValues:
              [CODE=text]
              ID Values
              1 4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001, 001T, 002, 003, 004
              2 E63929AEE2C8483 78BEACBD7A26CH7 BF / 123, 9876, 4445,876,T8876[/CODE]
          2. Create a Table named tblResults consisting of the following Fields:
            1. [ID] - (AutoNumber) - {Primary Key}
            2. [GUID] - (TEXT 255)
            3. [CSV] - (TEXT 50)
          3. The following code should give the results that you requested:
            [CODE=vb]
            Dim MyDB As DAO.Database, rstValues As DAO.Recordset, rstResults As DAO.Recordset
            Dim varGUID_CSV As Variant, intCounter As Integer

            Set MyDB = CurrentDb
            Set rstValues = MyDB.OpenRecord set("tblValues" , dbOpenTable)
            Set rstResults = MyDB.OpenRecord set("tblResults ", dbOpenDynaset)

            DoCmd.SetWarnin gs False
            DoCmd.RunSQL "Delete * From tblResults"
            DoCmd.SetWarnin gs True

            Do While Not rstValues.EOF
            'Populate Array with CSvs for a given GUID
            varGUID_CSV = Split(rstValues ![Values], ",")
            For intCounter = LBound(varGUID_ CSV) To UBound(varGUID_ CSV)
            '1st Element contains the GUID as well as the 1st CSV
            If intCounter = 0 Then
            With rstResults
            .AddNew
            ![Guid] = Trim$(Left$(var GUID_CSV(intCou nter), InStr(varGUID_C SV(intCounter), " ") - 1))
            ![CSV] = Trim$(Right$(va rGUID_CSV(intCo unter), Len(varGUID_CSV (intCounter)) - InStrRev(varGUI D_CSV(intCounte r), " ")))
            .Update
            End With
            Else
            With rstResults
            .AddNew
            ![Guid] = Trim$(Left$(var GUID_CSV(0), InStr(varGUID_C SV(0), " ") - 1))
            ![CSV] = Trim$(varGUID_C SV(intCounter))
            .Update
            End With
            End If
            Next
            rstValues.MoveN ext
            Loop

            rstValues.Close : Set rstValues = Nothing
            rstResults.Clos e: Set rstResults = Nothing

            'Let's see the fruits of our labor
            DoCmd.OpenTable "tblResults ", acViewNormal, acReadOnly[/CODE]

          OUTPUT:
          [CODE=text]
          ID GUID CSV
          162 4609F93EE2C8483 78BEB0BD7A26CF7 BD 001
          163 4609F93EE2C8483 78BEB0BD7A26CF7 BD 001T
          164 4609F93EE2C8483 78BEB0BD7A26CF7 BD 002
          165 4609F93EE2C8483 78BEB0BD7A26CF7 BD 003
          166 4609F93EE2C8483 78BEB0BD7A26CF7 BD 004
          167 E63929AEE2C8483 78BEACBD7A26CH7 BF 123
          168 E63929AEE2C8483 78BEACBD7A26CH7 BF 9876
          169 E63929AEE2C8483 78BEACBD7A26CH7 BF 4445
          170 E63929AEE2C8483 78BEACBD7A26CH7 BF 876
          171 E63929AEE2C8483 78BEACBD7A26CH7 BF T8876
          [/CODE]

            Comment

            • kageyone
              New Member
              • May 2007
              • 7
              #4
              Originally posted by ADezii
              Assumptions:
              1. Your Table name is tblValues, and consists of the following Fields:
                1. [ID] - (AutoNumber) - {Primary Key}
                2. [Values ] - (TEXT 255)
                3. Sample tblValues:
                  [CODE=text]
                  ID Values
                  1 4609F93EE2C8483 78BEB0BD7A26CF7 BD / 001, 001T, 002, 003, 004
                  2 E63929AEE2C8483 78BEACBD7A26CH7 BF / 123, 9876, 4445,876,T8876[/CODE]
              2. Create a Table named tblResults consisting of the following Fields:
                1. [ID] - (AutoNumber) - {Primary Key}
                2. [GUID] - (TEXT 255)
                3. [CSV] - (TEXT 50)
              3. The following code should give the results that you requested:
                [CODE=vb]
                Dim MyDB As DAO.Database, rstValues As DAO.Recordset, rstResults As DAO.Recordset
                Dim varGUID_CSV As Variant, intCounter As Integer

                Set MyDB = CurrentDb
                Set rstValues = MyDB.OpenRecord set("tblValues" , dbOpenTable)
                Set rstResults = MyDB.OpenRecord set("tblResults ", dbOpenDynaset)

                DoCmd.SetWarnin gs False
                DoCmd.RunSQL "Delete * From tblResults"
                DoCmd.SetWarnin gs True

                Do While Not rstValues.EOF
                'Populate Array with CSvs for a given GUID
                varGUID_CSV = Split(rstValues ![Values], ",")
                For intCounter = LBound(varGUID_ CSV) To UBound(varGUID_ CSV)
                '1st Element contains the GUID as well as the 1st CSV
                If intCounter = 0 Then
                With rstResults
                .AddNew
                ![Guid] = Trim$(Left$(var GUID_CSV(intCou nter), InStr(varGUID_C SV(intCounter), " ") - 1))
                ![CSV] = Trim$(Right$(va rGUID_CSV(intCo unter), Len(varGUID_CSV (intCounter)) - InStrRev(varGUI D_CSV(intCounte r), " ")))
                .Update
                End With
                Else
                With rstResults
                .AddNew
                ![Guid] = Trim$(Left$(var GUID_CSV(0), InStr(varGUID_C SV(0), " ") - 1))
                ![CSV] = Trim$(varGUID_C SV(intCounter))
                .Update
                End With
                End If
                Next
                rstValues.MoveN ext
                Loop

                rstValues.Close : Set rstValues = Nothing
                rstResults.Clos e: Set rstResults = Nothing

                'Let's see the fruits of our labor
                DoCmd.OpenTable "tblResults ", acViewNormal, acReadOnly[/CODE]

              OUTPUT:
              [CODE=text]
              ID GUID CSV
              162 4609F93EE2C8483 78BEB0BD7A26CF7 BD 001
              163 4609F93EE2C8483 78BEB0BD7A26CF7 BD 001T
              164 4609F93EE2C8483 78BEB0BD7A26CF7 BD 002
              165 4609F93EE2C8483 78BEB0BD7A26CF7 BD 003
              166 4609F93EE2C8483 78BEB0BD7A26CF7 BD 004
              167 E63929AEE2C8483 78BEACBD7A26CH7 BF 123
              168 E63929AEE2C8483 78BEACBD7A26CH7 BF 9876
              169 E63929AEE2C8483 78BEACBD7A26CH7 BF 4445
              170 E63929AEE2C8483 78BEACBD7A26CH7 BF 876
              171 E63929AEE2C8483 78BEACBD7A26CH7 BF T8876
              [/CODE]
              This is great! Thanks. This is a standalone vb program? This is not an Access Module right? Is this VB6 or did you do this in some other version?

              Thanks again.

                Comment

                • ADezii
                  Recognized Expert Expert
                  • Apr 2006
                  • 8834
                  #5
                  Originally posted by kageyone
                  This is great! Thanks. This is a standalone vb program? This is not an Access Module right? Is this VB6 or did you do this in some other version?

                  Thanks again.
                  Your comment never got Posted, so I have no idea what was said. If needed, delete my code reference and Post just your comment.

                    Comment

                    Previous template Next
                    Working...