Software library or tool to pivot and export a lot of data to Excel

Question

I'm trying to find a way to get normalized data that's in an MSSQL database out of there, pivoted, and into an Excel file. The main problem is that I have many of those databases, each with the same schema but different data, that also leads to a different set of pivoted columns for each database.

Currently I use SQL Server Reporting Services for this task, but it just breaks down beyond 3000 rows (which isn't entirely unexpected as it's not meant for this task).

Scenario
To explain my requirements let me set the scenario. I'm always exporting entities of -say- type "Person". Each Person should become a row in the final export. I have about of those 250.000 records. This Person record has about 25 "flat" properties, and up to another 20-some pivoted properties. In its final form after pivoting I expect anywhere between 25 and 300 columns.

Basic example of main requirement
To visualize this with a basic, small example, suppose I have this data:

I need a tool or library to pivot that into this:

This is a small example but you probably get the point.

It's not a requirement (or a nice-to-have, tops) to have the first two rows seperate, it's merely in the example for clarity. They can be combined into one row (textually merging things, e.g. "Trait - Bold", etc).

Main requirements
The following things are my main requirements:

• Pivot as above, for about 250.000 Persons max, with about max 20 normalized PropNames with each about 5 to 10 ValNames (counting "Other..." as one) and export into Excel.
• Pivot-columns not known until run-time. I have many DB's with the same schema but with different data in the columns that are to be pivoted.
• Should be live exports that run within seconds.
• Export to XLSX (slightly preferred) or XLS.
• Any solution in the .NET ecosystem would do, including non-gratis options.
• Any solution should be solid and maintainable, e.g. by being testable through integration tests.

I don't mind whether it's a ready-made tool or piece of software, or some software library that I have to wire myself in my own app.

Additional requirements
The following are non-essential but would be a huge pre:

• Localization of things like column headers (e.g. "First Name") in the Excel.
• Being able to style (fonts, borders, backgrounds) the resulting Excel file.
• Being able to leave metadata (export date, etc) in the final Excel.
• Both PropNames and ValNames have ordering, which should result in ordering of the columns.

Things I've considered
Here's a short list of things I've considered or tried:

• Straight up SSRS, obviously. This tool's not up to the amount of pivoting/data involved.
• SSIS packages. The tool seems meant for the job. I do hold a grudge against the tool, but maybe it’s time to get over that. Main worry I have is that SSIS seems to want to know the data to pivot into columns before runtime.
• Dynamic SQL + generated RDLs. Use DynSQL to do the pivoting. This requires generated RDL files because the fields of a query must be known up front to SSRS.
• Dynamic SQL + OPENROWSET + OleDB. Use DynSQL to do the pivoting, and export it straight to Excel using OleDB.
• FOR XML queries into OpenXML. The basic idea: fast FOR XML queries, possibly an XSLT, generating OpenXML data, and plug it in a basic XLSX.
• ORM or ADO.NET into an OpenXML using an XmlWriter. Something along these lines.
• BCP directly into Excel files. Haven’t looked into this yet, but it may be an option.
• SQL CLR. Not sure how this would work (if at all), but there might be options here.

My main question and bottom line is: what would you recommend?

Answer 1

While I haven't done this exact sort of thing myself I would recommend installing Python and Pandas.

This combination can:

• Directly query your database(s)
• Perform pivots with a little care on millions of data entries, see also
• Output to CSV or Excel formats

Better yet it is all free, gratis & open source.

Answer 2

I would try it in Microsoft Access. I might try it in one of the Access alternatives for Linux too, there are a few, though I'm not sure which of them are stable enough. You may have to set it up to operate in chunks. And I would not want to use just one Excel file to hold 250000 records, I would have the code build several in series, which is a natural chunking approach.

appending as requested:

I have done pivoting in Access, a vaguely similar case or two, 50-100K records, not quite your order of magnitude but not too far. It works, but you're adding Excel output which is another layer. I have done the layer but not at major magnitude. One has to be careful how big the dataset one throws at Excel, I recommend adding code to "chunk" the output to create multiple Excel spreadsheets. VBA and SQL both embed nicely into Access, though I don't know if one can get to Excel output that way. I have done that sort of work in VBA.

Answer 3

The hierarchical relationship in your crosstabbed columns (PropName/ValName) isn't really supported by the PIVOT command in SQL Server. An alternative approach is to write the normalized data out to CSV and then use a third-party tool that does support crosstabbing column groups like your PropName and ValName.

One such tool is xtab.py (https://pypi.org/project/xtab/). Disclaimer: I wrote it (when finding myself in the same situation that you are in). If use of Python and the two-step process of export-then-crosstab fits into your workflow, you might give it a try.