3 Replies Latest reply: Mar 27, 2017 4:18 AM by Anat Dagan RSS

    concatenate columns by field name and add fields by file name (if possible)

    Anat Dagan

      I have multiple excel files for students grade scores in various subjects from various schools and various periods . Here is an example for one of the excel files. The names in the grade scores columns are teacher names.

      The subject and level are in the column titles (Math 3, Math 4 etc)


      File name: ABC School Period 1 Year 2016


      StudentIDPeriodClassJohn Smith math 3John Doe math 3Karen El Math 4Jane Doe Math 4Final Math
      11A170 70
      21A2 80 75
      31A4 100
      41A3 9595



      I need to concatenate all tables into one table in the following format:


      StudentIDSchoolNameYearPeriodClassSubjectLevelscoreFinal Math ScoreFinal Math?
      1ABC20161A1MathMath 37070yes
      2ABC20161A2MathMath 38075yes
      3ABC20161A4MathMath 4100 no
      4ABC20161A3MathMath 49595yes


      my challenges:

      1. Is it possible to bring the school name and year from the file name into a table field?

      2. how do I concatenate the score fields based on a partial string in the column header name? (i don't care who is the teacher, i just care about the level (Math 3, Math 4 etc).

      3. The amount of teachers and levels vary from school to school. is there a way to write one general script that will be able to handle all the files?