New post
Article
· Jan 8, 2022 2m read

dataset-finance: trying to map columns to properties

#SQL #InterSystems IRIS

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?

Discussion (1)1
Log in or sign up to continue
Oliver Wilms · Jan 8, 2022

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"}}}

0 0