8

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:

Unpivoted data

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

Pivoted data

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?

2
  • What are you doing with this data after its in excel? If you want forms and reports it maybe easier just to use MS access to generate your output without using excel.
    – cybernard
    Jun 5, 2018 at 2:30
  • @cybernard Well, for me personally, at the time (4 years ago), it was a stopgap measure for our customers to do very basic analytics (little graph here and there), mail merges, imports into other systems, and whatnot. Excel was the target because our end users were only skilled in basic software (e-mail, spreadsheets, word processors) and not MS Access, Tableau, PowerBI (didn't exist back then yet), etc.
    – Jeroen
    Jun 5, 2018 at 7:45

3 Answers 3

1

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

This combination can:

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

0

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.

2
  • 2
    Was really looking for recommendations over just suggestions. Have you tried this approach yourself? How does it stack up against the requirements I laid out? When would you recommend "alternatives" (and which ones) over MS Access proper? What do you mean exactly by "chunks", do you suggest using a specific MS Access feature for that?
    – Jeroen
    Jan 26, 2018 at 16:46
  • @JEBofPonderworthy: Please cut your comment and paste it to the body of your answer, thanks! :-)
    – Nicolas Raoul
    Jan 29, 2018 at 10:21
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.