ejemplo - csv postgresql copy example

Copie algunas de las columnas de un archivo csv en una tabla

Question

Tengo un archivo CSV con 10 columnas. Después de crear una tabla PostgreSQL con 4 columnas, quiero copiar algunas de las 10 columnas en la tabla.

las columnas de mi tabla CSV son como:

x1 x2 x3 x4 x5 x6 x7 x8 x9 x10

las columnas de mi tabla PostgreSQL deberían ser como:

x2 x5 x7 x10

Answer 1

Si es una tarea ad hoc

Crear una tabla temporal con todas las columnas en el archivo de entrada

create temporary table t (x1 integer, ... , x10 text)

Copiar desde el archivo en él:

copy t (x1, ... , x10)
from ''/path/to/my_file''
with (format csv)

Ahora inserte en la tabla definitiva de la temperatura:

insert into my_table (x2, x5, x7, x10)
select x2, x5, x7, x10
from t

Y soltarlo:

drop table t

Si es una tarea frecuente

Use la extensión file_fdw . Como superusuario:

create extension file_fdw;
create server my_csv foreign data wrapper file_fdw;
create foreign table my_csv (
x1 integer,
x2 text,
x3 text
) server my_csv
options (filename ''/tmp/my_csv.csv'', format ''csv'' )
;

Conceda permiso de selección en la mesa al usuario que lo leerá:

grant select on table my_csv to the_read_user;

Luego, cuando sea necesario, lea directamente desde el archivo csv como si fuera una tabla:

insert into my_table (x2)
select x2
from my_csv
where x1 = 2

Answer 2

Acabo de llegar aquí buscando una solución para cargar solo un subconjunto de columnas, pero aparentemente no es posible. Entonces, use awk (o cut ) para extraer las columnas deseadas a un nuevo archivo new_file :

$ awk ''{print $2, $5, $7, $10}'' file > new_file

y carga el new_file . Puede canalizar la salida directamente a psql :

$ cut -d / -f 2,5,7,10 file |
psql -h host -U user -c "/COPY table(col1,col2,col3,col4) FROM STDIN DELIMITER '' ''" database

Answer 3

Como han señalado otras respuestas, ha sido posible especificar columnas para copiar en la tabla PG. Sin embargo, sin la opción de referenciar los nombres de columna en el CSV, esto tenía poca utilidad además de cargarse en una tabla donde las columnas tenían un orden diferente.

Afortunadamente, a partir de Postgres 9.3, es posible copiar columnas no solo desde un archivo o desde la entrada estándar, sino también desde un comando de shell usando PROGRAM:

PROGRAMA

Un comando para ejecutar En COPY FROM, la entrada se lee desde la salida estándar del comando, y en COPY TO, la salida se escribe en la entrada estándar del comando.

Tenga en cuenta que el comando es invocado por el shell, por lo que si necesita pasar cualquier argumento al comando del shell que provenga de un origen que no sea de confianza, debe tener cuidado de quitar o escapar cualquier carácter especial que pueda tener un significado especial para el shell . Por razones de seguridad, es mejor usar una cadena de comandos fija, o al menos evitar pasar cualquier entrada de usuario en ella.

Esta fue la pieza faltante que necesitábamos para una funcionalidad tan ansiosamente esperada. Por ejemplo, podríamos usar esta opción en combinación con cut (en un sistema basado en UNIX) para seleccionar ciertas columnas por orden:

COPY my_table (x2, x5, x7, x10) FROM PROGRAM ''cut -d "," -f 2,5,7,10 /path/to/file.csv'' WITH (FORMAT CSV, HEADER)

Sin embargo, cut tiene varias limitaciones al manipular CSV : no puede manipular adecuadamente cadenas con comas (u otros delímetros) dentro de ellos y no permite seleccionar columnas por nombre.

Hay varias otras herramientas de línea de comandos de código abierto que son mejores para manipular archivos CSV, como csvkit o miller . Aquí hay un ejemplo usando miller para seleccionar columnas por nombre:

COPY my_table (x2, x5, x7, x10) FROM PROGRAM ''mlr --csv lf cut -f x2,x5,x7,x10 /path/to/file.csv'' WITH (FORMAT CSV, HEADER)

Answer 4

Para cargar datos de la hoja de cálculo (Excel u OpenOffice Calc) en postgreSQL:

Guarde la página de la hoja de cálculo como un archivo CSV. El método preferido es abrir la hoja de cálculo en OpenOffice Calc y realizar el guardado. En la ventana "Exportar a archivo de texto", seleccione Conjunto de caracteres como Unicode (UTF8), Delimitador de campo: "," y Delimitador de texto "" ". El mensaje se mostrará diciendo que solo se guarda la hoja activa. Nota: Este archivo debe guardarse en una carpeta pero no en el escritorio y debe guardarse en formato UTF8 (postgreSQL por defecto es intensificar para la codificación UTF8). Si se guarda en el escritorio, postgreSQL dará un mensaje de "acceso denegado" y no se cargará.

En PostgreSQL, cree una tabla vacía con el mismo número de columnas que la hoja de cálculo.

Nota: En cada columna, el nombre de la columna tiene que ser el mismo, el tipo de datos tiene que ser el mismo. Además, tenga en cuenta la longitud de los datos donde el carácter varía con suficiente campo.

Luego, en postgreSQL, en la ventana SQL, coloque el código:

copie "ABC". "def" de E''C: // tmp // blabla.csv ''delimitadores'', ''CSV HEADER;

NOTA: Aquí C: // tmp es la carpeta donde se guarda el archivo CSV "blabla". "ABC". "Def" es la tabla creada en postgreSQL donde "ABC" es el esquema y "def" es la tabla real. Luego haz "ejecutar consulta" presionando el botón verde en la parte superior. Se necesita "CSV HEADER" cuando la tabla CSV se dirige al inicio de cada columna.

Si todo está bien, no se mostrará ningún mensaje de error y los datos de la tabla del archivo CSV se cargarán en la tabla postgreSQL. Pero si hay un mensaje de error haz lo siguiente:

Si el mensaje de error indica que los datos son demasiado largos para una columna específica, entonces aumente el tamaño de la columna. Esto ocurre principalmente en la columna variable de caracteres y caracteres. A continuación, ejecute el comando "ejecutar consulta" de nuevo.

Si el mensaje de error dice que el tipo de datos no coincide con una columna en particular, entonces cambie el tipo de datos en la columna-tabla de postgreSQL para que coincida con el de la tabla CSV.

En su caso, después de crear el archivo CSV, elimine las columnas no deseadas y haga coincidir las columnas en la tabla de postgreens.

Answer 5

Puede proporcionar las columnas que desea completar con el comando COPY . Al igual que:

/copy your_table (x2,x5,x7,x10) FROM ''/path/to/your-file.csv'' DELIMITER '','' CSV;

Aquí está el documento para el comando COPY .

Answer 6

Puede seguir la sugerencia de James Brown y hacer, todo en una sola línea:

archivo de gato | awk -F '','' ''{imprimir $ 2 "," $ 5 "," $ 7 "," $ 10}'' | psql -d db -c "/ copy MyTable from STDIN csv header"