.::: Convert Query select MariaDB MarinaDB MySQL to Excel CSV include using script scheduler :::.

A. Using cmd MariaDB

1. check query using normal query

select * from pembelian;

 

2. create folder

mkdir /convert
chmod 777 /convert
 
3. convert to csv or excel

SELECT 'KODE_PEMBELIAN', 'KODE_BARANG', 'KODE_CUSTOMER', 'TANGGAL_PEMBELIAN', 'JUMLAH_PEMBELIAN'
UNION ALL
SELECT * FROM pembelian
INTO OUTFILE '/convert/pembelian.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
 

4. open using cat
[root@teguhth ~]# cat /convert/pembelian.csv
"KODE_PEMBELIAN","KODE_BARANG","KODE_CUSTOMER","TANGGAL_PEMBELIAN","JUMLAH_PEMBELIAN"
"BEL-E001","ELK-01","J-0001","2002-05-20","3"
"BEL-E002","ELK-01","J-0001","2002-05-21","4"
"BEL-E003","ELK-01","J-0002","2002-05-20","2"
"BEL-E004","ELK-01","B-0001","2002-05-20","2"
"BEL-E005","ELK-01","B-0002","2002-05-22","3"
"BEL-E006","ELK-02","J-0001","2002-06-24","1"
"BEL-E007","ELK-02","J-0002","2002-06-24","1"
"BEL-E008","ELK-02","B-0001","2002-06-25","2"
"BEL-E009","ELK-02","B-0002","2002-06-25","2"
"BEL-E010","ELK-03","J-0001","2002-06-20","5"
"BEL-E011","ELK-03","J-0002","2002-07-02","4"
"BEL-E012","ELK-03","B-0001","2002-07-02","6"
"BEL-E013","ELK-03","J-0001","2002-07-10","5"
"BEL-E014","ELK-04","J-0002","2002-07-15","12"
"BEL-E015","ELK-04","B-0002","2002-07-17","15"
[root@teguhth ~]#
 

5. open using excel
 

B. Using redirect output

1. check query using normal query

2. create folder

mkdir /convert
chmod 777 /convert

3. convert to csv or excel

mysql -u root -pxxx -e "SELECT * FROM pembelian;" --batch --silent --database=teguhth > /convert/pembelian_direct.csv

echo "KODE_PEMBELIAN,KODE_BARANG,KODE_CUSTOMER,TANGGAL_PEMBELIAN,JUMLAH_PEMBELIAN" > /convert/pembelian_with_header.csv
cat /convert/pembelian_direct.csv >> /convert/pembelian_with_header.csv
 

4. cat filename

cat /convert/pembelian_with_header.csv

 

5. open using excel 
 

 

C. Using cmd MariaDB sample union

1. check query using normal query
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER;
 

2. create folder

mkdir /convert
chmod 777 /convert

3. convert to csv or excel

SELECT 'NAMA_BARANG', 'NAMA_SUPLIER', 'TANGGAL_PASOK', 'JUMLAH_PASOK'
UNION ALL
select b.NAMA_BARANG,s.NAMA_SUPLIER,p.TANGGAL_PASOK,p.JUMLAH_PASOK from barang b,suplier s,pasok p
where b.KODE_BARANG=p.KODE_BARANG and s.KODE_SUPLIER=p.KODE_SUPLIER
INTO OUTFILE '/convert/sample_union.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';
 

4. cat filename

cat /convert/sample_union.csv
 

5. open using excel 

 

D. Create Script and crontab

1. Using use database

mysql -uroot -pxxx -e "USE teguhth; \
SET GLOBAL max_statement_time=0; \
SELECT 'NAMA_BARANG', 'NAMA_SUPLIER', 'TANGGAL_PASOK', 'JUMLAH_PASOK' \
UNION ALL \
SELECT b.NAMA_BARANG, s.NAMA_SUPLIER, p.TANGGAL_PASOK, p.JUMLAH_PASOK \
FROM barang b, suplier s, pasok p \
WHERE b.KODE_BARANG = p.KODE_BARANG AND s.KODE_SUPLIER = p.KODE_SUPLIER \
INTO OUTFILE '/convert/sample_union_usedb.csv' \
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' \
LINES TERMINATED BY '\n'; \
SET GLOBAL max_statement_time=300;"

2. using direct query
mysql -uroot -pxxx -e "SET GLOBAL max_statement_time=0; \
SELECT 'NAMA_BARANG', 'NAMA_SUPLIER', 'TANGGAL_PASOK', 'JUMLAH_PASOK' \
UNION ALL \
SELECT b.NAMA_BARANG, s.NAMA_SUPLIER, p.TANGGAL_PASOK, p.JUMLAH_PASOK \
FROM teguhth.barang b, teguhth.suplier s, teguhth.pasok p \
WHERE b.KODE_BARANG = p.KODE_BARANG AND s.KODE_SUPLIER = p.KODE_SUPLIER \
INTO OUTFILE '/convert/sample_union_unix_query.csv' \
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' \
LINES TERMINATED BY '\n'; \
SET GLOBAL max_statement_time=300;"

3. using shell script

[root@teguhth convert]# cat convert_to_csv.sh
echo "remove file existing"
rm -f /convert/sample_union_unix_query.csv

echo "convert to csv"
mysql -uroot -pxxx -e "SET GLOBAL max_statement_time=0; \
SELECT 'NAMA_BARANG', 'NAMA_SUPLIER', 'TANGGAL_PASOK', 'JUMLAH_PASOK' \
UNION ALL \
SELECT b.NAMA_BARANG, s.NAMA_SUPLIER, p.TANGGAL_PASOK, p.JUMLAH_PASOK \
FROM teguhth.barang b, teguhth.suplier s, teguhth.pasok p \
WHERE b.KODE_BARANG = p.KODE_BARANG AND s.KODE_SUPLIER = p.KODE_SUPLIER \
INTO OUTFILE '/convert/sample_union_unix_query.csv' \
FIELDS TERMINATED BY ',' ENCLOSED BY '\"' \
LINES TERMINATED BY '\n'; \
SET GLOBAL max_statement_time=300;"

echo "read data output"
cat /convert/sample_union_unix_query.csv
echo "convert is done"

FILE_SIZE=$(ls -lh /convert/sample_union_unix_query.csv | awk '{print $5}')
echo "Size File sample_union_unix_query.csv is $FILE_SIZE"

[root@teguhth convert]# 

 

 4. result