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

[Anonymous]

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

StudentID	Period	Class	John Smith math 3	John Doe math 3	Karen El Math 4	Jane Doe Math 4	Final Math
1	1	A1	70				70
2	1	A2		80			75
3	1	A4			100
4	1	A3				95	95

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

StudentID	SchoolName	Year	Period	Class	Subject	Level	score	Final Math Score	Final Math?
1	ABC	2016	1	A1	Math	Math 3	70	70	yes
2	ABC	2016	1	A2	Math	Math 3	80	75	yes
3	ABC	2016	1	A4	Math	Math 4	100		no
4	ABC	2016	1	A3	Math	Math 4	95	95	yes

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?

[OmarBenSalem]

Hi anat,

Yes, it's possible.
I have a queston though, are your StudentIDs the same in each excel file? (1,2,3n4 and so?) or each student will have his unique name?

1) Let's assume your Excel files are all under the same folder/path and that all the titles are in the same format:

ABC School Period 1 Year 2016 (1 is school name, 6th word in the year)

here's is how we do to bring up the title and the year:

let vDir='D:\ExcelFiles\'; //your directory

LET vF=;

for each vF in filelist (vDir & '\*.xls' )

   let SchoolName= SubField('$(vF)', ' ',1)  ;

   let vSchoolName= '$(SchoolName)' ;

   let Year=SubField('$(vF)', ' ',6);

let vYear='$(Year)' ;

It begins as follow..
Can you please, provide me 2 excel samples from 2 schools, to work with and continue with my script?

Thanks a lot

[ahaahaaha]

Hi Anat,

The idea of the solution can be seen in the attached file QVF. Next, you should take account the real names of your files of Excel and the paths to them in the file system.

Regards,

Andrey

[Anonymous]

Thanks Omar!

The StudentID is unique (it's their Social Security Number).

I attached an example with 4 sheets representing the excel data i receive .

Each school sends a separate sheet for each period (1, 2), and each class grade (A, B, C) = a total of 6 spreadsheets per school per year.

Students can change teachers between semesters (See studentID 3)