PHP Classes

PHP Import CSV to PostgreSQL: Insert CSV file data into a PostgreSQL table

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not yet rated by the usersTotal: 66 All time: 10,372 This week: 56Up
Version License PHP version Categories
csvtopostgresql 1.0GNU General Publi...7Databases, Tools, Compression, Parsers, P...
Description 

Author

Moisés Lima

This package can insert CSV file data into a PostgreSQL table.

It can take a CSV file compressed in ZIP format and extracts the archive to parse the CSV data to extract its record values.

The package and insert the values into a PostgreSQL database table.

It can create the database table if it does not yet exist.

Innovation Award
PHP Programming Innovation award nominee
January 2023
Number 3		 PostgreSQL is one of the most popular SQL database server types that many PHP developers use to store and retrieve their applications' data.

Sometimes developers need to import data from an external source into the database.

This package allows importing data into a PostgreSQL database table from a CSV file that may have been exported from many other types of programs.

Manuel Lemos
Picture of Moisés Lima
  Performance   Level  
Name: Moisés Lima <contact>
Classes: 5 packages by
Country: Brazil Brazil
Age: 37
All time rank: 3503274 in Brazil Brazil
Week rank: 195 Up14 in Brazil Brazil Up

Level 1 Star

 Innovation award
Innovation award
Nominee: 3x

Example

#!/usr/bin/php -q
<?php

require_once 'vendor/autoload.php';

use TheMoiza\Csvtopostgresql\CsvToPgsql;

$csvToPgsql = new CsvToPgsql;

$csvToPgsql->setConfigs([
    'createPkey' => true,
    'enableTrim' => true,
    'enableTransaction' => true,
    'justCreateTables' => false,
    'inputEncoding' => 'UTF-8',
    'outputEncoding' => 'UTF-8'
]);

print 'Wait...'.PHP_EOL;

$result = $csvToPgsql->convertCsvFromZip(
    'csv_example.zip',
    [
        "DB_HOST" => "127.0.0.1",
        "DB_PORT" => "5432",
        "DB_DATABASE" => "csvtopostgresql",
        "DB_USERNAME" => "csvtopostgresql",
        "DB_PASSWORD" => "csvtopostgresql",
        "DB_SCHEMA" => "target_schema"
    ]
);

print ($result['result'] ? 'Success' : 'Fail').PHP_EOL;

print $result['message'].PHP_EOL;

Details

Csv to PostgreSql with PHP

Converte CSV para PostgreSql.

Install with composer

composer require the.moiza/csvtopostgresql

CONTRIBUTE

Join this project: https://discord.com/channels/973324521987792916/973324521987792918

Support this project: https://skit.network/donate

PT-BR

Dependências da ferramenta: php7.2 ou maior, mbstring, fileinfo, zip e pdo;

Você poder usar o modo CLI para fazer a conversão, ou criar sua própria implementação.

php cli.php

CONFIGURAÇÕES

  • createPkey = Se true, cria crave primária \_pkey\_, padrão: false;
  • enableTrim = Se true, corta espaços em branco, quebra de linha tab no início e fim de cada dado importado, padrão: true;
  • enableTransaction = Se true habilita boa prática de Transaction (recomendado), padrão: true;
  • justCreateTables = Se true, não insere os dados, apenas cria as tabelas, padrão: false;
  • inputEncoding = Seta encoding do CSV, padrão: UTF-8';
  • outputEncoding = Seta encoding do banco de dados, padrão: UTF-8';

O CSV

O CSV deve estar em formato ZIP.

O(s) CSV(s) devem ter na primeira linha os nomes das colunas. Cada CSV será importado como uma tabela.

O nome da tabela será o nome do arquivo CSV corretamente tratado com lowcase, remoção de acentos, substituição de espaços (&nbsp;) e hífen (-) por (_) underline;

Os nomes das colunas serão tratados com as mesmas regras.

Binários (blob, bytea) devem ser exportados em BASE64 para o CSV e serão importados como text.

Exemplo de CSV aceito:

|LatD|LatM|LatS|NS|LonD|LonM|LonS|EW|City |State| |----|----|----|--|----|----|----|--|----------|-----| |41 |5 |59 |N |80 |39 |0 |W |Youngstown|OH | |42 |52 |48 |N |97 |23 |23 |W |Yankton |SD | |46 |35 |59 |N |120 |30 |36 |W |Yakima |WA |

O separador de coluna será detectado automaticamente, pode ser ,(vírgula) ou ;(ponto e vírgula).

A ferramenta tentará detectar os principais tipos de dados:

  • timestamp
  • date
  • numeric
  • integer
  • boolean
  • text

EN

Help us to translate and write the documentation in other languages.


  Files folder image Files (8)  
File Role Description
Files folder imagesrc (2 files)
Files folder imagevendor (1 file)
Accessible without login Plain text file cli.php Example Example script
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file composer.lock Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files (8)  /  src  
File Role Description
  Plain text file CsvToPgsql.php Class Class source
  Plain text file CsvToPgsqlException.php Class Class source

  Files folder image Files (8)  /  vendor  
File Role Description
  Accessible without login Plain text file autoload.php Aux. Auxiliary script

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloadcsvtopostgresql-2023-01-15.zip 8KB
Downloadcsvtopostgresql-2023-01-15.tar.gz 7KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:66
This week:0
All time:10,372
This week:56Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.