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

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

4
NewbieNewbie
4

    Oct 30, 2007#1

    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.

    236
    MasterMaster
    236

      Oct 30, 2007#2

      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

      4
      NewbieNewbie
      4

        Oct 30, 2007#3

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

        262
        MasterMaster
        262

          Oct 30, 2007#4

          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

          4
          NewbieNewbie
          4

            Oct 30, 2007#5

            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

            262
            MasterMaster
            262

              Oct 31, 2007#6

              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

              6,675585
              Grand MasterGrand Master
              6,675585

                Nov 01, 2007#7

                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
                Best regards from an UC/UE/UES for Windows user from Austria

                4
                NewbieNewbie
                4

                  Nov 11, 2007#8

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