Data migration with case sensitive IDs (was: Data migration help please!) [ Edited ]

[hudini]

I have been migrating data from one database to a new one. i am trying to migrate relaionships.  For example,  these 20 contacts belong to this campaign. I was advised to use the vlookup. i imported the old data with the old ID's as the 'dub ID field' and then exported both the dub ID's and the new ID's. then i take the old campaign that has the old ID's and with the vlookup replace the old ID's with the new one. This would threoretically work great EXCEPT that all the ID's are case sensitive. IE 012000yHG is a diff person than 012000Yhg!

So, i found the case sensitive vlookup which is

=IF(EXACT(C1,VLOOKUP(C1,A1:B5,1,FALSE))=TRUE,VLOOKUP(C1,A1:B5,2,FALSE),"No exact match")

BUT what this one does is let's say it's looking for Csw and finds CSw it inserts value 'no exact match' and stops looking for the exact match. i need to  add to the above formula to tell it to KEEP LOOKING down the column untill it does find the exact match.

any ideas??

thanks!

Hudi

Message Edited by Kingsley on 06-30-2006 03:58 AM

[PKP]

Hudini,

I assume you are using your  OLD ID's as the Lookup value to bring back the salesforce ID?  As long as your Old-ID's are just numeric the VLOOKUP should work fine and you don't ever need to Lookup the salesforce id.

So, VLOOKUP(C1,A1:B5,1,FALSE) in your case should be VLOOKUP(Old-Id,A= Column with export of dub-id:B= Column with related sfdc-ID),2= Column with sfdc-ID,FALSE)

I hope that helps and doesn't confuse the issue more!!

[hudini]

Thanks!

I figured this out (with SF help!) seems that when you pull the ID's from the data loader they are not case sensitive as they were with pulling them from SF itself via a report. I have to say that SF help was really great with this one! The data import with the relationships worked! one point worth mentioning - the data loader seemed to 'crash' when I loaded too much info at once - I found I had to split my file up in order for the DL to read it.

( I wrote out a step by step process for the import - if anyone ever needs, feel free to email me for it)

HFalik@aish.com

[ChicagoDrew · Accepted solution]

I've experienced similar frustrations with this, and also received great from SFDC support. They recommended using the Excel formula below to create a unique, non-case-sensitive ID from a case-sensitive SFDC ID. Just replace “# “ in the formula below with the cell number where the ID is located:

=#&CODE(MID(#,12,1))&CODE(MID(#,13,1))&CODE(MID(#,14,1))&CODE(MID(#,15,1))

When I plugged in this code and tried it out, here's what I got:

00130000001s02U	00130000001s02U115485085
00130000001s02u	00130000001s02u1154850117
00130000001S02u	00130000001S02u834850117

[hudini]

This is actually a great formula to keep in mind. ...  Thanks!

[Scot]

Hudi,

What I've usually done in this case (using Excel with ids) is to expand the case sensitive (15 character) ID into the case insensitive - (18 character) version.

Depending on how you're getting your source data, you may be able to get the 18 character version in the first place.  If not, you can convert it with a relatively simple visual-basic function. There's a version of this code in the Excel connector which looks like:

Code:

Function FixID(InID As String) As String
' Converts a 15 character ID to an 18 character, case-insensitive one ...
Dim InChars As String, InI As Integer, InUpper As String
Dim InCnt As Integer
InChars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ012345"
InUpper = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

InCnt = 0
For InI = 15 To 1 Step -1
   InCnt = 2 * InCnt + Sgn(InStr(1, InUpper, Mid(InID, InI, 1), vbBinaryCompare))
   If InI Mod 5 = 1 Then
       FixID = Mid(InChars, InCnt + 1, 1) + FixID
       InCnt = 0
       End If
    Next InI
    FixID = InID + FixID
End Function

[JudiS]

---

I've experienced similar frustrations with this, and also received great from SFDC support. They recommended using the Excel formula below to create a unique, non-case-sensitive ID from a case-sensitive SFDC ID. Just replace “# “ in the formula below with the cell number where the ID is located:

=#&CODE(MID(#,12,1))&CODE(MID(#,13,1))&CODE(MID(#,14,1))&CODE(MID(#,15,1))

---

Thank you, thank you!! I had been struggling with this for hours...this worked perfectly! :-)

[ryanhallman]

data migration can be a pain in excel...

i was trying to manipulate 400Mb csv files with vlookup's going everywhere...

For larger data migrations, I've started using Apatar, it's a free ETL tool, which can transform data between data sources. E.g. from SQL to Sfdc.

It takes a few hours to correctly define a complicated Map, but once set up, it's a good way to do a one off data migration, you can also schedule it as a service for ongoing batch uploads.

Personally I believe Apatar is probably not as good as the commercial products out there, but considering it's open source and free I use it with my clients who needs to transfer data once off and who dont have a need to purchase an commercial grade ETL tool.

[Mounir]

Hi,

Nice one ChicagoDrew.

Thanks !

[Stevemo]

I would also make sure that the ID field is in Column-A, and files are sorted by ID (both files).  That should keep your Excel VLOOKUP from quitting.