Biblioteca de software o herramienta para pivotar y exportar una gran cantidad de datos a Excel

Estoy tratando de encontrar una manera de sacar los datos normalizados que están en una base de datos MSSQL, girarlos y colocarlos en un archivo de Excel. El problema principal es que tengo muchas de esas bases de datos, cada una con el mismo esquema pero con datos diferentes, lo que también conduce a un conjunto diferente de columnas pivote para cada base de datos.

Actualmente uso SQL Server Reporting Services para esta tarea, pero solo se divide más allá de las 3000 filas (lo cual no es del todo inesperado ya que no está diseñado para esta tarea).

Escenario
Para explicar mis requisitos, permítanme establecer el escenario. Siempre estoy exportando entidades de -digamos- tipo " Person". Cada persona debe convertirse en una fila en la exportación final. Tengo alrededor de esos 250.000 registros. Este registro de persona tiene alrededor de 25 propiedades "planas" y hasta otras 20 propiedades dinámicas. En su forma final después de girar, espero entre 25 y 300 columnas.

Ejemplo básico de requisito principal
Para visualizar esto con un pequeño ejemplo básico, supongamos que tengo estos datos:

Datos no pivotados

Necesito una herramienta o biblioteca para convertir eso en esto:

Datos pivotados

Este es un pequeño ejemplo, pero probablemente entiendas el punto.

No es un requisito (ni es bueno tenerlo, como mucho) tener las dos primeras filas separadas, está simplemente en el ejemplo para mayor claridad. Se pueden combinar en una fila (fusionando cosas textualmente, por ejemplo, "Rasgo - Negrita", etc.).

Requisitos principales
Las siguientes cosas son mis requisitos principales:

  • Pivote como arriba, durante aproximadamente 250.000 Persons como máximo, con aproximadamente 20 s normalizados como máximo PropNamecon cada uno de 5 a 10 ValNames (contando "Otro ..." como uno) y exporte a Excel .
  • Las columnas dinámicas no se conocen hasta el tiempo de ejecución . Tengo muchos DB con el mismo esquema pero con diferentes datos en las columnas que se van a pivotar.
  • Deben ser exportaciones en vivo que se ejecutan en segundos.
  • Exporte a XLSX (ligeramente preferido) o XLS.
  • Cualquier solución en el ecosistema .NET funcionaría, incluidas las opciones no gratuitas.
  • Cualquier solución debe ser sólida y mantenible , por ejemplo, comprobable a través de pruebas de integración.

No me importa si es una herramienta lista para usar o un software, o alguna biblioteca de software que tengo que conectar yo mismo en mi propia aplicación.

Requisitos adicionales
Los siguientes no son esenciales, pero serían un gran pre:

  • Localización de cosas como encabezados de columna (por ejemplo, "Nombre") en Excel.
  • Ser capaz de diseñar (fuentes, bordes, fondos) el archivo de Excel resultante.
  • Pudiendo dejar metadatos (fecha de exportación, etc) en el Excel final.
  • Tanto PropNames como ValNames tienen ordenación, lo que debería dar como resultado la ordenación de las columnas.

Cosas que he considerado
Aquí hay una breve lista de cosas que he considerado o probado:

  • Directamente SSRS , obviamente. Esta herramienta no está a la altura de la cantidad de pivote/datos involucrados.
  • Paquetes SSIS . La herramienta parece hecha para el trabajo. Le guardo rencor a la herramienta, pero tal vez sea hora de superarlo. La principal preocupación que tengo es que SSIS parece querer saber los datos para pivotar en columnas antes del tiempo de ejecución.
  • RDL dinámicos generados por SQL + . Use DynSQL para hacer el pivote. Esto requiere archivos RDL generados porque los campos de una consulta deben conocerse por adelantado para SSRS.
  • SQL dinámico + OPENROWSET + OleDB . Use DynSQL para hacer el pivote y expórtelo directamente a Excel usando OleDB.
  • PARA consultas XML en OpenXML . La idea básica: consultas FOR XML rápidas, posiblemente un XSLT, generando datos OpenXML, y conéctelo a un XLSX básico.
  • ORM o ADO.NET en un OpenXML usando un XmlWriter . Algo por el estilo.
  • BCP directamente en archivos de Excel . Todavía no lo he investigado, pero puede ser una opción.
  • CLR de SQL . No estoy seguro de cómo funcionaría esto (si es que funciona), pero podría haber opciones aquí.

Mi pregunta principal y la línea de fondo es: ¿qué recomendaría?

¿Qué estás haciendo con estos datos después de su Excel? Si desea formularios e informes, tal vez sea más fácil usar el acceso de MS para generar su salida sin usar Excel.
@cybernard Bueno, para mí personalmente, en ese momento (hace 4 años), era una medida provisional para que nuestros clientes hicieran análisis muy básicos (pequeños gráficos aquí y allá), fusiones de correo, importaciones a otros sistemas y demás. Excel era el objetivo porque nuestros usuarios finales solo eran expertos en software básico (correo electrónico, hojas de cálculo, procesadores de texto) y no en MS Access, Tableau, PowerBI (que aún no existían), etc.

Respuestas (3)

Si bien no he hecho exactamente este tipo de cosas, recomendaría instalar Python y Pandas .

Esta combinación puede:

Mejor aún, todo es gratis, gratis y de código abierto.

Yo lo probaría en Microsoft Access. También podría probarlo en una de las alternativas de Access para Linux, hay algunas, aunque no estoy seguro de cuáles son lo suficientemente estables. Es posible que deba configurarlo para que funcione en fragmentos. Y no me gustaría usar solo un archivo de Excel para contener 250000 registros, haría que el código compilara varios en serie, que es un enfoque de fragmentación natural.

adjuntando lo solicitado:

He hecho pivoting en Access, uno o dos casos vagamente similares, 50-100K registros, no del todo su orden de magnitud pero no demasiado lejos. Funciona, pero está agregando la salida de Excel, que es otra capa. He hecho la capa pero no a mayor magnitud. Uno tiene que tener cuidado con el tamaño del conjunto de datos que arroja en Excel, recomiendo agregar código para "fragmentar" la salida para crear varias hojas de cálculo de Excel. VBA y SQL se integran muy bien en Access, aunque no sé si uno puede llegar a la salida de Excel de esa manera. He hecho ese tipo de trabajo en VBA.

Realmente estaba buscando recomendaciones sobre solo sugerencias. ¿Has probado este enfoque tú mismo? ¿Cómo se compara con los requisitos que establecí? ¿Cuándo recomendaría "alternativas" (y cuáles) sobre MS Access propiamente dicha? ¿Qué quiere decir exactamente con "trozos", sugiere usar una función específica de MS Access para eso?
@JEBofPonderworthy: corte su comentario y péguelo en el cuerpo de su respuesta, ¡gracias! :-)

La relación jerárquica en sus columnas con tabulación cruzada (PropName/ValName) no es realmente compatible con el comando PIVOT en SQL Server. Un enfoque alternativo es escribir los datos normalizados en CSV y luego usar una herramienta de terceros que admita grupos de columnas de tabulación cruzada como PropName y ValName.

Una de esas herramientas es xtab.py ( https://pypi.org/project/xtab/ ). Descargo de responsabilidad: lo escribí (al encontrarme en la misma situación en la que te encuentras). Si el uso de Python y el proceso de dos pasos de exportación y luego tabulación cruzada encajan en su flujo de trabajo, puede intentarlo.

Biblioteca de software o herramienta para pivotar y exportar una gran cantidad de datos a Excel
RespuestasAquíPolítica de privacidad1y, 0v