qkJHgdmS

1Firstly you will need a table for the bank statement entries
2```
3CREATE TABLE `bank_statement_entries` (
4  `id` int(11) NOT NULL,
5  `cheque_id` varchar(50) DEFAULT NULL,
6  `date` date DEFAULT NULL,
7  `amount` decimal(13,4) DEFAULT NULL,
8  `subcategory` varchar(50) DEFAULT NULL,
9  `memo` varchar(100) DEFAULT NULL,
10  `my_notes` text
11) ENGINE=MyISAM DEFAULT CHARSET=latin1;
12```
13
14The full script to import a batch of csv statement entries into table `bank_statement_entries`
15```
16DELIMITER $$
17
18/* remove any tables created by a previous run */
19DROP TABLE IF EXISTS `bank_statement_entries_raw`$$
20DROP TABLE IF EXISTS `bank_statement_entries_batch`$$
21
22/* make an empty copy of the master table to put the batch of statements into */
23/* but get rid of the id column */
24CREATE TABLE `bank_statement_entries_batch` LIKE `bank_statement_entries`$$
25ALTER TABLE `bank_statement_entries_batch` DROP `id`$$
26
27/* prepare a table ready for the raw csv import */
28CREATE TABLE `bank_statement_entries_raw` (
29  `Number` varchar(50) DEFAULT NULL,
30  `Date` varchar(100) DEFAULT NULL,
31  `Account` varchar(100) DEFAULT NULL,
32  `Amount` decimal(13,4) DEFAULT NULL,
33  `Subcategory` varchar(50) DEFAULT NULL,
34  `Memo` varchar(100) DEFAULT NULL,
35  `Count` int NOT NULL AUTO_INCREMENT,
36  PRIMARY KEY (Count)
37)$$
38
39/* upload the local csv file */
40LOAD DATA LOCAL INFILE '/home/chris/2018-07-31_to_2018-09-03.csv'                        
41INTO TABLE `bank_statement_entries_raw`
42FIELDS TERMINATED BY ',' 
43ENCLOSED BY '"'
44LINES TERMINATED BY '\n'
45IGNORE 1 ROWS$$
46
47/* populate the batch table */
48/* do any transformations here */
49INSERT INTO bank_statement_entries_batch (
50  cheque_id,
51  date,
52  amount,
53  subcategory,
54  memo) 
55SELECT 
56   `Number`
57	,DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%Y-%m-%d')
58	,Amount
59	,Subcategory
60	,Memo
61FROM `bank_statement_entries_raw`
62ORDER BY `Count` DESC$$
63
64/* now insert the newly prepared batch of statements in to the master table */
65INSERT INTO `bank_statement_entries` (
66  cheque_id,
67  date,
68  amount,
69  subcategory,
70  memo,
71  my_notes
72)
73SELECT 
74  cheque_id,
75  date,
76  amount,
77  subcategory,
78  memo,
79  my_notes
80FROM `bank_statement_entries_batch`$$
81
82DELIMITER ;
83```