ETL To BuddyPress

Field Mapping - Member$

email,email
user_login,user_email

Field Mapping - Profile$

gender,country,ethnicity,postalcode
gender,country,ethnicity,postalcode
,,,
,,,
,JP-->Japan,American Indian/Alaskan Native-->AIAN,
,GB-->United Kingdom,Asian-->Asian,
,CA-->Canada,"White, Non-Hispanic-->White",
,NZ-->New Zealand,African American/Black-->Black,
,UM-->United States,White/Non-Hispanic-->White,
,AU-->Australia,Other-->OthEth,
,ZA-->South Africa,Native Hawaiian/Pacific Islander-->NHPI,
,AM-->Armenia,Hispanic-->Hispanic,
,ES-->Spain,,
,IE-->Ireland,,
,"VI-->Virgin Islands, U.S.",,
,BR-->Brazil,,
,MX-->Mexico,,
,BE-->Belgium,,
,US-->United States,,

LINQPad Script

void Main()
{
	var fileName = string.Format("{0}\\DrupalToWpProfileMapping.xls", @"C:\temp");
	var connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\";", fileName);
	
	var adapter = new OleDbDataAdapter("SELECT * FROM [Field Mapping - Member$]", connectionString);
	var ds = new DataSet();
	
	adapter.Fill(ds, "MemberMaps");
	
	DataTable memberMaps = ds.Tables["MemberMaps"];
	var mapFromRow = memberMaps.Rows[0];
	var mapRow = memberMaps.Rows[1];
	
	var dictMemberMaps = new Dictionary<string, string>();
	var dictMemberMapTranslations = new Dictionary<string, Dictionary<string, string>>();
	var diagnoses = new List<string>();
	
	foreach(DataColumn c in memberMaps.Columns)
	{
		if(!string.IsNullOrEmpty(mapRow[c.ColumnName].ToString()))
		{
			var icn = mapRow[c.ColumnName].ToString();
			
			dictMemberMaps[icn] = mapFromRow[c.ColumnName].ToString();
			dictMemberMapTranslations[icn] = new Dictionary<string, string>();
			
			for(var i = 4; true; ++i)
			{
				var data = memberMaps.Rows[i][c.ColumnName].ToString();
				
				if(string.IsNullOrEmpty(data)) break;
				
				var parts = data.Split(new string[] { "-->" }, StringSplitOptions.None);
				
				dictMemberMapTranslations[icn][parts[0]] = parts[1];				
			}
		}
	}
	
	adapter = new OleDbDataAdapter("SELECT * FROM [Field Mapping - Profile$]", connectionString);
	
	adapter.Fill(ds, "ProfileMaps");
	
	DataTable profileMaps = ds.Tables["ProfileMaps"];
	mapFromRow = profileMaps.Rows[0];
	mapRow = profileMaps.Rows[1];
	
	var dictProfileMaps = new Dictionary<string, string>();
	var dictProfileMapTranslations = new Dictionary<string, Dictionary<string, string>>();

	
	foreach(DataColumn c in profileMaps.Columns)
	{
		if(!string.IsNullOrEmpty(mapRow[c.ColumnName].ToString()))
		{
			var icn = mapRow[c.ColumnName].ToString().Trim();
			
			dictProfileMaps[icn] = mapFromRow[c.ColumnName].ToString().Trim();
			dictProfileMapTranslations[icn] = new Dictionary<string, string>();
			
			for(var i = 4; true; ++i)
			{
				var data = profileMaps.Rows[i][c.ColumnName].ToString();
				
				if(string.IsNullOrEmpty(data)) break;
				
				var parts = data.Split(new string[] { "-->" }, StringSplitOptions.None);
				
				dictProfileMapTranslations[icn][parts[0].Trim()] = parts[1].Trim();
			}
			
			if(mapFromRow[c.ColumnName].ToString() == "diagnosed")
			{
				diagnoses = new List<string>(dictProfileMapTranslations[icn].Values);
			}
		}
	}
	
	var tbl = new DataTable();
	
	foreach(var kvp in dictMemberMaps)
	{
		tbl.Columns.Add(kvp.Key);
	}
	
	foreach(var kvp in dictProfileMaps)
	{
		tbl.Columns.Add(kvp.Key);
	}
	
	foreach(var diagnosis in diagnoses)
	{
		tbl.Columns.Add(diagnosis);
	}

	var activeMembers = members.Where(m => m.active == 1);
	var profileData = profiles.ToList();
	
	var memberType = typeof(members);
	var profileType = typeof(profiles);

	foreach(var member in activeMembers)
	{
		var row = tbl.NewRow();
		
		foreach(var kvp in dictMemberMaps)
		{
			MemberInfo mi = memberType.GetField(kvp.Value) as MemberInfo ?? memberType.GetProperty(kvp.Value) as MemberInfo;

			//try
			//{
				var data = mi.GetValue(member).ToString();
				foreach(var map in dictMemberMapTranslations[kvp.Key])
				{
					if(string.Compare(data, map.Key, true) == 0)
						data = map.Value;
				}
				
				row[kvp.Key] = data;
				
			//} catch(Exception ex) { ex.Dump(); }
		}
		
		var profile = profileData.Where(m => m.memberid == member.id).FirstOrDefault();

		if(profile != null)
		{
			foreach(var kvp in dictProfileMaps)
			{
				MemberInfo mi = profileType.GetField(kvp.Value) as MemberInfo ?? profileType.GetProperty(kvp.Value) as MemberInfo;
				
				//try
				//{
					var data = Scrub((mi.GetValue(profile) ?? "").ToString());
					var items = data.Split(",".ToCharArray());
					var dataResults = new List<string>();
					
					foreach(var item in items)
					{
						data = item.Trim();
						
						foreach(var map in dictProfileMapTranslations[kvp.Key])
						{
							if(string.Compare(data, map.Key, true) == 0)
								data = map.Value.Trim();
						}
						
						dataResults.Add(data);
					}
					
					if(kvp.Value != "diagnosed")
					{
						if(dataResults.Count == 1 && string.IsNullOrEmpty(dataResults[0]))
						{
							// NOOP
						}
						else
						{
							row[kvp.Key] = string.Join("::", dataResults);
						}
					}
					else
					{
						foreach(var item in dataResults)
						{
							if(string.IsNullOrEmpty(item))
								continue;
								
							row[item] = "Yes";
						}
					}
					
				//} catch(Exception ex) { ex.Dump(); }
			}
		}
		
		tbl.Rows.Add(row);
	}
	
	tbl.Rows.Dump();
}

// scrub out commas from individual records so it doesn't split them
public string Scrub(string input)
{
	return input.Replace("White, Non-Hispanic", "White/Non-Hispanic")
			.Replace("Yes, full time", "Full Time");
}

SQL to Split a column on comma

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
  FROM table1 t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))
 ORDER BY value

SQL gotten from: http://stackoverflow.com/questions/19073500/sql-split-comma-separated-row