Exportar datos de la base de datos a un archivo CSV

Microsoft Access (2007, 2003) | MySQL | Postgres | MS SQL Server | Oracle | The Enterprise Data Publisher

SpatialKey requiere que los datos se carguen e importen mediante un archivo CSV antes de que se pueda importar un conjunto de datos. Hemos intentado que el proceso de importación sea lo más divertido y sencillo posible, pero SpatialKey no puede ayudarte a convertir tus datos en formato CSV. En muchos casos, los datos valiosos a los que necesita acceso se guardarán dentro de una base de datos. Hay muchos tipos de bases de datos, y la mayoría de ellas tienen capacidades para ayudar a exportar sus datos a un formato CSV, pero generalmente son específicas para la base de datos en cuestión.

Este objetivo de este artículo es examinar cómo obtener datos en un formato CSV de muchas de las más bases de datos de álamo y sistemas de gestión de bases de datos en el mercado hoy en día. Si no tiene acceso a la base de datos donde residen sus datos y tiene un administrador de base de datos o personal de TI que genera sus informes, pase el enlace a este artículo cuando solicite datos para SpatialKey, ya que también puede ahorrarles tiempo.

Tenga en cuenta que este artículo trata sobre el uso de los comandos de Lenguaje de Consulta Estructurado (SQL) necesarios para ejecutarse en una base de datos y está destinado a usuarios que estén familiarizados con SQL y las herramientas para la base de datos que utilizan habitualmente.

Microsoft Access:

Microsoft Access es una popular base de datos basada en archivos utilizada normalmente por pequeñas organizaciones con un número limitado de usuarios y también por individuos. Se puede acceder a los datos de MS Access utilizando la aplicación MS Access de forma visual, así como a través de enfoques basados en SQL tradicionales. Como MS Access ha sufrido recientemente un cambio importante en la interfaz de usuario, exploraremos las versiones de 2007 y 2003 de la aplicación (si está trabajando con una versión anterior, también deberían estar disponibles técnicas similares).

Access 2007:

En la primera figura (1.1) puede ver que he abierto la base de datos de ejemplo estándar que viene con MS Access (llamada Northwind). También he seleccionado la consulta llamada «Los Diez Mejores pedidos por Cantidad de Ventas». A esta consulta, he agregado información de la dirección del cliente, ya que a SpatialKey le encantan los datos que contienen datos temporales (hora y fecha) y geoespaciales (datos que se pueden traducir a una ubicación).

Fig. 1.1-MS Access 2007

Desde aquí puedo exportar fácilmente los resultados a un archivo CSV:

1. Haga clic en la tabla o consulta desde la que desea exportar (en este ejemplo, «Los diez mejores pedidos por Cantidad de ventas» a la izquierda)
2. Haga clic en la pestaña «Datos externos» en la parte superior de la ventana
3. En la sección «Exportar», haga clic en «Archivo de texto» y aparecerá un asistente
4. Elija una ubicación para el archivo CSV exportado y asigne un nombre a su archivo (asegúrese de que el archivo termine con a .extensión csv)
5. Haga clic en Aceptar
6. En la siguiente pantalla asegúrese de que la opción «Delimitada» esté seleccionada
7. Haga clic en el botón «Avanzado Advanced» en la parte inferior izquierda de la ventana
8. SpatialKey almacena sus datos como Unicode UTF-8, necesitamos asegurarnos de que los datos se exportan en este formato
   1. Haga clic en el cuadro desplegable junto a la página de código
   2. Elija Unicode (UTF-8) en la lista de opciones
   3. /li>
   4. Haga clic en Aceptar
9. De vuelta en la ventana Exportar texto, haga clic en «Siguiente»
10. Asegúrese de que «Coma» esté seleccionado como delimitador y el Calificador de texto sea una comilla doble: «
11. Haga clic en la casilla de verificación «Incluir nombres de campo en la Primera Fila» (debe seleccionarse)
12. Haga clic en «Siguiente»
13. Verifique el nombre y la ubicación del archivo y haga clic en «Finalizar»
14. La pantalla final del asistente le da la opción de guardar los pasos que permiten una fácil reexportación de los datos en el futuro. Si anticipa la necesidad de actualizar los datos en SpatialKey, siga adelante y marque la casilla de verificación para ahorrar tiempo en el futuro. Cierre la ventana cuando haya terminado.

Si abres el archivo en un editor de texto, deberías ver algo como esto:

Fig. 1.2-Datos Northwind exportados

Ahora está listo para cargar el nuevo archivo CSV en SpatialKey.

MS Access 2003:

La siguiente figura muestra la versión más antigua de MS Access 2003 de la base de datos Northwind. Exportaré la consulta guardada llamada «Orders Qry» a la izquierda.

Fig. 1.3-MS Access 2003 Base de datos Northwind

Ahora está listo para cargar el nuevo archivo CSV en SpatialKey.

MySQL:

MySQL es una popular base de datos preparada para empresas de código abierto. Hay muchas herramientas disponibles para interactuar con esta base de datos, pero al igual que con la mayoría de las bases de datos empresariales, la mayoría del acceso se realiza a través de un Lenguaje de consulta Estructurado. Hay algunas opciones para exportar datos a CSV desde MySQL. Algunos requieren herramientas de tercera parte, mientras que otros usan una herramienta de línea de comandos (útil en máquinas Unix) y una opción final a través de SQL. Echemos un vistazo a las dos últimas opciones:

Herramienta de línea de comandos MySQL (en Unix):

mysql -u exampleuser -p letmein exampledb -B -e "select * from\'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv

¿Qué significa todo eso?

• mysql : el archivo ejecutable (debe estar donde está instalado MySQL)
• -u exampleuser : un conmutador y una opción para el nombre de usuario para ejecutar el SQL con
• -p letmein : un conmutador y una opción para la contraseña del usuario
• exampledb : la base de datos para ejecutar el SQL contra
• -B : indica que la salida debe estar delimitada por tabulaciones (lo convertiremos en comas más adelante en el comando)
• -e «la instrucción sql aquí» : la instrucción SQL a ejecutar que devuelve los datos
• | sed ‘s/\t/»,»/g;s/^/»/;s/li/»/;s/\n//g’ : sed es un procesador de flujo Unix, esencialmente permite transformaciones en este caso. Aquí tenemos cuatro comandos sed que cambian las pestañas a comillas dobles, agrega comillas dobles al principio y al final de cada línea y agrega un nuevo marcador de línea al final de cada línea.
• > nombre de archivo.csv: muestra los resultados en el archivo llamado filename.csv

Ahora está listo para cargar el nuevo archivo CSV en SpatialKey.

Sintaxis SQL de MySQL:

SELECT column_a,column_b,column_c+column_d INTO OUTFILE '/tmp/result.csv'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n'FROM test_table;

La instrucción MySQL select utiliza un comando INTO OUTFILE para generar los resultados de SQL select en un archivo (en este caso /tmp/result.csv).

Un par de notas:

• En ambos casos, el archivo se guarda en una ubicación local desde la que se ejecuta la base de datos (en la misma máquina, o en una unidad de montaje/unidad disponible para la máquina)
• La fila de encabezado deberá agregarse a ambos archivos (lo que se puede hacer utilizando cualquier editor de texto u programa de hoja de cálculo)

Ahora está listo para cargar el nuevo archivo CSV en SpatialKey.

Postgres:

Postgres es otra popular base de datos preparada para empresas de código abierto. Similar en naturaleza a MySQL, hay muchas herramientas de terceros (incluyendo pgAdmin de Postgres) para administrar y administrar la base de datos. La mejor opción para exportar datos CSV de Postgres es usar un comando SQL específico de Postgres llamado COPY:

COPY (SELECT column_a,column_b FROM whatever) TO '/tmp/dump.csv' WITH CSV HEADER

Este comando es extremadamente rápido y produce un archivo CSV con el formato adecuado, incluidos los encabezados.

Ahora está listo para cargar el nuevo archivo CSV en SpatialKey.

Servidor MS SQL:

SQL Server es una base de datos disponible comercialmente de Microsoft (también hay una versión más pequeña y menos destacada llamada SQL Server Express que está disponible para descargar de forma gratuita). SQL Server es una base de datos preparada para empresas utilizada por muchas empresas de todo el mundo. A lo largo de los años ha habido muchas herramientas de gestión tanto de terceros como de Microsoft (Enterprise Manager, Management Studio y a través de MS Developer Studio, por ejemplo). Dos opciones para exportar como CSV desde un servidor MS SQL son el uso de la herramienta de línea de comandos BPC (Programa de copia Masiva) o a través de Servicios de Transformación de datos.

BCP:

El programa de copia masiva se puede ejecutar desde una línea de comandos o a través de SQL.

• línea de Comandos:
  • BCP <nombre de la tabla> fuera <nombre de archivo.csv> -c -t, -U <user name> -P <password> -S<server name>
  • Switches:
    • -c : export as ASCII with a tab delimiter and carriage return/line feed line terminator
    • -t, : override the tab delimiter with a comma
    • -U : username
    • -P : password
    • -S : server to connect to
• SQL:
  • MS SQL Server tiene un comando Transact-SQL llamado xp_cmdshell, este comando le permite ejecutar ejecutables de línea de comandos desde en SQL
  • xp_cmdshell BCP <nombre de tabla> out <nombre de archivo.csv>- c-t, -T-S<nombre del servidor>
    • Tenga en cuenta que cambié los valores-U y – P a a-T, que utiliza una conexión de confianza, ya que probablemente nos estamos autenticando en el equipo de SQL server para ejecutar el comando SQL.

DTS:
Los paquetes DTS se pueden crear utilizando scripts SQL o a través de una interfaz de usuario de MS.

1. En Enterprise Manager, elija >Asistentes >Transformación de datos > Exportar datos
2. Elija su base de datos de origen
3. Para el archivo de texto de selección de destino y especifique el archivo de destino y el nombre de archivo con a .extensión csv
4. Establecer la tabla o consulta para extraer los datos de
5. Establecer la opción que la primera fila contiene información de columna
6. En la pantalla para seleccionar el delimitador, asegúrese de que la coma esté seleccionada
7. Ejecutar el paquete

Ahora está listo para cargar el nuevo archivo CSV en SpatialKey.

Oracle:

Oracle es una base de datos disponible comercialmente que tiene un gran número de seguidores en grandes empresas. Al igual que SQL Server, Oracle es una base de datos preparada para empresas. Las opciones para extraer a CSV usando Oracle incluyen el uso de Oracle SQL Developer (una herramienta gratuita de Oracle) y el uso directo de PL/SQL (generalmente mediante la creación de un procedimiento almacenado, ya que está un poco más basado en código que las otras soluciones de base de datos).

Desarrollador de Oracle SQL:

Esta es, con mucho, la forma más fácil de exportar datos para alguien que no esté familiarizado con procedimientos almacenados PL/SQL complejos.

Una forma de hacerlo es crear un informe en SQL Developer y exportar los resultados

1. Una vez creado y ejecutado el informe, haga clic con el botón derecho en la cuadrícula de resultados y elija «Exportar» en el menú contextual
2. En la siguiente pantalla, seleccione CSV como formato e ingrese el nombre del archivo.csv y ubicación

También puede exportar una sola tabla haciendo clic con el botón derecho en el nombre de la tabla en la vista de árbol de objetos.

Seleccione Exportar.

Seleccione CSV. Aparece la ventana Exportar datos.

Haga clic en la pestaña Formato.

Seleccionar Formato como: CSV

Introduzca un nombre de archivo y una ubicación.

Haga clic en la pestaña Columnas.

Marque las columnas que desea exportar

Haga clic en la pestaña Donde y agregue los criterios necesarios para filtrar los datos

Haga clic en Aplicar.

PL / SQL:

La mejor manera de exportar datos a través de PL/SQL es crear un procedimiento almacenado que utilice el comando UTL_FILE.

Para obtener un excelente ejemplo de procedimiento almacenado para esto, consulte este artículo en oracle.com. Tenga en cuenta que se recomienda un cambio en el procedimiento almacenado original ubicado en los comentarios (maneja correctamente la cita de datos con caracteres CSV reservados).

Ahora está listo para cargar el nuevo archivo CSV en SpatialKey.

El Editor de datos empresariales:

Para nuestros clientes empresariales que tienen grandes cantidades de datos para importar o necesitan datos actualizados con frecuencia en SpatialKey, ofrecemos un producto adicional llamado Editor de datos empresariales. Este es un producto basado en servicios que se ejecuta detrás de escena (y su firewall) que se puede programar para enviar datos automáticamente a SpatialKey desde sus bases de datos. Si está interesado en obtener más información sobre las capacidades y el costo del Editor de datos empresariales, envíenos un correo electrónico a nuestra página Contáctenos. Estaremos encantados de ayudarle con sus necesidades de datos!

En conclusión, SpatialKey necesita que sus datos brillen. Producir datos a partir de una base de datos existente no tiene que ser una tarea desalentadora, como muestran los pasos enumerados anteriormente, y es un primer paso importante para desbloquear realmente sus Datos.