Cleansing Data to be uploaded into Web Database

K

kaylee1982

New Member
Joined
Jul 19, 2012
Messages
1
Hello - I only have very basic excel skills so would appreciate your help.

I run a database which is used by Universities to order items of clothing for their academic staff at graduation ceremonies.

In my database I have over 400 Universities and all of these have their own set of awards/degrees i.e. Bachelors, Masters, PhDs etc.

Universty administrators have th abiltiy to upload spreadsheets with this data into a web database to make their ordres. They could have a spreadhseet with anywhere between 10 and 2000 rows. They match the fields on the web to the columns in the spreadsheet and upload. Sounds simple.

The spreadsheets typically have the following format:

[TABLE="width: 603"]
<colgroup><col style="width: 91pt; mso-width-source: userset; mso-width-alt: 4425;" width="121"> <col style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;" width="48"> <col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;" width="74"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2596;" width="71"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <col style="width: 156pt; mso-width-source: userset; mso-width-alt: 7606;" width="208"> <col style="width: 39pt; mso-width-source: userset; mso-width-alt: 1901;" width="52"> <col style="width: 74pt; mso-width-source: userset; mso-width-alt: 3620;" width="99"> <col style="width: 36pt; mso-width-source: userset; mso-width-alt: 1755;" width="48"> <tbody>[TR]
[TD="class: xl64, width: 121, bgcolor: transparent"]Username[/TD]
[TD="class: xl64, width: 48, bgcolor: transparent"]Title[/TD]
[TD="class: xl64, width: 74, bgcolor: transparent"]First Name[/TD]
[TD="class: xl64, width: 71, bgcolor: transparent"]Last Name[/TD]
[TD="class: xl64, width: 82, bgcolor: transparent"]Department[/TD]
[TD="class: xl64, width: 208, bgcolor: transparent"]Institution at which you studied[/TD]
[TD="class: xl64, width: 52, bgcolor: transparent"]Degree[/TD]
[TD="class: xl64, width: 99, bgcolor: transparent"]Year of Degree[/TD]
[TD="class: xl64, width: 48, bgcolor: transparent"]Height[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk[/TD]
[TD="class: xl66, bgcolor: transparent"]Mr.[/TD]
[TD="class: xl66, bgcolor: transparent"]John[/TD]
[TD="class: xl66, bgcolor: transparent"]Brown[/TD]
[TD="class: xl66, bgcolor: transparent"]English[/TD]
[TD="class: xl66, bgcolor: transparent"]Birmingham University[/TD]
[TD="class: xl66, bgcolor: transparent"]BA[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1985[/TD]
[TD="class: xl66, bgcolor: transparent"]6' 0"[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk[/TD]
[TD="class: xl66, bgcolor: transparent"]Ms.[/TD]
[TD="class: xl66, bgcolor: transparent"]Charlotte[/TD]
[TD="class: xl66, bgcolor: transparent"]Green[/TD]
[TD="class: xl66, bgcolor: transparent"]Science[/TD]
[TD="class: xl66, bgcolor: transparent"]University of Exeter[/TD]
[TD="class: xl66, bgcolor: transparent"]BSc[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1975[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 4"[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk[/TD]
[TD="class: xl66, bgcolor: transparent"]Prof.[/TD]
[TD="class: xl66, bgcolor: transparent"]Jenny[/TD]
[TD="class: xl66, bgcolor: transparent"]Black[/TD]
[TD="class: xl66, bgcolor: transparent"]Law[/TD]
[TD="class: xl66, bgcolor: transparent"]Yale[/TD]
[TD="class: xl66, bgcolor: transparent"]LLM[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1999[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 2"[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk[/TD]
[TD="class: xl66, bgcolor: transparent"]Dr.[/TD]
[TD="class: xl66, bgcolor: transparent"]Bob[/TD]
[TD="class: xl66, bgcolor: transparent"]White[/TD]
[TD="class: xl66, bgcolor: transparent"]Languages[/TD]
[TD="class: xl66, bgcolor: transparent"]Queen Mary London[/TD]
[TD="class: xl66, bgcolor: transparent"]PhD[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2011[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 10"[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk[/TD]
[TD="class: xl66, bgcolor: transparent"]Mr.[/TD]
[TD="class: xl66, bgcolor: transparent"]Harry[/TD]
[TD="class: xl66, bgcolor: transparent"]Red[/TD]
[TD="class: xl66, bgcolor: transparent"]English[/TD]
[TD="class: xl66, bgcolor: transparent"]Cambridge University[/TD]
[TD="class: xl66, bgcolor: transparent"]MA[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2005[/TD]
[TD="class: xl66, bgcolor: transparent"]5'11"[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk[/TD]
[TD="class: xl66, bgcolor: transparent"]Dr.[/TD]
[TD="class: xl66, bgcolor: transparent"]James[/TD]
[TD="class: xl66, bgcolor: transparent"]Grey[/TD]
[TD="class: xl66, bgcolor: transparent"]Science[/TD]
[TD="class: xl66, bgcolor: transparent"]University of Durham[/TD]
[TD="class: xl66, bgcolor: transparent"]PDPhil[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2003[/TD]
[TD="class: xl66, bgcolor: transparent"]6' 2"[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk[/TD]
[TD="class: xl66, bgcolor: transparent"]Mrs.[/TD]
[TD="class: xl66, bgcolor: transparent"]Debby[/TD]
[TD="class: xl66, bgcolor: transparent"]Blue[/TD]
[TD="class: xl66, bgcolor: transparent"]Law[/TD]
[TD="class: xl66, bgcolor: transparent"]Oxford[/TD]
[TD="class: xl66, bgcolor: transparent"]LLM[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1984[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 7"[/TD]
[/TR]
</tbody>[/TABLE]


Some of the data uploads fine...things like Title, name, email address etc. However the items where it is trying to match what is uploaded to items in the database are proving very difficult.

For example. The University of Birmingham can be expressed on the spreadsheet as: 'The University of Birmingham' 'University of Birmingham' 'Birmingham University' ' Birmingham'.

At the moment unless the person creating the spreadsheet puts a value that is exactly the same as what is held in the database the data will not upload and you end up checking the dtatabase against every entry on the spreadsheet. By the time you have done this you may as well have entered the data manually row by row! :( It is made even worse when uploading the degree because each University has its own way of expressing each degree class. i.e. some will have BA other will say All bachelors.

I would ideally like to create a spreadsheet where the adminstrator can put their data in and press a button or a macro which would cleanse this data in some way or show which data/entries would need to be changed prior to upload. I have no idea how to do this though. I have had a play with data validation but I am not skilled enough to know if this is the right thing to do.

I cannot find any topics on the web which explain in plain english how complex data can be successfully uploaded into a database and I am spending days and days manually cleansing data for people to save them the agony.

I am wondering if any of you intelligent folks can offer any suggestions.

Thanks

Kaylee
 
Sort by date Sort by votes
Hi Kaylee,

A technique I sometimes use to do this is to create a "Dictionary" in the contaminated workbook. The dictionary is simply an added worksheet (can be hidden) containing a list of what to find in column A and what to replace the found value with in column B.

You will have to adjust the code sample below to use the correct sheet names and columns. Please see the code comments.

The order that you list the words can be important as well as the length (shortest to longest etc). Leading and trailing spaces may also be necessary in the word list to get 100% results.

Example:

Find Replace

Univ University (no space after Univ)

Birmingham UnivUniversity

You could use a trailing space in the find word to avoid the above problem or add another entry in the list to replace UnivUniversity with University. Of course adding extra entries to undo undesired changes will take longer.

Hope it helps.

Gary


Code: 
Public Sub Test()

Dim sFind As String
Dim sReplace As String
Dim oDictionary As Worksheet
Dim oData As Worksheet
Dim oWordList As Range
Dim oCell As Range

Dim oSearch As Range

Set oData = ThisWorkbook.Worksheets("Data") ' Change "Data" to name of sheet to be searched

'Create worksheet named "Dictionary" list words or phrases to be changed in column A
'list replacement word or phrase in column B
Set oDictionary = ThisWorkbook.Worksheets("Dictionary")
Set oWordList = oDictionary.Range("A1:A" & oDictionary.Range("A" & Rows.Count).End(xlUp).Row)

Set oSearch = ThisWorkbook.Worksheets("Data").Range("F:F") ' Change F:F to desired column

For Each oCell In oWordList

    sFind = oCell.Text
    sReplace = oCell.Offset(0, 1).Text

    oSearch.Replace what:=sFind, replacement:=sReplace, Lookat:=xlPart, MatchCase:=False
    
Next oCell

End Sub
 
Upvote 0
kaylee1982 said:
Hello - I only have very basic excel skills so would appreciate your help.

I run a database which is used by Universities to order items of clothing for their academic staff at graduation ceremonies.

In my database I have over 400 Universities and all of these have their own set of awards/degrees i.e. Bachelors, Masters, PhDs etc.

Universty administrators have th abiltiy to upload spreadsheets with this data into a web database to make their ordres. They could have a spreadhseet with anywhere between 10 and 2000 rows. They match the fields on the web to the columns in the spreadsheet and upload. Sounds simple.

The spreadsheets typically have the following format:

[TABLE="width: 603"]
<tbody>[TR]
[TD="class: xl64, width: 121, bgcolor: transparent"]Username
[/TD]
[TD="class: xl64, width: 48, bgcolor: transparent"]Title
[/TD]
[TD="class: xl64, width: 74, bgcolor: transparent"]First Name
[/TD]
[TD="class: xl64, width: 71, bgcolor: transparent"]Last Name
[/TD]
[TD="class: xl64, width: 82, bgcolor: transparent"]Department
[/TD]
[TD="class: xl64, width: 208, bgcolor: transparent"]Institution at which you studied
[/TD]
[TD="class: xl64, width: 52, bgcolor: transparent"]Degree
[/TD]
[TD="class: xl64, width: 99, bgcolor: transparent"]Year of Degree
[/TD]
[TD="class: xl64, width: 48, bgcolor: transparent"]Height
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mr.
[/TD]
[TD="class: xl66, bgcolor: transparent"]John
[/TD]
[TD="class: xl66, bgcolor: transparent"]Brown
[/TD]
[TD="class: xl66, bgcolor: transparent"]English
[/TD]
[TD="class: xl66, bgcolor: transparent"]Birmingham University
[/TD]
[TD="class: xl66, bgcolor: transparent"]BA
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1985
[/TD]
[TD="class: xl66, bgcolor: transparent"]6' 0"
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk
[/TD]
[TD="class: xl66, bgcolor: transparent"]Ms.
[/TD]
[TD="class: xl66, bgcolor: transparent"]Charlotte
[/TD]
[TD="class: xl66, bgcolor: transparent"]Green
[/TD]
[TD="class: xl66, bgcolor: transparent"]Science
[/TD]
[TD="class: xl66, bgcolor: transparent"]University of Exeter
[/TD]
[TD="class: xl66, bgcolor: transparent"]BSc
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1975
[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 4"
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk
[/TD]
[TD="class: xl66, bgcolor: transparent"]Prof.
[/TD]
[TD="class: xl66, bgcolor: transparent"]Jenny
[/TD]
[TD="class: xl66, bgcolor: transparent"]Black
[/TD]
[TD="class: xl66, bgcolor: transparent"]Law
[/TD]
[TD="class: xl66, bgcolor: transparent"]Yale
[/TD]
[TD="class: xl66, bgcolor: transparent"]LLM
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1999
[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 2"
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk
[/TD]
[TD="class: xl66, bgcolor: transparent"]Dr.
[/TD]
[TD="class: xl66, bgcolor: transparent"]Bob
[/TD]
[TD="class: xl66, bgcolor: transparent"]White
[/TD]
[TD="class: xl66, bgcolor: transparent"]Languages
[/TD]
[TD="class: xl66, bgcolor: transparent"]Queen Mary London
[/TD]
[TD="class: xl66, bgcolor: transparent"]PhD
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2011
[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 10"
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mr.
[/TD]
[TD="class: xl66, bgcolor: transparent"]Harry
[/TD]
[TD="class: xl66, bgcolor: transparent"]Red
[/TD]
[TD="class: xl66, bgcolor: transparent"]English
[/TD]
[TD="class: xl66, bgcolor: transparent"]Cambridge University
[/TD]
[TD="class: xl66, bgcolor: transparent"]MA
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2005
[/TD]
[TD="class: xl66, bgcolor: transparent"]5'11"
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk
[/TD]
[TD="class: xl66, bgcolor: transparent"]Dr.
[/TD]
[TD="class: xl66, bgcolor: transparent"]James
[/TD]
[TD="class: xl66, bgcolor: transparent"]Grey
[/TD]
[TD="class: xl66, bgcolor: transparent"]Science
[/TD]
[TD="class: xl66, bgcolor: transparent"]University of Durham
[/TD]
[TD="class: xl66, bgcolor: transparent"]PDPhil
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]2003
[/TD]
[TD="class: xl66, bgcolor: transparent"]6' 2"
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]staff1@staff.ac.uk
[/TD]
[TD="class: xl66, bgcolor: transparent"]Mrs.
[/TD]
[TD="class: xl66, bgcolor: transparent"]Debby
[/TD]
[TD="class: xl66, bgcolor: transparent"]Blue
[/TD]
[TD="class: xl66, bgcolor: transparent"]Law
[/TD]
[TD="class: xl66, bgcolor: transparent"]Oxford
[/TD]
[TD="class: xl66, bgcolor: transparent"]LLM
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]1984
[/TD]
[TD="class: xl66, bgcolor: transparent"]5' 7"
[/TD]
[/TR]
</tbody>[/TABLE]


Some of the data uploads fine...things like Title, name, email address etc. However the items where it is trying to match what is uploaded to items in the database are proving very difficult.

For example. The University of Birmingham can be expressed on the spreadsheet as: 'The University of Birmingham' 'University of Birmingham' 'Birmingham University' ' Birmingham'.

At the moment unless the person creating the spreadsheet puts a value that is exactly the same as what is held in the database the data will not upload and you end up checking the dtatabase against every entry on the spreadsheet. By the time you have done this you may as well have entered the data manually row by row! :( It is made even worse when uploading the degree because each University has its own way of expressing each degree class. i.e. some will have BA other will say All bachelors.

I would ideally like to create a spreadsheet where the adminstrator can put their data in and press a button or a macro which would cleanse this data in some way or show which data/entries would need to be changed prior to upload. I have no idea how to do this though. I have had a play with data validation but I am not skilled enough to know if this is the right thing to do.

I cannot find any topics on the web which explain in plain english how complex data can be successfully uploaded into a database and I am spending days and days manually cleansing data for people to save them the agony.

I am wondering if any of you intelligent folks can offer any suggestions.

Thanks

Kaylee
Click to expand...
Try the add in fuzzy lookup for excel from Microsoft http://www.microsoft.com/en-us/download/details.aspx?id=15011
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
A
  • Question Question
VBA Run-time error '1004'
2
Replies
11
Views
2K
JustynaMK
JustynaMK
L
  • Question Question
VBA - Consolidate data and Sum mulitiple ranges
Replies
1
Views
2K
MickG
M
S
  • Question Question
Locating duplicate rows in Excel
Replies
1
Views
595
JoeMo
J

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top