How to import CSV data from http://data.gov.ro/ Datasets

import_datagov_steps.txt

for z in *.csv; do zip -6 $z.zip $z ; done

------------------------------------------------------

sudo apt install composer

composer jsonlint php-cli php-cli-prompt php-common php-composer-ca-bundle
  php-composer-semver php-composer-spdx-licenses php-json-schema php-psr-log
  php-symfony-console php-symfony-filesystem php-symfony-finder
  php-symfony-polyfill-mbstring php-symfony-process php7.1-cli php7.1-common
  php7.1-json php7.1-opcache php7.1-readline

Creating config file /etc/php/7.1/mods-available/calendar.ini with new version
Creating config file /etc/php/7.1/mods-available/ctype.ini with new version
Creating config file /etc/php/7.1/mods-available/exif.ini with new version
Creating config file /etc/php/7.1/mods-available/fileinfo.ini with new version
Creating config file /etc/php/7.1/mods-available/ftp.ini with new version
Creating config file /etc/php/7.1/mods-available/gettext.ini with new version
Creating config file /etc/php/7.1/mods-available/iconv.ini with new version
Creating config file /etc/php/7.1/mods-available/pdo.ini with new version
Creating config file /etc/php/7.1/mods-available/phar.ini with new version
Creating config file /etc/php/7.1/mods-available/posix.ini with new version
Creating config file /etc/php/7.1/mods-available/shmop.ini with new version
Creating config file /etc/php/7.1/mods-available/sockets.ini with new version
Creating config file /etc/php/7.1/mods-available/sysvmsg.ini with new version
Creating config file /etc/php/7.1/mods-available/sysvsem.ini with new version
Creating config file /etc/php/7.1/mods-available/sysvshm.ini with new version
Creating config file /etc/php/7.1/mods-available/tokenizer.ini with new version

------------------------------------------------------

sudo apt install mysql-client mysql-server

mysql --version
mysql  Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using  EditLine wrapper

------------------------------------------------------

sudo apt install mongodb mongodb-clients  mongodb-server mongo-tools
sudo apt install postgresql postgresql-client postgresql-client-common

sudo apt install apache2
sudo apt-get install apache2 libapache2-mod-php
sudo apt-get install php-mysql php-mcrypt php-mbstring php-mongodb 
sudo systemctl restart apache2

------------------------------------------------------

get 

phpMyAdmin-4.8.3-all-languages.zip 

wget http://c758482.r82.cf2.rackcdn.com/sublime-text_build-3083_amd64.deb
sudo dpkg -i sublime-text_build-3083_amd64.deb 



------------------------------------------------------



CREATE DATABASE datagovro;

mysql -u root -p -database datagovro 

CREATE TABLE contracte (
	Castigator  VARCHAR(255) NULL,
	CastigatorCUI  VARCHAR(255) NULL,
	CastigatorTara  VARCHAR(255) NULL,
	CastigatorLocalitate  VARCHAR(255) NULL,
	CastigatorAdresa  VARCHAR(255) NULL,
	Tip  VARCHAR(255) NULL,
	TipContract  VARCHAR(255) NULL,
	TipProcedura  VARCHAR(255) NULL,
	AutoritateContractanta  VARCHAR(255) NULL,
	AutoritateContractantaCUI  VARCHAR(255) NULL,
	TipAC  VARCHAR(255) NULL,
	TipActivitateAC  VARCHAR(255) NULL,
	NumarAnuntAtribuire  VARCHAR(255) NULL,
	DataAnuntAtribuire  VARCHAR(255) NULL,
	TipIncheiereContract  VARCHAR(255) NULL,
	TipCriteriiAtribuire  VARCHAR(255) NULL,
	CuLicitatieElectronica  VARCHAR(255) NULL,
	NumarOfertePrimite  VARCHAR(255) NULL,
	Subcontractat  VARCHAR(255) NULL,
	NumarContract  VARCHAR(255) NULL,
	DataContract  VARCHAR(255) NULL,
	TitluContract  VARCHAR(255) NULL,
	Valoare  VARCHAR(255) NULL,
	Moneda  VARCHAR(255) NULL,
	ValoareRON  VARCHAR(255) NULL,
	ValoareEUR  VARCHAR(255) NULL,
	CPVCodeID  VARCHAR(255) NULL,
	CPVCode  VARCHAR(255) NULL,
	NumarAnuntParticipare  VARCHAR(255) NULL,
	DataAnuntParticipare  VARCHAR(255) NULL,
	ValoareEstimataParticipare  VARCHAR(255) NULL,
	MonedaValoareEstimataParticipare  VARCHAR(255) NULL,
	FonduriComunitare  VARCHAR(255) NULL,
	TipFinantare  VARCHAR(255) NULL,
	TipLegislatieID  VARCHAR(255) NULL,
	FondEuropean  VARCHAR(255) NULL,
	ContractPeriodic  VARCHAR(255) NULL,
	DepoziteGarantii  VARCHAR(255) NULL,
	ModalitatiFinantare  VARCHAR(255) NULL
);





LOAD DATA LOCAL INFILE 'contracte-2007.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2008.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2009.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2010.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2011.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2012-s1.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2012-s2.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2013-s1.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2013-s2.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2014-s1.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2014-s2.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2015-s1.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2015-s2.csv' INTO TABLE contracte FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'contracte-2016-s1.csv' INTO TABLE contracte FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';




ALTER TABLE `contracte` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);

mysqldump -u root -p datagovro contracte > contracte.sql
mysqldump --compatible=postgresql -u root -p  datagovro contracte > contracte_postgresql.sql
zip contracte.sql.zip contracte.sql 
zip contracte_postgresql.sql.zip contracte_postgresql.sql 

-------------------------------------


CREATE TABLE cumparari_directe (

	Castigator VARCHAR(255) NULL,
	CastigatorCUI VARCHAR(255) NULL,
	CastigatorTara VARCHAR(255) NULL,
	CastigatorLocalitate VARCHAR(255) NULL,
	CastigatorAdresa VARCHAR(255) NULL,
	TipProcedura VARCHAR(255) NULL,
	AutoritateContractanta VARCHAR(255) NULL,
	AutoritateContractantaCU VARCHAR(255) NULL,
	NumarAnunt VARCHAR(255) NULL,
	DataAnunt VARCHAR(255) NULL,
	TipIncheiereContract VARCHAR(255) NULL,
	NumarContract VARCHAR(255) NULL,
	DataContract VARCHAR(255) NULL,
	TitluContract VARCHAR(255) NULL,
	Valoare VARCHAR(255) NULL,
	Moneda VARCHAR(255) NULL,
	ValoareRON VARCHAR(255) NULL,
	ValoareEUR VARCHAR(255) NULL,
	CPVCodeID VARCHAR(255) NULL,
	CPVCode VARCHAR(255) NULL

);


LOAD DATA LOCAL INFILE 'cumparari-directe-2007-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2007-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2007-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2007-t4.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2008-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2008-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2008-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2008-t4.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2009-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2009-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2009-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2009-t4.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2010-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2010-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2010-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2010-t4.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2011-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2011-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2011-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2011-t4.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2012-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2012-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2012-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2012-t4-part1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2012-t4-part2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2013-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2013-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2013-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2013-t4-part1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2013-t4-part2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2013-t4-part3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2014-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2014-t2-part1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2014-t2-part2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2014-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2014-t4-part1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2014-t4-part2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2015-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2015-t2-part-1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2015-t2-part-2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2015-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2015-t4.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2016-t1.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2016-t2.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE 'cumparari-directe-2016-t3.csv' INTO TABLE cumparari_directe FIELDS TERMINATED BY '^' LINES TERMINATED BY '\n';


ALTER TABLE `cumparari_directe` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`);


mysqldump -u root -p datagovro cumparari_directe > cumparari_directe.sql
mysqldump --compatible=postgresql -u root -p  datagovro cumparari_directe > cumparari_directe_postgresql.sql

zip cumparari_directe.sql.zip cumparari_directe.sql
zip cumparari_directe_postgresql.sql.zip cumparari_directe_postgresql.sql


// IMPORT

TRUNCATE TABLE `contracte` ;

mysql -u root -p  
use datagovro; 
source contracte.sql;


// EXPORT 2 CSV
SELECT * INTO OUTFILE '/var/lib/mysql-files/contracte.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM contracte WHERE 1;

SELECT @@GLOBAL.secure_file_priv;
+---------------------------+
| @@GLOBAL.secure_file_priv |
+---------------------------+
| /var/lib/mysql-files/     |
+---------------------------+
1 row in set (0.02 sec)


mysql> SELECT * FROM contracte INTO OUTFILE '/var/lib/mysql-files/contracte.csv' FIELDS TERMINATED BY ',';
Query OK, 855704 rows affected (18.64 sec)

sudo cp /var/lib/mysql-files/contracte.csv .


for z in *.zip; do unzip $z; done


----------------------------

##########################################################
#
#	postgres
#
###########################################################

sudo apt-get install pgadmin3

sudo -u postgres psql postgres
psql (9.6.9)
Type "help" for help.

postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=# create database datagovro;
CREATE DATABASE

\q # exit

pgadmin3


-- Database: datagovro
-- DROP DATABASE datagovro;

CREATE DATABASE datagovro
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'de_DE.UTF-8'
       LC_CTYPE = 'de_DE.UTF-8'
       CONNECTION LIMIT = -1;




/*
CREATE TABLE contracte (
	Castigator  VARCHAR(255) NULL,
	CastigatorCUI  VARCHAR(255) NULL,
	CastigatorTara  VARCHAR(255) NULL,
	CastigatorLocalitate  VARCHAR(255) NULL,
	CastigatorAdresa  VARCHAR(255) NULL,
	Tip  VARCHAR(255) NULL,
	TipContract  VARCHAR(255) NULL,
	TipProcedura  VARCHAR(255) NULL,
	AutoritateContractanta  VARCHAR(255) NULL,
	AutoritateContractantaCUI  VARCHAR(255) NULL,
	TipAC  VARCHAR(255) NULL,
	TipActivitateAC  VARCHAR(255) NULL,
	NumarAnuntAtribuire  VARCHAR(255) NULL,
	DataAnuntAtribuire  VARCHAR(255) NULL,
	TipIncheiereContract  VARCHAR(255) NULL,
	TipCriteriiAtribuire  VARCHAR(255) NULL,
	CuLicitatieElectronica  VARCHAR(255) NULL,
	NumarOfertePrimite  VARCHAR(255) NULL,
	Subcontractat  VARCHAR(255) NULL,
	NumarContract  VARCHAR(255) NULL,
	DataContract  VARCHAR(255) NULL,
	TitluContract  VARCHAR(255) NULL,
	Valoare  VARCHAR(255) NULL,
	Moneda  VARCHAR(255) NULL,
	ValoareRON  VARCHAR(255) NULL,
	ValoareEUR  VARCHAR(255) NULL,
	CPVCodeID  VARCHAR(255) NULL,
	CPVCode  VARCHAR(255) NULL,
	NumarAnuntParticipare  VARCHAR(255) NULL,
	DataAnuntParticipare  VARCHAR(255) NULL,
	ValoareEstimataParticipare  VARCHAR(255) NULL,
	MonedaValoareEstimataParticipare  VARCHAR(255) NULL,
	FonduriComunitare  VARCHAR(255) NULL,
	TipFinantare  VARCHAR(255) NULL,
	TipLegislatieID  VARCHAR(255) NULL,
	FondEuropean  VARCHAR(255) NULL,
	ContractPeriodic  VARCHAR(255) NULL,
	DepoziteGarantii  VARCHAR(255) NULL,
	ModalitatiFinantare  VARCHAR(255) NULL
);*/


-- Table: public.contracte
-- DROP TABLE public.contracte;

CREATE TABLE public.contracte
(
  castigator character varying(255),
  castigatorcui character varying(255),
  castigatortara character varying(255),
  castigatorlocalitate character varying(255),
  castigatoradresa character varying(255),
  tip character varying(255),
  tipcontract character varying(255),
  tipprocedura character varying(255),
  autoritatecontractanta character varying(255),
  autoritatecontractantacui character varying(255),
  tipac character varying(255),
  tipactivitateac character varying(255),
  numaranuntatribuire character varying(255),
  dataanuntatribuire character varying(255),
  tipincheierecontract character varying(255),
  tipcriteriiatribuire character varying(255),
  culicitatieelectronica character varying(255),
  numaroferteprimite character varying(255),
  subcontractat character varying(255),
  numarcontract character varying(255),
  datacontract character varying(255),
  titlucontract character varying(255),
  valoare character varying(255),
  moneda character varying(255),
  valoareron character varying(255),
  valoareeur character varying(255),
  cpvcodeid character varying(255),
  cpvcode character varying(255),
  numaranuntparticipare character varying(255),
  dataanuntparticipare character varying(255),
  valoareestimataparticipare character varying(255),
  monedavaloareestimataparticipare character varying(255),
  fonduricomunitare character varying(255),
  tipfinantare character varying(255),
  tiplegislatieid character varying(255),
  fondeuropean character varying(255),
  contractperiodic character varying(255),
  depozitegarantii character varying(255),
  modalitatifinantare character varying(255),
  id integer
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.contracte
  OWNER TO postgres;



SELECT * FROM contracte;
ALTER TABLE public.contracte ADD COLUMN id integer;