Datacrunching: extracting data from Excel file into SQL / compare data with other SQL / export results into Excel

1. DATA CRUNCHING 1. Export a EXcel file with about 15.000 entries, each with 15 columns, into MySQl or whatever database you like.? 2. Make an output in Excel so reporters can work with your results 3. Compare the SQL with an older SQL and merge them where possible 4. Extract questions and answer out of the source file according my instructions and create new data fields for some questions 5. In the end, you will have resorted the Excel-file into different columns and added fieldnames. Then you have to export the results to 6 XLS-files (sorry, but the reporters need to work with them) 2. SOME MACRO STUFF OR PHP STUFF The answers you filed away according to my instructions will get a rating. So Q1.1. gets f.e. 1 point, Q2.2 gets 3 points etc. You will have to add all points and make a ranking according to the one with the highest number of points. 3. MAKE A MYSQL DUMP Then you have to make a dump that's compatible with step 1.3 The project is phased: I need step 1 a.s.a.p, step 2 and 3 in max 2 weeks. There is a 25% bonus if you manage to have the job done in time. See for outline of the complete project: [login to view URL] See for specific instructions of Phase 1: [login to view URL] [login to view URL] [login to view URL] ## Deliverables **PART 1** **? ** Watchinstructions here: [login to view URL] **? ** **PITCH :** Make 3 data sets out of 1 by sorting and comparing with [login to view URL]: need output in 3 Excel Files, even when you hate XLS :) **1 EXCEL FILE-****? ** **TWO TYPES OF HOMES **? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? **ZT** **VV** --? compare with MySQL dump ? V V ??"verpleeghuizen ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? VV-verzorgingstehuizen Resulting in 3files [login to view URL] 2.VV-verpleeghuizen 3.Vv-verzorgingshuizen ? ? **PART 2** Watchinstructions here: [login to view URL] **Sort the columns like this in the output file:** **OE_CODE- OE_NAAM - OE_PLAATS- CONCERN_PLAATS? - CONCERN_NAAM CONCERN_KVK-COMMENT** **PART 3** Watchinstructions here: [login to view URL] **PITCH:** ? Need to extract the answersof? a series of questions, whichwill result in 6 data sets. Output needs to be 6 Excel files, all starting with**OE_CODE- OE_NAAM - OE_PLAATS-CONCERN_PLAATS? - CONCERN_NAAM CONCERN_KVK-COMMENT??" (** How to find thequestions and extract the answers? The questions canbe found under three tabs, **CQ**, **ZI** and **ORG** **CQ-questions** **Check tab CQ field D, “Indicator??.** Each question canhave three categories with each time unique answers.? The three categories are decided by SOORT : iview/pg and [login to view URL] answer can be found in column G, **gecorrigeerdescore**Extract the unique answer for eachcategory like this Q1.1iview Q1.1pgor Q1.1ZT Also include thethree columns **digimv2008_id** ? **sterren**and **pilot**. You have alreadyalready **concern_kvk** and **oecode** from part 2, include them (andany other fields if I forgot some) **ZI-questions** **Check tab ZI field C, “Indicator??** The values youuse for the answers are column E (prestatie_index). Also include the fivecolumns **digimv2008_id** ? **sterren****pilot** **aantal** **onbekend**. Youhave already **concern_kvk** and **oecode** from part 2 This time youdon’t have? a field like **SOORT** as in the CQ-TAB, so you need tomake something up yourself. Look at OECODE 153 This one has 2answers for question 7.2 (C, indicator) and 4 answers for question 4.1. Extractthem like this 1 answer: Q4.1.1. 2 answers [login to view URL] Q4.1.2 3 answers Q4.1.1, Q4.1.2 and Q4.13 4 answers Q4.1.1, Q4.1.2. , Q4.13 and Q4.14 **ORG-questions** **Check tab ZI field C, “Indicator??** Straightforwardstuff, answers are in **waarde**. Pleaseinclude fiels **pilot. If you get 2 ormore values, like OECODE? 4092,file it like this:** **? ** 1 answer: Q8.3.1.? 2 answers Q8.3.1and Q8.3.2 ? etc. Done? Now we need6 files for the reporters: ? Resulting in 3files 1.ZT-CQQuestions 2.ZT-ZIquestions 3.VV-verpleeghuizen-CQ questions 4.VV-verpleeghuizen-ZI questions 5.Vv-verzorgingshuizen-CQ questions 6.VV-verzorginshuizen-ZI questions ? ? **PART 4** **We will give each data field some points and needto calculate the total number of points.** **PART 5** **Export it back to the MySQL database and adapt it.**