dataset-finance: trying to map columns to properties
I have a csv file with 13 columns.
The header row is:
"Date","Check","Merchant","Category","SubCategory","Memo","Credit","Debit","Bill Pay","Debit Card","Account","Balance","Status"
The first data row is:
"","","","","","","","","","","Cash","56",""
I have created a SQL table to LOAD DATA into. When I ran
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv' into dc_data_finance.transact
I got Cash into “Account” property (correct) and I saw 56 in “Status” property. I want to skip the “Balance” column. I updated my LOAD DATA statement to:
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv'
INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status)
VALUES("Date","Check","Merchant","Category","SubCategory","Memo","Credit","Debit","Bill Pay","Debit Card","Account","Status")
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv'
INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status)
VALUES(Date,Check,Merchant,Category,SubCategory,Memo,Credit,Debit,Bill Pay,Debit Card,Account,Status)
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv'
INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status)
VALUES('Date',Check,Merchant,Category,SubCategory,Memo,Credit,Debit,Bill Pay,Debit Card,Account,Status)
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv'
INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status)
VALUES("Date",Check,Merchant,Category,SubCategory,Memo,Credit,Debit,Bill Pay,Debit Card,Account,Status)
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv'
INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status)
VALUES("Date","Check",Merchant,Category,SubCategory,Memo,Credit,Debit,Bill Pay,Debit Card,Account,Status)
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv'
INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status)
VALUES("Date","Check",Merchant,Category,SubCategory,Memo,Credit,Debit,"Bill Pay","Debit Card",Account,Status)
None of these variations brought the desired outcome. Is it user error or a software bug?
User Error identified: I missed adding USING {"from":{"file":{"header":"1"}}} at the end. The correct statement that skipped "Balance" column was:
LOAD DATA FROM FILE '/irisrun/repo/data/finance-transactions-08-Jan-2022_08-25.csv'
INTO dc_data_finance.transact(TranDate,CheckNumber,Merchant,Category,SubCategory,Memo,Credit,Debit,BillPay,DebitCard,Account,Status)
VALUES("Date","Check",Merchant,Category,SubCategory,Memo,Credit,Debit,"Bill Pay","Debit Card",Account,Status)
USING {"from":{"file":{"header":"1"}}}