Results 1 to 16 of 16

Thread: Detecting and avoiding duplicate data stored in a loaded CSV

  1. #1

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Lightbulb Detecting and avoiding duplicate data stored in a loaded CSV

    Morning VB community..

    I am involved with a project where they have provided me a CSV file with a 3-column comma delimited data array..

    It goes as follows:

    Machine Name, UserID, LabelID
    MSI-MACHINE01,adm-testaccoun!,LABELNAME1

    This CSV file has over a thousand rows with Machine Names associated with the same LabelID at least once as follows:

    Code:
    MSI-MACHINE01,adm-testaccoun!,LABELNAME1
    MSI-MACHINE01,adm-testaccount,LABELNAME2
    MSI-MACHINE02,adm-testaccoun!,LABELNAME3
    MSI-MACHINE03,adm-testaccount,LABELNAME4
    Essentally, we use the CSV to capture what MachineIDs or UserIDs are attached to that third column "LabelID" and write this to a txt file called "ShortcutFile" to the user's own Z:\UserDesktop\.

    So the scenario is as follows:

    If the below script reads the CSV file with the MachineID or UserID matches the LabelID data on the third column then write a ShortcutFile 1.txt to Z:\UserDesktop\ in an area of the txt called "LABELNAME001" which replaces it with the LabelID found.

    Before doing this it checks for the existence of any older named .txt of this type and renames them to .old as a quick backup process.

    This all works fine when the MachineID or UserIDs have different LabelIDs as follows in the loaded CSV:

    Code:
    MSI-MACHINE01,adm-testaccoun!,LABELNAME1
    MSI-MACHINE0!,adm-testaccount,LABELNAME2
    MSI-MACHINE01,adm-testaccoun!,LABELNAME3
    MSI-MACHINE0!,adm-testaccount,LABELNAME4
    So the above scenario if the machine I am on is called "MSI-MACHINE01" and the user ID I am using is "adm-testaccount" it will create the following Shortcut X.txt files in Z:\UserDesktop\

    Shortcut 1.txt -> saves as "LABELNAME1" within the txt file
    Shortcut 2.txt -> saves as "LABELNAME2" within the txt file
    Shortcut 3.txt -> saves as "LABELNAME3" within the txt file
    Shortcut 4.txt -> saves as "LABELNAME4" within the txt file

    Problem is: when it finds this instead in the CSV:

    Code:
    MSI-MACHINE01,adm-testaccoun!,LABELNAME1
    MSI-MACHINE0!,adm-testaccount,LABELNAME1
    MSI-MACHINE01,adm-testaccoun!,LABELNAME2
    MSI-MACHINE0!,adm-testaccount,LABELNAME2
    It will create these shortcut files:

    Shortcut 1.txt -> saves as "LABELNAME1" within the txt file
    Shortcut 2.txt -> saves as "LABELNAME1" within the txt file
    Shortcut 3.txt -> saves as "LABELNAME2" within the txt file
    Shortcut 4.txt -> saves as "LABELNAME2" within the txt file

    The objective we want is if it detects trying to save the same kind of LabelID it will skip it to go onto the next unique label so the above scenario we want is this:

    Shortcut 1.txt -> saves as "LABELNAME1" within the txt file
    Shortcut 2.txt -> saves as "LABELNAME2" within the txt file

    So as you see we want it to skip writing Shortcut 2.txt with "LABELNAME1" (since it wrote it already in Shortcut 1.txt) and move onto the next unique LabelID and write it as the next incremented Shortcut X.txt.

    Here is the code we are using..

    Code:
    On Error Resume Next
    
    Const ForReading = 1
    Const ForWriting = 2
    Const CSV_File = "WorkstationUserLabel.csv"
    Const LABEL_Filename = "ShortcutFile"
    Const LABEL_TargetPath = "Z:\UserDesktop\"
    Dim CurDir, oFSO, sCSV, fCSV, aCSV_Line, sComputerName, sUserName, nFileIndex, fLABEL, sLABELContents
    
    netDrive = "Z:\"
    Set objFSO = CreateObject("Scripting.FileSystemObject")
     
    baseDir = "UserDesktop"
    If Not objFSO.FolderExists (netDrive & baseDir) Then
        objFSO.CreateFolder(netDrive & baseDir)
    Else
    
    End If
    
    'Search Z:\UserDesktop and rename existing ShortcutFile-only LABEL files to OLD
    Set strUserDesktop = objFSO.GetFolder("Z:\UserDesktop")
    Set folder = objFSO.GetFolder(strUserDesktop)
    
    For each file In folder.Files  
         If instr(file, "ShortcutFile") > 0 OR instr(file, "shortcutfile") > 0 THEN
             file.name = replace(file.name, ".txt", ".old")
         End IF
     Next
    
    sComputerName = CreateObject("WScript.Network").ComputerName
    sUserName = CreateObject("WScript.Network").UserName
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    CurDir = oFSO.GetParentFolderName(WScript.ScriptFullName)
    If Right(CurDir, 1) <> "\" Then CurDir = CurDir & "\"
    
    sCSV = CurDir & CSV_File
    
    If oFSO.FileExists(sCSV) Then
        Set fCSV = oFSO.OpenTextFile(sCSV, ForReading)
            Do Until fCSV.AtEndOfStream
                aCSV_Line = Split(fCSV.ReadLine, ",")
                If ((aCSV_Line(0) = sComputerName) Or (aCSV_Line(1) = sUserName)) Then  'Scan .CSV file for relevant Computer and Users and associate them to a LABEL NAME
                    nFileIndex = 1
                    Do Until Not(oFSO.FileExists(LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt"))
                        nFileIndex = nFileIndex + 1
                    Loop
                    oFSO.CopyFile CurDir & LABEL_Filename & ".txt", LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt" 'If associated LABEL file is detected, copy the select one(s) to Z:\UserDesktop
                    Set fLABEL = oFSO.OpenTextFile(LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt", ForReading)
                        sLABELContents = fLABEL.ReadAll
                    fLABEL.Close
                    sLABELContents = Replace(sLABELContents, "LABELNAME001", aCSV_Line(2))
                    Set fLABEL = oFSO.OpenTextFile(LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt", ForWriting) 'Associate detected LABEL NAME to declared LABEL filenames using how many Computer or Users are either found
                        fLABEL.WriteLine sLABELContents
                    fLABEL.Close
                End If
            Loop
        fCSV.Close
    End If
    All help is most appreciated.
    Last edited by thudo; Mar 24th, 2021 at 10:25 AM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,341

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Will the names be consecutive like that, or can they appear anywhere?
    ie, is it always like this:
    Code:
    MSI-MACHINE01,adm-testaccoun!,LABELNAME1
    MSI-MACHINE0!,adm-testaccount,LABELNAME1
    MSI-MACHINE01,adm-testaccoun!,LABELNAME2
    MSI-MACHINE0!,adm-testaccount,LABELNAME2
    Or can it also be like this:
    Code:
    MSI-MACHINE01,adm-testaccoun!,LABELNAME1
    MSI-MACHINE01,adm-testaccoun!,LABELNAME2
    MSI-MACHINE0!,adm-testaccount,LABELNAME1
    MSI-MACHINE0!,adm-testaccount,LABELNAME2
    There's a quick easy solution for one, that won't work for the second. There's a more involved solution that will work for both.
    I'm hoping for the former.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Thank you Techgnome for the prompt inquiry..

    So the CSV file in question we are reading "WorkstationUserLabel.csv" basically has a mis-mash of Workstation IDs + User IDs + Label IDs so it can be anything as you asked.

    Code:
    MSI-MACHINE01,adm-testaccount001,LABELNAME1
    MSI-MACHINE01,adm-testaccount001,LABELNAME2
    MSI-MACHINE02,adm-testaccount002,LABELNAME1
    MSI-MACHINE02,adm-testaccount003,LABELNAME2
    MSI-MACHINE03,adm-testaccount004,LABELNAME1
    MSI-MACHINE03,adm-testaccount004,LABELNAME2
    MSI-MACHINE03,adm-testaccount004,LABELNAME3
    MSI-MACHINE04,adm-testaccount004,LABELNAME1
    Basically we can have multiple Workstations with the same User ID and the same Label IDs OR one Workstation ID with multiple User IDs with different Label IDs.

    The VBS code I provided supports ONLY different Workstation IDs with different User IDs with different Label IDs in sequence so this works fine:

    Code:
    MSI-MACHINE01,adm-testaccount001,LABELNAME1
    MSI-MACHINE02,adm-testaccount001,LABELNAME2
    MSI-MACHINE03,adm-testaccount001,LABELNAME3
    MSI-MACHINE04,adm-testaccount001,LABELNAME4
    As well as this:

    Code:
    MSI-MACHINE01,adm-testaccount001,LABELNAME1
    MSI-MACHINE01,adm-testaccount002,LABELNAME2
    MSI-MACHINE01,adm-testaccount003,LABELNAME3
    MSI-MACHINE01,adm-testaccount004,LABELNAME4
    So it writes the txt line properly and in sequence BUT it fails when it finds this scenario:

    Code:
    MSI-MACHINE01,adm-testaccount001,LABELNAME1
    MSI-MACHINE02,adm-testaccount001,LABELNAME1
    MSI-MACHINE03,adm-testaccount001,LABELNAME2
    MSI-MACHINE04,adm-testaccount001,LABELNAME2
    or

    Code:
    MSI-MACHINE01,adm-testaccount001,LABELNAME1
    MSI-MACHINE01,adm-testaccount002,LABELNAME1
    MSI-MACHINE01,adm-testaccount003,LABELNAME2
    MSI-MACHINE01,adm-testaccount004,LABELNAME2
    See the issue? It will create a second shortcut txt file "LABELNAME1" rather than skip it to create the next unique one "LABELNAME2" as we do not want two of the same shortcut files with the same LABELNAME as its redundant.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,341

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Yuck.
    Use a Collection. Check to see if the LABELNAME# is in it, if not, create the shortcut, then add it to the Collection. If it is, then skip it and move on to the next row.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    The CSV is required.. I cannot get around that piece as it is a hard requirement.

    Based on my main VBS code I posted in the first post, can you edit it where I should have this check for the presence of the label name and, if found, if it tries to add a shortcut with the same LABELNAME# (after it added a a shortcut with the first instance) to skip it and proceed to create the next incremented shortcut with the next unique LABELNAME#.

  6. #6
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,073

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    If LABELNAME# is unique for all rows then can't you just increment it in each iteration?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  7. #7

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    The problem is some rows with the same Workstation ID or especially User ID have the same LABELNAME#.. if the column LABELNAME# has all unique #s for each row then the existing logic would be fine BUT we found they had similar LABELNAME#s for the same User ID, as example:

    Code:
    MSI-MACHINE01,adm-testaccount001,LABELNAME1
    MSI-MACHINE02,adm-testaccount001,LABELNAME1
    MSI-MACHINE03,adm-testaccount001,LABELNAME2
    MSI-MACHINE04,adm-testaccount001,LABELNAME2
    or

    Code:
    MSI-MACHINE01,adm-testaccount001,LABELNAME1
    MSI-MACHINE01,adm-testaccount002,LABELNAME1
    MSI-MACHINE01,adm-testaccount003,LABELNAME2
    MSI-MACHINE01,adm-testaccount004,LABELNAME2
    So if the LABELNAME# were all different then the script would be fine but the issue is it would need to skip trying to write a 2nd shortcut using the same LABELNAME# and go to the next one as having two incremented shortcut files BUT with the same LABELNAME# is redundant.

  8. #8
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,073

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    I mean, increment the # for each iteration, something like

    Code:
    a = 1
    loop
      a = a + 1
      label = "labelname" & a
    Or do you mean you just need the label # to be unique for the same MACHINE NAME and USERID?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  9. #9

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Thank you (and others) for your feedback: very much appreciate the support.

    So the CSV (which has three columns "Machine Name", "UserID", "LabelID") can have rows like the one I mentioned previous.. we can have the same Machine Names (Machine IDs) but different User IDs but the same Label IDs.. or it could have all unique Machine IDs but the same User IDs but similar Label IDs. Everything is fine until we get Label IDs duplicated for either the same Machine or User ID: it all Labels ID were completely unique per row then, as mentioned, we would not have the issue we're having. We're just trying to avoid the logic from duplicating creating a second Shortcut based on same Label ID found.

  10. #10
    Frenzied Member
    Join Date
    Nov 2017
    Posts
    1,495

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Having worked in a place a while ago that had convoluted nonsense happening on a large scale, I can only assume that is what you are having to work with here.

    I think I understand your goal, but even after reading your posts multiple times, I'm still not sure.

    If the end goal is to simply prevent the same Label ID from being written to more than 1 file, then your best bet is to follow the exact advice that techgnome provided above. If you Google search for vbscript collection you should find examples of how to use a collection which should help you solve your issue.

    Good luck.

  11. #11

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    > the end goal is to simply prevent the same Label ID from being written to more than 1 file
    Correct.. its writing a 2nd "redundant" shortcut txt with the same internal LABELNAME when it finds the same Workstation or User ID with the same Label ID duplicated.

    I guess I need to see the proper edit in my master script in the first post to understand how to prevent redundant LABELNAME'ed shortcuts.

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    33,341

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    I never said anything about dumping the CSV. I simply said use a Collection to keep track of the LAbelIds as you use them. When you read one from the CSV data, you look to see if it's in the collection. If it is, then you know you've processed it before, and can skip creating the shortcut for it. If it isn't then it's a new one, create the shortcut, then add the LabelId to the Collection.

    I'd modify your code, but I'm not sure Iknow enough about what you're doing to do it properly.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Thank you Techgnome... basically its preventing duplicate Shortcut txt files where it has the same duplicate LABELNAMEs so we want to check each time not to duplicate the same LABELNAME inside the shortcuts so each one is unique. I figure we need a internal text check to look for the LABELNAME# wildcard as each operation is processed.

  14. #14

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Update to all.. still challenged with this one and need a solution real soon.

    Here is the condensed VBS script:

    Code:
    On Error Resume Next
    
    Const ForReading = 1
    Const ForWriting = 2
    Const CSV_File = "List.csv"
    Const LABEL_Filename = "ShortcutFile"
    Const LABEL_TargetPath = "C:\Temp\"
    Dim CurDir, oFSO, sCSV, fCSV, aCSV_Line, sComputerName, sUserName, nFileIndex, fLABEL, sLABELContents
    
    sComputerName = CreateObject("WScript.Network").ComputerName
    sUserName = CreateObject("WScript.Network").UserName
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    CurDir = oFSO.GetParentFolderName(WScript.ScriptFullName)
    If Right(CurDir, 1) <> "\" Then CurDir = CurDir & "\"
    
    sCSV = CurDir & CSV_File
    
    If oFSO.FileExists(sCSV) Then
        Set fCSV = oFSO.OpenTextFile(sCSV, ForReading)
            Do Until fCSV.AtEndOfStream
                aCSV_Line = Split(fCSV.ReadLine, ",")
                If ((aCSV_Line(0) = sComputerName) Or (aCSV_Line(1) = sUserName)) Then  'Scan .CSV file for relevant Computer and Users and associate them to a LABEL NAME
                    nFileIndex = 1
                    Do Until Not(oFSO.FileExists(LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt"))
                        nFileIndex = nFileIndex + 1
                    Loop
                    oFSO.CopyFile CurDir & LABEL_Filename & ".txt", LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt" 'If associated LABEL file is detected, copy the select one(s) to Z:\UserDesktop
                    Set fLABEL = oFSO.OpenTextFile(LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt", ForReading)
                        sLABELContents = fLABEL.ReadAll
                    fLABEL.Close
                    sLABELContents = Replace(sLABELContents, "LABELNAME001", aCSV_Line(2))
                    Set fLABEL = oFSO.OpenTextFile(LABEL_TargetPath & LABEL_Filename & Chr(32) & cStr(nFileIndex) & ".txt", ForWriting) 'Associate detected LABEL NAME to declared LABEL filenames using how many Computer or Users are either found
                        fLABEL.WriteLine sLABELContents
                    fLABEL.Close
                End If
            Loop
        fCSV.Close
    End If
    So what you can do to simulate what we're looking for is as follows:

    1) First, create a folder in C:\ called "TEMP".
    2) Copy this VBS above to any place outside this TEMP folder. Call it whatever you wish.
    3) Where this VBS is, create a txt file called "ShortcutFile.txt"
    4) Edit this text file in, say, Notepad, and add only this one line and save the file: LABEL NAME=LABELNAME001
    5) Where this VBS also is, create a text file in, say, Notepad, and call it "List.txt". Edit this file and add the following within it:

    Code:
    MACHINE_NAME01,adm-testaccoun!,LABELNAME1
    MACHINE_NAME0!,adm-testaccount,LABELNAME1
    MACHINE_NAME01,adm-testaccoun!,LABELNAME2
    MACHINE_NAME0!,adm-testaccount,LABELNAME2
    Now "MACHINE_NAME01" should be your actual "Machine Name" and "adm-testaccount" is your current logged in user ID.

    Note the "!" in some columns.. that is simply to disable it for testing purposes.

    As example: this is how I set it up on my end where for me MACHINE_NAME01 = BLURP and adm-testaccount = Thudo so my List.txt looks as follows:

    Code:
    BLURP,Thud!,LABELNAME1
    BLURP,Thudo,LABELNAME1
    BLURP,Thud!,LABELNAME2
    BLURP,Thudo,LABELNAME2
    So set up your own List.txt as I have based on your Machine Name and current logged in user.

    6) Now rename List.txt to List.csv where it is.
    7) If all is ready, run the VBS and go to C:\TEMP and note how many txt files are there. There should four as follows and note the contents within:

    ShortcutFile 1.txt -> LABEL NAME=LABELNAME1
    ShortcutFile 2.txt -> LABEL NAME=LABELNAME1
    ShortcutFile 3.txt -> LABEL NAME=LABELNAME2
    ShortcutFile 4.txt -> LABEL NAME=LABELNAME2

    Now the problem is we want the actual result to be this:

    ShortcutFile 1.txt -> LABEL NAME=LABELNAME1
    ShortcutFile 2.txt -> LABEL NAME=LABELNAME2

    So in other words, it would skip over the detection of the second "LABELNAME1" which was placed in ShortcutFile 2.txt (as it would be considered as "present" by the ShortcutFile 1.txt that has it already and proceed to the next unique one "LABELNAME2" and write that to ShortcutFile 2.txt instead.

    I hope this now makes clear what we are looking to achieve and now you have a quick test scenario to see it in action.

    If you can, please edit my original script above. This is absolutely most appreciated!

  15. #15
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,399

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    As soon as a Script gets more complex, you should use more SubRoutines, like e.g. the following ones:

    Code:
    Function ReadUniqueLabels(FileName)
        Set ReadUniqueLabels = CreateObject("Scripting.Dictionary") 'we set the Function-return to a Dictionary-Object here
            ReadUniqueLabels.CompareMode = vbTextCompare 'make Key-comparisons case-insensitive
        Dim Line
        For Each Line In Split(ReadTextContents(FileName), vbCrLf)
            Line = Split(Line, ",")
            If Ubound(Line) = 2 Then
               If UCase(Trim(Line(0))) = UCase(sComputerName) OR _
                  UCase(Trim(Line(1))) = UCase(sUserName) Then
                    ReadUniqueLabels.Item(Trim(Line(2))) = Line 'this ensures the unique Key (with some useful data behind it: in this case the whole line-array)
               End If
            End If
        Next
    End Function
    
    Function ReadTextContents(FileName)
        With oFSO.OpenTextFile(FileName, 1) 'ForReading
             ReadTextContents = .ReadAll: .Close
        End With
    End Function
    
    Sub WriteTextContents(FileName, TextContent)
        With oFSO.OpenTextFile(FileName, 2, True) 'ForWriting, True -> create new, if not exists
            .Write TextContent: .Close
        End With
    End Sub

    Your Main-code (on top of these Routines) will then read a bit nicer:
    Code:
    Option Explicit
    'On Error Resume Next
    
    Const CSV_File = "List.csv"
    Const LABEL_Filename = "ShortcutFile"
    Const LABEL_TargetPath = "C:\Temp\"
    
    Dim sComputerName, sUserName, oFSO, CurDir, DLabels, Label, LblTemplate, Idx
        sComputerName = CreateObject("WScript.Network").ComputerName
        sUserName = CreateObject("WScript.Network").UserName
    
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    CurDir = oFSO.GetParentFolderName(WScript.ScriptFullName) & "\"
    
    LblTemplate = ReadTextContents(CurDir & LABEL_Filename & ".txt")
    Set DLabels = ReadUniqueLabels(CurDir & CSV_File)
    For Each Label In DLabels.Keys
        Label = Replace(LblTemplate, "LABELNAME001", Label)
        Idx = Idx + 1
        WriteTextContents LABEL_TargetPath & LABEL_Filename & " " & Idx & ".txt", Label
    Next
    Olaf

    Last edited by Schmidt; Apr 3rd, 2021 at 12:35 AM.

  16. #16

    Thread Starter
    Junior Member thudo's Avatar
    Join Date
    Sep 2008
    Location
    North Toronto, Canada
    Posts
    28

    Re: Detecting and avoiding duplicate data stored in a loaded CSV

    Utterly brilliant, Schmidt! That worked without question! \o/ Very much appreciate all your patience.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width