Adding unique client id to over 950,000 names Help!

[clsyldy2u]

I'm new to this so I need help from those that know what they are talking about. I need to add a field to my mailing list database with a unique client id for each line. I could input it manually but you see the problem as there are over 950,000 names and the lists will only get bigger. I want it to start with a letter and a random number after. doesn't have to be very long but they all have to be different. Thanks.

[pietzcker]

I guess you could do something like that with a script. But I'd also suppose that this script would have serious performance issues since it must check each new random number against all previously used ones.

The fastest way, I think, would be to use a hash function (as long as the 950.000 lines themselves are all different). I haven't checked, but probably UE's script engine doesn't provide one of those. In Python, the whole thing could be done in three lines:

Code: Select all

output=open("output.csv","w")
for line in open("input.csv","r"):
   output.write(hex(hash(line)+2147483648)[2:].rstrip("L")+","+line)


(doesn't necessarily start with a letter, though. Which one would you like?)

Just as an idea...
Tim

[clsyldy2u]

Thanks for the input I wanted to start with A i guess. Start from the beginning is best.

[jorrasdk]

I agree that a script could do it but with slow performance.

As I read it it's not vital that the client id is random as long as it is unique. So I think a fast solution could use a combination of search/replace with regular expression and Mofis CountUp Macro.

But before going any further we need to see some example lines of your database file. Please supply a before example as well as an after example. Of course you should anonymize sensitive data, but please don't make the examples too generic as they might not resemble real live data close enough.

Post both examples and surround them with the [­code][/code] tags

[clsyldy2u]

It's just a simple .txt file with comma delimiters

STEPHEN,XXXX,XXX WEST XX STREET,NEW YORK,NY,10001,YEAR,MAKE,MODEL,VIN
LAURA,XXXXX,1XX JANNEY LN,SPRINGBORO,OH,45066,YEAR,MAKE,MODEL,VIN
JAMES,XXXX,PO BOX XXXX,ARCHER CITY,TX,76351-1224,YEAR,MAKE,MODEL,VIN
SUSAN M,XXXXX,4XX N Sagehorn Dr,Hartford,SD,57033-2360,YEAR,MAKE,MODEL,VIN

[jorrasdk]

On second thought I think I will suggest a different approach. This macro uses the powerful column mode to insert a letter "C" and a number increased by 1 for every line. Give it a go and see if that works for you.

InsertMode
ColumnModeOn
HexOff
UnixReOff
Top
SelectToBottom
","
Top
SelectToBottom
"C"
ColumnInsertNum 1 1 LeadingZero
Top
ColumnModeOff

Your example will look like this after the macro is run:

C1,STEPHEN,XXXX,XXX WEST XX STREET,NEW YORK,NY,10001,YEAR,MAKE,MODEL,VIN
C2,LAURA,XXXXX,1XX JANNEY LN,SPRINGBORO,OH,45066,YEAR,MAKE,MODEL,VIN
C3,JAMES,XXXX,PO BOX XXXX,ARCHER CITY,TX,76351-1224,YEAR,MAKE,MODEL,VIN
C4,SUSAN M,XXXXX,4XX N Sagehorn Dr,Hartford,SD,57033-2360,YEAR,MAKE,MODEL,VIN

[Mofi]

Macro from jorrasdk is a good idea. Here is it again a little bit optimized and with additional code to make sure that "Cxx," is not inserted on empty lines at end of the file.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Loop
Bottom
IfColNum 1
Key BACKSPACE
Else
ExitLoop
EndIf
EndLoop
Top
ColumnModeOn
ColumnInsert "C,"
Key RIGHT ARROW
ColumnInsertNum 1 1 LeadingZero
ColumnModeOff
Bottom
InsertLine
Top

[clsyldy2u]

You have no idea how much you guys helped me out..........Thank YOU!!