469,337 Members | 5,958 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,337 developers. It's quick & easy.

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
4609F93EE2C848378BEB0BD7A26CF7BD / 001, 001T, 002, 003, 004

Desired output:
GUID /SingleValue
4609F93EE2C848378BEB0BD7A26CF7BD / 001
4609F93EE2C848378BEB0BD7A26CF7BD / 001T
4609F93EE2C848378BEB0BD7A26CF7BD / 002
4609F93EE2C848378BEB0BD7A26CF7BD / 003
4609F93EE2C848378BEB0BD7A26CF7BD /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
Feb 29 '08 #1
4 5460
ADezii
8,800 Expert 8TB
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
4609F93EE2C848378BEB0BD7A26CF7BD / 001, 001T, 002, 003, 004

Desired output:
GUID /SingleValue
4609F93EE2C848378BEB0BD7A26CF7BD / 001
4609F93EE2C848378BEB0BD7A26CF7BD / 001T
4609F93EE2C848378BEB0BD7A26CF7BD / 002
4609F93EE2C848378BEB0BD7A26CF7BD / 003
4609F93EE2C848378BEB0BD7A26CF7BD /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.
Feb 29 '08 #2
ADezii
8,800 Expert 8TB
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
4609F93EE2C848378BEB0BD7A26CF7BD / 001, 001T, 002, 003, 004

Desired output:
GUID /SingleValue
4609F93EE2C848378BEB0BD7A26CF7BD / 001
4609F93EE2C848378BEB0BD7A26CF7BD / 001T
4609F93EE2C848378BEB0BD7A26CF7BD / 002
4609F93EE2C848378BEB0BD7A26CF7BD / 003
4609F93EE2C848378BEB0BD7A26CF7BD /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:
      Expand|Select|Wrap|Line Numbers
      1. ID    Values
      2. 1    4609F93EE2C848378BEB0BD7A26CF7BD / 001, 001T, 002, 003, 004
      3. 2    E63929AEE2C848378BEACBD7A26CH7BF / 123, 9876, 4445,876,T8876
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, rstValues As DAO.Recordset, rstResults As DAO.Recordset
    2. Dim varGUID_CSV As Variant, intCounter As Integer
    3.  
    4. Set MyDB = CurrentDb
    5. Set rstValues = MyDB.OpenRecordset("tblValues", dbOpenTable)
    6. Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    7.  
    8. DoCmd.SetWarnings False
    9.  DoCmd.RunSQL "Delete * From tblResults"
    10. DoCmd.SetWarnings True
    11.  
    12. Do While Not rstValues.EOF
    13.   'Populate Array with CSvs for a given GUID
    14.   varGUID_CSV = Split(rstValues![Values], ",")
    15.     For intCounter = LBound(varGUID_CSV) To UBound(varGUID_CSV)
    16.       '1st Element contains the GUID as well as the 1st CSV
    17.       If intCounter = 0 Then
    18.         With rstResults
    19.           .AddNew
    20.             ![Guid] = Trim$(Left$(varGUID_CSV(intCounter), InStr(varGUID_CSV(intCounter), " ") - 1))
    21.             ![CSV] = Trim$(Right$(varGUID_CSV(intCounter), Len(varGUID_CSV(intCounter)) - InStrRev(varGUID_CSV(intCounter), " ")))
    22.           .Update
    23.         End With
    24.       Else
    25.         With rstResults
    26.           .AddNew
    27.             ![Guid] = Trim$(Left$(varGUID_CSV(0), InStr(varGUID_CSV(0), " ") - 1))
    28.             ![CSV] = Trim$(varGUID_CSV(intCounter))
    29.           .Update
    30.         End With
    31.       End If
    32.     Next
    33.   rstValues.MoveNext
    34. Loop
    35.  
    36. rstValues.Close: Set rstValues = Nothing
    37. rstResults.Close: Set rstResults = Nothing
    38.  
    39. 'Let's see the fruits of our labor
    40. DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. ID    GUID                               CSV
  2. 162    4609F93EE2C848378BEB0BD7A26CF7BD    001
  3. 163    4609F93EE2C848378BEB0BD7A26CF7BD    001T
  4. 164    4609F93EE2C848378BEB0BD7A26CF7BD    002
  5. 165    4609F93EE2C848378BEB0BD7A26CF7BD    003
  6. 166    4609F93EE2C848378BEB0BD7A26CF7BD    004
  7. 167    E63929AEE2C848378BEACBD7A26CH7BF    123
  8. 168    E63929AEE2C848378BEACBD7A26CH7BF    9876
  9. 169    E63929AEE2C848378BEACBD7A26CH7BF    4445
  10. 170    E63929AEE2C848378BEACBD7A26CH7BF    876
  11. 171    E63929AEE2C848378BEACBD7A26CH7BF    T8876
  12.  
Mar 1 '08 #3
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:
      Expand|Select|Wrap|Line Numbers
      1. ID    Values
      2. 1    4609F93EE2C848378BEB0BD7A26CF7BD / 001, 001T, 002, 003, 004
      3. 2    E63929AEE2C848378BEACBD7A26CH7BF / 123, 9876, 4445,876,T8876
  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:
    Expand|Select|Wrap|Line Numbers
    1. Dim MyDB As DAO.Database, rstValues As DAO.Recordset, rstResults As DAO.Recordset
    2. Dim varGUID_CSV As Variant, intCounter As Integer
    3.  
    4. Set MyDB = CurrentDb
    5. Set rstValues = MyDB.OpenRecordset("tblValues", dbOpenTable)
    6. Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
    7.  
    8. DoCmd.SetWarnings False
    9.  DoCmd.RunSQL "Delete * From tblResults"
    10. DoCmd.SetWarnings True
    11.  
    12. Do While Not rstValues.EOF
    13.   'Populate Array with CSvs for a given GUID
    14.   varGUID_CSV = Split(rstValues![Values], ",")
    15.     For intCounter = LBound(varGUID_CSV) To UBound(varGUID_CSV)
    16.       '1st Element contains the GUID as well as the 1st CSV
    17.       If intCounter = 0 Then
    18.         With rstResults
    19.           .AddNew
    20.             ![Guid] = Trim$(Left$(varGUID_CSV(intCounter), InStr(varGUID_CSV(intCounter), " ") - 1))
    21.             ![CSV] = Trim$(Right$(varGUID_CSV(intCounter), Len(varGUID_CSV(intCounter)) - InStrRev(varGUID_CSV(intCounter), " ")))
    22.           .Update
    23.         End With
    24.       Else
    25.         With rstResults
    26.           .AddNew
    27.             ![Guid] = Trim$(Left$(varGUID_CSV(0), InStr(varGUID_CSV(0), " ") - 1))
    28.             ![CSV] = Trim$(varGUID_CSV(intCounter))
    29.           .Update
    30.         End With
    31.       End If
    32.     Next
    33.   rstValues.MoveNext
    34. Loop
    35.  
    36. rstValues.Close: Set rstValues = Nothing
    37. rstResults.Close: Set rstResults = Nothing
    38.  
    39. 'Let's see the fruits of our labor
    40. DoCmd.OpenTable "tblResults", acViewNormal, acReadOnly
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. ID    GUID                               CSV
  2. 162    4609F93EE2C848378BEB0BD7A26CF7BD    001
  3. 163    4609F93EE2C848378BEB0BD7A26CF7BD    001T
  4. 164    4609F93EE2C848378BEB0BD7A26CF7BD    002
  5. 165    4609F93EE2C848378BEB0BD7A26CF7BD    003
  6. 166    4609F93EE2C848378BEB0BD7A26CF7BD    004
  7. 167    E63929AEE2C848378BEACBD7A26CH7BF    123
  8. 168    E63929AEE2C848378BEACBD7A26CH7BF    9876
  9. 169    E63929AEE2C848378BEACBD7A26CH7BF    4445
  10. 170    E63929AEE2C848378BEACBD7A26CH7BF    876
  11. 171    E63929AEE2C848378BEACBD7A26CH7BF    T8876
  12.  
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.
Mar 3 '08 #4
ADezii
8,800 Expert 8TB
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.
Mar 6 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Brian Graham | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.