XU3sXW2E

1#!/bin/bash
2
3# show commands being executed, per debug
4# set -x
5
6# define database connectivity
7_db="xxxxxxxx"
8_db_host="xxxxxxxx"
9_db_user="xxxxxxx"
10# _db_password="xxxxxx"
11
12mysql_config_editor set --host=$_db_host --user=$_db_user --password
13# define directory containing CSV files
14_csv_directory="/path/to/csv"
15
16# go into directory
17cd $_csv_directory
18
19# get a list of CSV files in directory
20_csv_files=`ls -1 *.csv`
21
22# loop through csv files
23for _csv_file in ${_csv_files[@]}
24do
25
26  # remove file extension
27  _csv_file_extensionless=`echo $_csv_file | sed 's/\(.*\)\..*/\1/'`
28
29  # define table name
30  _table_name="${_csv_file_extensionless}"
31
32  # get header columns from CSV file
33  _header_columns=`head -1 $_csv_directory/$_csv_file | tr ',' '\n' | sed 's/^"//' | sed 's/"$//' | sed 's/ /_/g'`
34  _header_columns_string=`head -1 $_csv_directory/$_csv_file | sed 's/ /_/g' | sed 's/"//g'`
35
36  echo "---> Creating table \`$_table_name\`"
37
38  # ensure table exists
39  mysql $_db << eof
40    CREATE TABLE IF NOT EXISTS \`$_table_name\` (name_temp_xyz VARCHAR(20)) ENGINE=InnoDB CHARACTER SET=utf8mb4;
41eof
42  has_primary=false
43  # loop through header columns
44  for _header in ${_header_columns[@]}
45  do
46
47    # add column
48    column_type="text CHARACTER SET utf8mb4"
49    echo "adding \`$_header\`"
50    if [[ $_header == *"_id"* ]]
51    then
52      column_type="integer"
53    fi
54    if [[ $_header == "id" ]]
55    then
56      column_type="integer"
57      has_primary=true
58    fi
59    echo "-> type is $column_type"
60    # echo "exec: alter table \`$_table_name\` add column \`$_header\` $column_type collate utf8_general_ci"
61    mysql $_db --execute="alter table \`$_table_name\` add column \`$_header\` $column_type"
62    if [[ $_header == *"_id"* ]]
63    then
64      mysql $_db --execute="alter table \`$_table_name\` add index ($_header)"
65      echo "-> indexed"
66    fi
67    if [[ $_header == "id" ]]
68    then
69      mysql $_db --execute="alter table \`$_table_name\` add primary key ($_header)"
70      echo "-> primary key"
71    fi
72  done
73
74
75  echo "removing temp column"
76  mysql $_db --execute="alter table \`$_table_name\` drop column \`name_temp_xyz\`"
77  echo "importing datas..."
78  mysql $_db --enable-local-infile << eof
79  LOAD DATA LOCAL INFILE "$_csv_directory/$_csv_file"
80  INTO TABLE $_table_name
81  CHARACTER SET UTF8
82  FIELDS TERMINATED BY ','
83  OPTIONALLY ENCLOSED BY '"'
84  LINES TERMINATED BY '\n'
85  IGNORE 1 ROWS
86eof
87  if [ "$has_primary" = false ] ; then
88    mysql $_db --execute="ALTER TABLE \`$_table_name\` ADD \`id\` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id)"
89    echo "added primary key"
90  fi
91done
92exit