Import Barclays Bank statement

Updated: 24 September 2022

Create a table for the bank statement entries

CREATE TABLE `bank_statement_entries` (
  `id` int(11) NOT NULL,
  `cheque_id` varchar(50) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `amount` decimal(13,4) DEFAULT NULL,
  `subcategory` varchar(50) DEFAULT NULL,
  `memo` varchar(100) DEFAULT NULL,
  `my_notes` text
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

SQL script for importing batch of csv statement entries into table `bank_statement_entries`

DELIMITER $$

/* remove any tables created by a previous run */
DROP TABLE IF EXISTS `bank_statement_entries_raw`$$
DROP TABLE IF EXISTS `bank_statement_entries_batch`$$

/* make an empty copy of the master table to put the batch of statements into */
/* but get rid of the id column */
CREATE TABLE `bank_statement_entries_batch` LIKE `bank_statement_entries`$$
ALTER TABLE `bank_statement_entries_batch` DROP `id`$$

/* prepare a table ready for the raw csv import */
CREATE TABLE `bank_statement_entries_raw` (
  `Number` varchar(50) DEFAULT NULL,
  `Date` varchar(100) DEFAULT NULL,
  `Account` varchar(100) DEFAULT NULL,
  `Amount` decimal(13,4) DEFAULT NULL,
  `Subcategory` varchar(50) DEFAULT NULL,
  `Memo` varchar(100) DEFAULT NULL,
  `Count` int NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (Count)
)$$

/* upload the local csv file */
LOAD DATA LOCAL INFILE '/home/chris/2018-07-31_to_2018-09-03.csv'                        
INTO TABLE `bank_statement_entries_raw`
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS$$

/* populate the batch table */
/* do any transformations here */
INSERT INTO bank_statement_entries_batch (
  cheque_id,
  date,
  amount,
  subcategory,
  memo) 
SELECT 
   `Number`
	,DATE_FORMAT(STR_TO_DATE(Date, '%d/%m/%Y'), '%Y-%m-%d')
	,Amount
	,Subcategory
	,Memo
FROM `bank_statement_entries_raw`
ORDER BY `Count` DESC$$

/* now insert the newly prepared batch of statements in to the master table */
INSERT INTO `bank_statement_entries` (
  cheque_id,
  date,
  amount,
  subcategory,
  memo,
  my_notes
)
SELECT 
  cheque_id,
  date,
  amount,
  subcategory,
  memo,
  my_notes
FROM `bank_statement_entries_batch`$$

DELIMITER ;