-
Mar 24th, 2021, 10:22 AM
#1
Thread Starter
Junior Member
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.
-
Mar 24th, 2021, 11:28 AM
#2
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
-
Mar 24th, 2021, 11:50 AM
#3
Thread Starter
Junior Member
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.
-
Mar 24th, 2021, 12:52 PM
#4
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
-
Mar 24th, 2021, 01:18 PM
#5
Thread Starter
Junior Member
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#.
-
Mar 24th, 2021, 09:21 PM
#6
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?
-
Mar 24th, 2021, 09:33 PM
#7
Thread Starter
Junior Member
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.
-
Mar 24th, 2021, 09:45 PM
#8
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?
-
Mar 24th, 2021, 10:02 PM
#9
Thread Starter
Junior Member
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.
-
Mar 24th, 2021, 10:12 PM
#10
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.
-
Mar 24th, 2021, 10:19 PM
#11
Thread Starter
Junior Member
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.
-
Mar 25th, 2021, 07:36 AM
#12
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
-
Mar 25th, 2021, 09:15 AM
#13
Thread Starter
Junior Member
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.
-
Apr 2nd, 2021, 11:31 AM
#14
Thread Starter
Junior Member
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!
-
Apr 3rd, 2021, 12:31 AM
#15
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.
-
Apr 3rd, 2021, 02:38 PM
#16
Thread Starter
Junior Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|