Results 1 to 3 of 3
  1. #1
    capjlp is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2014
    Location
    Berea, Ky
    Posts
    26

    Splitting Comma vs Atoms?

    Hey

    I have a an issue where someone sent me a csv file and I need to unfortunately as always make what they send to me work. I would like to just use query's for this or make a new table if need to.

    From this example of Query1 you see I have a reference ID which my ChildPK A Device UUID which is a serial number and the Device Model which is the Model number.


    Reference ID Device UUID(s) Device Model(s)
    1606 09aa01ac081802v8 Nest Learning Thermostat
    1607 09af01af34190h36 Nest Learning Thermostat
    1611 09ag01ac361806n6,09ag01ac361806nq Nest Learning Thermostat,Nest Learning Thermostat
    1615 09aa01ac33160p7b,09aa01ac16160r6e Nest Learning Thermostat,Nest Learning Thermostat
    1616 02aa01ac501307g5,02aa01ac481303ht Nest Learning Thermostat,Nest Learning Thermostat
    1627 09aa01ac15180skg,09aa01ac44160x28 Nest Learning Thermostat,Nest Learning Thermostat
    1630 15aa01ac021906ma Nest Thermostat E
    1639 09aa01ac24160jfg Nest Learning Thermostat


    Query1.zip



    I created a csv for this example if that might help you help me. It has been attached.

    What i need is to split those serial and model numbers up so 1 row per Serial Number/Model

    Like this
    ReferenceID Device UUID(s) Device Model(s)
    1607 09af01af34190h36 Nest Learning Thermostat
    1611 09ag01ac361806n6 Nest Learning Thermostat
    1611 09ag01ac361806nq Nest Learning Thermostat


    Help is appreciated Thank you in advance!

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    query 1 pulls ONLY the recs with commas, then parses each item in list ,then writes the record to the table.
    something like:

    Code:
    Public Sub ParseCommaTbl()
    Dim rst
    'query1 ONLY pulls the records with commas
    'query1 = "select * from table where instr([device model(s)],',')>0")
    DoCmd.SetWarnings False
    Set rst = CurrentDb.OpenRecordset("select * from query1")
    With rst
      While Not .EOF
         VREF = .Fields("[Reference ID]").Value
         vDevice = .Fields("[Device UUID(s)]").Value
         vFld = .Fields("[Device Model(s)]").Value
         
         i = InStr(vFld, ",")
         While i > 0
           vWord = Left(vFld, i - 1)
           vFld = Mid(vFld, i + 1)
           GoSub PostRec
         Wend
           'post last item in comma list
         vWord = vFld
         GoSub PostRec
         
         .MoveNext
      Wend
      
    End With
      
      'remove comma recs
    ' DoCmd.OpenQuery "qdDeleteCommaRecs"
    DoCmd.SetWarnings True
    Exit Sub
       'post single rec to the table
    PostRec:
       sSql = "Insert into table ([REFERENCE ID],[Device UUID(s)],[Device Model(s)]) VALUES ('" & VREF & "','" & vDevice & "','" & vWord & "')"
       DoCmd.RunSQL sSql
      Return
    End Sub
    
    


  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This was interesting...

    So the dB reads a CSV file and writes to another CSV file in the same location. The new file will have "_Mod" added to the file name.
    Old file name "Query1.csv"
    New file name "Query1_Mod.csv"


    *** There is very little to no error handling.
    It doesn't check to see if it is a valid csv file. Given the small data sample, there will be errors if the number of commas is not 2 or is not 4.

    You will over write the modified (new) CSV file if you run the code more than once - NO warnings!



    "Reference ID","Device UUID(s)","Device Model(s)"
    BTW, these are very poor field names. There are spaces and special characters in the names.


    Good luck with your project.
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to hide comma if there is no value
    By accessLearn in forum Reports
    Replies: 3
    Last Post: 10-26-2015, 01:09 PM
  2. Comma in filename?
    By lithium in forum Programming
    Replies: 4
    Last Post: 10-23-2014, 01:34 PM
  3. comma in a query
    By jscriptor09 in forum Access
    Replies: 1
    Last Post: 05-18-2012, 04:18 PM
  4. Comma
    By tmcrouse in forum Queries
    Replies: 4
    Last Post: 10-11-2011, 04:33 PM
  5. Replies: 10
    Last Post: 02-06-2010, 10:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums