Pivoting multiple files into one

[FredPereira]

Hi everyone,

I am facing a challenge right now.

I need to combine various CSV into one database. The "catch" is:

1. The files have the columns as rows;

2. The files may have different number of rows (columns).

Here is an example below:

File1

Agent Name,xpto1

Agent Email,xpto1@abcd.com

Date,04/18/2019 14:58:25

Time Zone,Europe/Lisbon

Time Filter Begin,04/17/2019 00:00:00

Time Filter End,04/17/2019 23:00:00

Total Unavailable Time (hh:mm:ss),00:00:00

Total Offline Time (hh:mm:ss),00:47:50

File2

Agent Name,xpto2

Agent Email,xpto2@abcd.com

Date,04/18/2019 14:58:25

Time Zone,Europe/Lisbon

Time Filter Begin,04/17/2019 00:00:00

Time Filter End,04/17/2019 23:00:00

Total Auto Answered Calls Notifications Offered,27

Total Number of Calls Notification Offered,42

Total Auto Answered Calls Connected,15

Total Number of Calls Connected,15

Total Number of Multicast Call Notifications Offered,15

Total Unavailable Time (hh:mm:ss),00:02:32

Total Offline Time (hh:mm:ss),14:54:48

Total Wrap-up Time (hh:mm:ss),01:40:37

Total Email Time (hh:mm:ss),00:42:50

Total In-call Time (hh:mm:ss),03:57:42

Total Available Time (hh:mm:ss),01:04:56

Total Meal Time (hh:mm:ss),01:00:59

Total Break Time (hh:mm:ss),00:35:39

Basically I need to combine a hundred (give or take) of these files in a database format

Column 1, Column2, etc, with the correspondent values below each column, with nulls/zeros in case some file does not have a row that other may have.

Any clue how to do this?

I've tried to pivot all the files after merging but I am getting an error:

"Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
List"

Thanks in advance!

[parry2k · Accepted solution]

@FredPereira See attached, I have created 2 sample tables file1 and file2, look at file1 which is pivoted. Key here is to add unique identifier for each table before pivot. I added index column in both files which work as unique identifier, you can call that index column anything, with any value but it need to be unique for each file.

[FredPereira]

Forgot to mention an important detail.

I am linked to a folder, so I am merging all the files that way. So everyday, around 50 files are downloaded and inserted in said folder everyday.

The idea is to have my database updating automatically just by adding files to the folder.

Will the index column be feasible in this scenario?

Thank you very much for the response!

[parry2k]

@FredPereira yes it is even better, basically the way you want to think is to have a column with unique value in it for each file, in case of folder, you can surely have filename as unique or create one. I think it is easier and solution I provided would work.