Exportación de datos: De consulta SQL a hoja de cálculo

¿Cómo exportar datos de una base de datos SQL a Excel u otra hoja de cálculo? En este artículo, examinaré varios métodos y compartiré algunos consejos para cuando las cosas vayan mal.

SQL es el método más utilizado para extraer datos de una base de datos. Sin embargo, no es el único artilugio de la caja de herramientas de un analista. Una vez extraída la información, a menudo querrás importarla a una hoja de cálculo, ya sea para realizar un análisis más detallado o simplemente para darle un aspecto más atractivo y poder presentársela a la dirección. SQL extrae los datos en forma de texto sin formato, así que ¿cómo puedes introducirlos en una hoja de cálculo?

Como analista de datos, necesitarás conocimientos tanto de hoja de cálculo como de SQL. Las hojas de cálculo se utilizan mucho y se enseñan en las escuelas, así que daré por hecho que ya sabes utilizarlas.

Si aún no eres un experto en SQL, quizá te interese leer La mejor forma de aprender SQL, que te dará algunas ideas sobre cómo empezar. También te recomiendo LearnSQL.es's SQL para el análisis de datos programa. Especialmente diseñado para analistas de datos, este itinerario consta de cuatro cursos que te llevarán desde el nivel de principiante hasta el de experto en análisis de datos con SQL. Se tarda unas 48 horas en completarlo, y te enseñará a resolver el tipo de problemas que te encontrarás en el trabajo de análisis.

Si ya conoce un poco de SQL pero le gustaría obtener un poco de ayuda, aquí tiene una hoja de trucos gratuita de SQL para el análisis de datos.

Informes SQL con Excel

En este artículo, me centraré principalmente en cómo exportar datos SQL a hojas de cálculo, pero antes me gustaría dedicar unos minutos a ver por qué querrías hacerlo. Veamos algunos escenarios:

Trabajas en marketing

Has utilizado SQL para extraer datos demográficos y saber qué tipo de productos han comprado los clientes de distintas zonas y grupos de edad en los últimos dos años. Ahora tienes que hacer una presentación con gráficos y tablas para justificar la campaña que tienes en mente para el año que viene.

Puedes utilizar una hoja de cálculo para crear estos gráficos y tablas y darles un aspecto profesional. Las hojas de cálculo disponen de una serie de herramientas útiles para los analistas de datos, entre las que se incluyen:

• Tablas y gráficos.
• Tablas dinámicas.
• Mapas dinámicos.
• Ordenación y filtrado.
• Funciones que realizan cálculos complejos.

Trabaja en un banco

Sospechas que se ha producido un fraude. Has utilizado SQL para obtener detalles de las transacciones sospechosas, pero necesitas experimentar con el filtrado y la clasificación de las transacciones y compararlas con otros eventos durante el período. Para ello, probablemente utilizaría una hoja de cálculo, ya que simplifica el examen de los datos de muchas maneras diferentes.

Tiene que presentar un informe a su jefe

Cada mes, tiene que crear un informe que muestre las ventas totales realizadas a cada cliente. Su consulta SQL lo genera:

Sin embargo, le gustaría formatearlo en un informe atractivo para su gerente, como éste:

Siempre habrá demanda de sus habilidades si puede trabajar con datos de hojas de cálculo de Excel, importar datos de SQL y producir informes atractivos y significativos.

Exportación de datos SQL a hojas de cálculo

Bien, pasemos al punto del artículo: cómo pasar los datos de una consulta SQL a una hoja de cálculo. Existen muchos proveedores de bases de datos, cada uno con un entorno de trabajo ligeramente diferente, y muchos tipos distintos de hojas de cálculo. Esto hace que sea difícil escribir una guía completa de exportación de consultas SQL, pero te mostraré cómo utilizar tres bases de datos populares ...

• Google BigQuery,
• Microsoft SQL Server, y
• MySQL

... con tres hojas de cálculo diferentes:

• Excel,
• Libre Calc de la suite Libre Office, y
• Google Sheets.

También asumo que estás en un PC con Windows. Los usuarios de Linux y Mac tendrán que utilizar los equivalentes al botón derecho del ratón y los atajos de teclado de Crtl+.

Veré varios métodos y cómo se pueden utilizar con el software que he mencionado. Aprenderás lo suficiente para exportar e importar datos en cualquier lugar, ya que la mayoría de las bases de datos y hojas de cálculo son muy similares.

Mostraré ejemplos utilizando los datos públicos de BigQuery, la base de datos de muestra AdventureWorks de Microsoft y la base de datos de muestra Employee de MySQL.

Comenzaré con el método más simple.

Método 1: Copiar y pegar

Esta es la forma más rápida de exportar una consulta SQL a una hoja de cálculo, y la utilizo el 99% de las veces. Obviamente, primero tienes que ejecutar una consulta SQL para proporcionar los datos.

No deberías utilizar este método para conjuntos de datos muy grandes, ya que tiene que almacenar los datos del portapapeles en la memoria. Es posible que se congele el ordenador si el conjunto de datos es demasiado grande. Más adelante en este artículo, voy a ver algunos de los problemas que puede encontrar con este método, y cómo resolverlos.

En la imagen siguiente, he escrito una consulta para extraer todos los clientes que cumplen años en enero. El departamento de relaciones con los clientes puede utilizarla para enviarles correos electrónicos de cumpleaños. Estoy utilizando SQL Server Management Studio para ejecutar la consulta, y éste es el resultado:

Puede copiar datos de dos formas. En primer lugar, resalte los datos. A continuación, puede hacer clic en Editar → Copiar en el menú, o puede utilizar el método abreviado de teclado pulsando CTRL+C.

Esto copia las filas que ha resaltado, más los encabezados, como datos separados por tabulaciones en el portapapeles.

Otra opción, si quieres copiar todas las filas en tu hoja de cálculo, es hacer clic en el cuadrado en blanco de la parte superior, como indica la flecha roja en la imagen de abajo. Se resaltarán todas las filas; a continuación, puede copiar los datos en el portapapeles.

Si hace clic con el botón derecho del ratón en el mismo cuadrado en blanco, aparecerá un menú que le dará la opción de copiar con o sin encabezados.

Copiar datos de MySQL

En la siguiente captura de pantalla, puede ver mi consulta para extraer todos los empleados que tendrán más de 65 años a principios de 2024; la empresa puede utilizar esta información para considerar la jubilación de determinados empleados. Estoy utilizando MySQL Workbench para ejecutar la consulta.

De nuevo, hay varias formas de copiar al portapapeles: Edición → Copiar del menú o CTRL+C. Esto copia los datos sin encabezados y delimitados por tabulaciones.

Si haces clic en la celda en blanco de la parte superior indicada en la imagen inferior, seleccionará todas las filas. Si luego lo copias en el portapapeles, incluirá los encabezados.

Copiar datos de BigQuery

En la siguiente captura de pantalla, he escrito una consulta para extraer una lista de bancos registrados en la FDIC. Se trata de una base de datos pública de BigQuery. Estoy utilizando BigQuery Studio para ejecutar la consulta.

Aunque en teoría se pueden resaltar las filas y utilizar CTRL+C o la función de portapapeles del navegador, esto no funciona bien en la práctica: las columnas tienden a desincronizarse. La única forma correcta de copiar en el portapapeles es utilizar la función Guardar resultados que aparece resaltada en la imagen siguiente:

Aparece un menú:

Si elige Copiar al portapapeles, copiará los datos delimitados por tabulaciones con encabezados. Nota: Sólo puede copiar 1 MB de datos de esta manera.

Pegar en Excel

Una simple operación de Pegar - utilizando el icono del portapapeles en el menú Inicio o tecleando CTRL+V - lleva los datos a Excel, comenzando en la posición del cursor. Normalmente tendrás que dar formato a los datos para que queden bien: ampliar las columnas, añadir títulos significativos, etc.

Si los datos no aparecen como esperabas, hay una sección al final de este artículo sobre cómo solucionar problemas comunes al exportar datos SQL a hojas de cálculo.

Cómo pegar en Libre Calc

En Libre Calc, la operación de pegado se inicia tecleando CTRL+V o eligiendo Edición → Pegar en el menú Inicio. A continuación, el programa le permite establecer algunas opciones para controlar cómo se importan los datos. Aparecerá un menú emergente:

Si simplemente hace clic en Aceptar, utilizará los valores predeterminados para importar los datos. Esto suele funcionar bien, pero puede que tengas que configurar algunas opciones si no es así.

El cambio más común sería establecer el tipo de datos de una columna que se está importando incorrectamente. Para ello, haga clic en la columna en la ventana de vista previa de datos, que seleccionará la columna. A continuación, puede establecer el tipo de datos haciendo clic en el cuadro Tipo de columna situado justo encima de la sección de vista previa y seleccionando un tipo de datos:

A veces, por razones mejor conocidas por los gremlins de bytes ocultos en todos los ordenadores, este cuadro de diálogo no aparece y los datos se pegan utilizando los valores por defecto. Normalmente no pasa nada. Pero si necesita configurar alguna de las opciones, puede forzar la aparición del cuadro de diálogo eligiendo Edición → Pegado especial → Pegado especial en el menú Inicio :

Esto hará que aparezca otro menú:

Elija Usar diálogo de importación de texto. Aparecerá el mismo cuadro de diálogo que hemos visto antes.

Cómo pegar en Google Sheets

En las Hojas de cálculo de Google, basta con colocar el cursor y elegir Edición → Pegar o pulsar CTRL+V. No hay opciones que te permitan controlar cómo se pegan los datos; simplemente utiliza sus propios valores predeterminados.

Método 2: Utilizar archivos CSV para transferir datos

Los archivos CSV (valores separados por comas) son una forma popular de transferir datos de una aplicación a otra.

Los utilizarías con preferencia a copiar y pegar cuando

• Los datos son demasiado grandes para caber en el portapapeles. Normalmente, no querrás utilizar copiar y pegar para más de diez mil filas.
• La hoja de cálculo se encuentra en una máquina distinta de la que se utiliza para extraer los datos.
• Desea distribuir los datos para que otra persona pueda importarlos a su propia hoja de cálculo.

Las columnas del resultado de la consulta estarán separadas por comas en el archivo CSV.

Extracción de un archivo CSV desde SQL Server

En la ventana de resultados de la consulta, haga clic con el botón derecho del ratón en la celda en blanco de la parte superior izquierda de los datos. Aparecerá el siguiente menú.

Seleccione Guardar resultados como. A continuación, aparecerá un cuadro de diálogo estándar para que pueda asignar un nombre al archivo CSV y navegar hasta el lugar donde desea almacenarlo.

Cómo extraer un archivo CSV de MySQL

Justo encima de la ventana de resultados de la consulta, hay una opción para exportar los resultados a un archivo externo. Esta opción aparece resaltada en la siguiente imagen.

Si hace clic en esta opción, aparecerá el cuadro de diálogo Guardar archivo, que le permite asignar un nombre al archivo y navegar hasta el lugar donde desea almacenarlo.

Extracción de un archivo CSV de BigQuery

En BigQuery Studio, hay un botón Guardar resultados justo encima de la ventana de resultados. Si hace clic en él, aparecerá el siguiente menú:

A continuación, puede elegir entre:

• Archivo local CSV para guardarlo en su ordenador. Se abrirá el cuadro de diálogo Guardar archivo habitual.
• CSV(Google Drive). Se guardará en Google Drive, utilizando (en mi opinión) un nombre de archivo poco amigable. Por suerte, aparece un enlace como el de la imagen de abajo. Si haces clic en el archivo, podrás hacer cosas útiles con él, como cambiarle el nombre, compartirlo o abrirlo con Google Sheets.

Abrir un archivo CSV en Excel

Puedes abrir un archivo CSV desde el menú Archivo → Abrir. Cuando aparezca el cuadro de diálogo Abrir archivo, busque el lugar donde guardó el archivo. En algunas versiones de Excel, los archivos CSV aparecerán automáticamente en la lista. En otras, tendrás que configurarlo para que muestre Todos los archivos, como verás en la imagen siguiente:

A continuación, puedes seleccionar el archivo y abrirlo.

Probablemente tendrás que darle algún formato para que se vea bien. Utilice siempre Guardar como para guardarlo como un libro de Excel, porque los archivos CSV no pueden incluir ningún formato.

Abrir un archivo CSV en Libre Calc

En Libre Calc, elija Archivo → Abrir y navegue hasta donde guardó el archivo. Libre Calc muestra automáticamente todos los archivos, por lo que no debería tener problemas para encontrarlo.

Una vez que hayas seleccionado el archivo y lo hayas abierto, Libre Calc mostrará el mismo cuadro de diálogo de importación de texto que ya has visto al pegar los resultados de la consulta.

Abrir un archivo CSV en Google Sheets

Abra una nueva hoja en blanco y, a continuación, seleccione Archivo → Importar.

De este modo, podrás elegir de dónde quieres importar el archivo:

• Si guardaste el archivo en tu Google Drive, elige Mi Drive y selecciona el archivo que quieras.
• Si el archivo está en su ordenador, elija Cargar. Esto le permitirá arrastrar el archivo a la ventana. O puede elegir Examinar para ir al cuadro de diálogo Abrir archivo.

Entonces verá las siguientes opciones:

Normalmente, puede simplemente hacer clic en Importar Datos y utilizar la configuración por defecto. Pero puede que desee cambiar la opción Reemplazar Hoja de Cálculo. Al hacer clic en ella, obtendrá varias opciones útiles.

Si desmarca Convertir texto en números, fechas y fórmulas, todos los datos se importarán como texto. Esto puede ser útil, ya que a veces las fechas y los campos de texto que contienen números pueden aparecer incorrectamente. Por ejemplo, un banco puede tener un número de cuenta numérico de 16 dígitos. Dado que Google Sheets tiene un límite de 15 dígitos significativos en los campos numéricos, el número de cuenta puede verse corrompido por el redondeo.

Método 3: Funciones de exportación en software RDBMS

La mayoría de los sistemas de gestión de bases de datos relacionales (RDBMS) incluyen funciones para exportar los resultados de las consultas. Veamos brevemente algunas de ellas.

Servidor SQL

SQL Server tiene dos utilidades útiles para la exportación de SQL a Excel. Ambas son bastante complicadas, por lo que me limitaré a describir lo que hacen y a proporcionar enlaces a la documentación pertinente de Microsoft.

• Programa de copiamasiva (BCP). El programa de copia masiva está diseñado para copiar datos de SQL Server en varios formatos. Puede utilizarlo para crear archivos CSV. Y como es posible programarlo para que se ejecute con regularidad, es una buena forma de automatizar SQL a Excel o a cualquier otra hoja de cálculo. Puede aprender a utilizar BCP aquí.
• Asistente de importación y exportación de SQL Server. Puede exportar los resultados de una consulta en varios formatos, incluido Excel. Puede aprender a utilizar el asistente para exportar desde la base de datos SQL a Excel aquí. Dado que Libre Calc y Google Sheets pueden abrir archivos de Excel, también puede utilizar este método para exportar a esos programas.

MySQL

En la sección anterior, vimos cómo utilizar MySQL Workbench para dar salida a los datos como un archivo CSV. La opción Exportar conjunto de resultados a un archivo externo también se puede utilizar para generar otros tipos de archivos.

Como puede ver, una de las opciones que tiene es exportar a una hoja de cálculo Excel. Esto exporta los datos como un archivo XML de hoja de cálculo, que se importa fácilmente y con precisión en Excel. Libre Office también abrirá correctamente los archivos exportados de esta manera, pero Google Sheets no lo hará.

BigQuery

El botón Guardar resultado en BigQuery se puede utilizar para exportar los datos directamente a Google Sheets, como se puede ver en las opciones de abajo. Esta es la forma más sencilla y eficaz de importar datos de BigQuery a Google Sheets. Dado que Google Sheets permite exportar a Excel y Libre Calc puede abrir hojas de cálculo de Excel, también puede utilizar este método para llevar sus datos a cualquiera de estos paquetes.

Método 4: Utilizar otras herramientas de análisis de datos para crear hojas de cálculo

Existen muchas herramientas de análisis de datos en el mercado y casi todas ellas le permiten utilizar SQL para extraer datos de una base de datos. Varias también tienen la opción de guardar los resultados como una hoja de cálculo. Dado que la mayoría de las herramientas pueden programar informes con regularidad, pueden constituir una buena forma de producir automáticamente hojas de cálculo cuando sea necesario. Algunas de las herramientas que permiten extraer datos con SQL y guardarlos como hojas de cálculo son:

• Tableau
• Crystal Reports
• Power BI
• Zoho Analytics
• Jasper Reports

Problemas comunes al exportar SQL a hojas de cálculo

La mayoría de las veces, sus datos se importarán correctamente a hojas de cálculo, pero ocasionalmente se encontrará con problemas. He aquí algunos problemas comunes y sus soluciones.

Todos los datos aparecen en una sola columna

Pega los resultados en Excel. Pero en lugar de que cada campo esté en una columna separada, todos aparecen en una sola columna. Esto suele ocurrir cuando Excel "recuerda" (de una operación de pegado anterior) que debe utilizar algo distinto de tabuladores para los delimitadores. Los datos pueden tener este aspecto:

Para solucionarlo, vaya a la pestaña Datos de la cinta de opciones. Asegúrese de que la opción Elegir texto en columnas está seleccionada:

En el cuadro emergente, elija Delimitado y, a continuación, haga clic en Siguiente. En el siguiente cuadro de diálogo, marque Tabulaciones en Delimitadores y, a continuación, Siguiente. Ahora tendrás la oportunidad de elegir los tipos de datos para cada columna.

Si necesitas cambiar alguno de ellos, haz clic en la columna y selecciona su tipo en los botones de opción de la parte superior. Esto es especialmente útil si quieres que las fechas aparezcan en un formato diferente o que los números se traten como texto.

Si esto te sigue ocurriendo, ¿qué puedes hacer? La próxima vez que quieras pegar datos, haz clic en la flecha situada bajo el botón Pegar. Te dará la opción de utilizar el asistente de importación de texto, es decir, los cuadros de diálogo que vimos en el párrafo anterior. Esto te permitirá elegir Tabulador como delimitador; Excel "recordará" esta elección para futuras operaciones de pegado.

Las fechas están al revés

Esto puede ocurrir al pegar o importar desde un CSV. A veces es sólo un problema de formato: puedes seleccionar la columna y utilizar el comando Formato para que las fechas tengan el aspecto que deseas.

Pero a veces puede ser más grave. El formato de fecha abreviado estadounidense, por ejemplo, es mm/dd/aaaa, mientras que el formato de fecha abreviado británico es dd/mm/aaaa. Si tu formato de fecha por defecto en la hoja de cálculo es distinto del formato de tu aplicación de base de datos, podrías acabar interpretando una fecha que debería representar el 4 de enero como el 1 de abril porque los días y los meses están intercambiados.

La mejor forma de evitarlo es aplicar algún formato en SQL para asegurarse de que las fechas tienen el formato esperado por la hoja de cálculo.

Los números están en notación científica

Si ves números en tu hoja de cálculo que parecen 123456E+14, están en notación científica. Esto podría deberse a que tu columna es demasiado estrecha; puedes solucionarlo ampliando la columna. Sin embargo, es probable que el problema sea que tienes un número de más de 15 dígitos. Las tres hojas de cálculo que hemos visto no pueden manejar números mayores. Los redondean automáticamente y los muestran en notación científica.

Esto está bien para aplicaciones científicas, pero no es tan bueno en contabilidad - ¡y definitivamente NO está bien si el número representa un número de cuenta de 16 dígitos!

Puede conseguir que los números se muestren correctamente tratándolos como texto, no como números. Sin embargo, no podrá realizar operaciones aritméticas con ellos en ese formato.

Si utilizó cortar y pegar para importar los datos, utilice Pegado especial → Asistente de importación de texto para cambiar el tipo de datos de esa columna a texto.

Si los datos proceden de un archivo CSV, la forma más sencilla de conseguir que se importen correctamente es abrir el archivo en el Bloc de notas (o en cualquier otro editor de texto básico), seleccionar y copiar todos los datos y utilizar Pegado especial→ Asistente para importación de texto para especificar los tipos de datos.

Si sus datos son demasiado grandes para una sola operación de copiar/pegar, puede que tenga que hacer la operación de copiar/pegar en trozos más pequeños.

Su columna ID ha perdido ceros a la izquierda o un dígito

Es posible que tenga una columna que contenga un identificador numérico y que los números sean muy grandes. Los ceros a la izquierda son importantes en este caso, ya que forman parte del identificador.

La solución es la misma que para los números que aparecen en notación científica: Trate la columna como texto, no como datos numéricos.

Desarrolle sus habilidades de SQL a hoja de cálculo

Como hemos visto, SQL y las hojas de cálculo pueden utilizarse fácilmente de forma conjunta para aprovechar la potencia de ambas aplicaciones. SQL es tu puerta de entrada al mundo de los datos, y merece la pena que aumentes tus conocimientos en este campo.

Si no has aprendido SQL, nuestro curso SQL para principiantes es un buen punto de partida. Y si quiere invertir de verdad en su futuro con SQL, el paquete Todo, para siempre le da acceso a todos nuestros cursos, ¡incluidos los que aún no hemos escrito! Esto significa que siempre podrás estar al día de las nuevas funciones que se añadan a SQL en el futuro.

Si puedes utilizar SQL y hojas de cálculo, y hacer que ambos funcionen juntos, tus habilidades siempre serán demandadas en el mundo actual, basado en los datos. Así que da el primer paso hoy mismo. Empieza a aprender, a experimentar y a tomar las riendas de tu futuro.