Script to Transform One CSV File Into Another

[Nello Lucchesi]

I’m new to scripting BBEdit and columnular transformations.

I’d like to create/modify/steal a script to transform a csv file containing financial data from my credit card company’s format into the format exported by Mint (so that I can import the resulting Mint imposter into Quicken for Mac).

Credit card CSV (Input)

Date, Id, Amount, Description

Mint CSV (Output)

Date,Description,Original Description,Amount,Transaction Type,Category,Account Name,Labels,Notes

The new columns will be either:

• Constants,
• Empty.
• Duplicates of other columns

Can someone please point me in the right direction?  Ideally, can you point me towards a script that does most of this and that I can modify?

Thank you.

[Tom Robinson]

Assuming a basic CSV — no commas or line breaks inside quoted fields, this will pick up the 4 fields, duplicate one of them, and insert constants:

Find:

^([^,]+),([^,]+),([^,]+),([^,]+)$

^ anchor to start of line

( start a capture buffer, the first capture buffer is referenced with \1, etc.

[ start a character class

^, search for anything which isn’t a comma

] finish the character class

+ one or more occurrences (use * if you might have empty fields)

) finish the capture buffer

, look for a comma

…

$ anchor to end of line

Replace:

\1,\1,xxx,\2,\3,yyy,zzz,\4

You could also import the CSV into a spreadsheet, do the manipulation, and export as CSV.

Cheers

[Massimo Rainato]

if the CSV fiel had ';' as field separator, open BBEdit cutn paste that 

Date;id;amount;description

01/01/2022;0001;123.00;una descrizione

01/01/2022;0001;123.00;una descrizione

01/01/2022;0001;123.00;una descrizione

(some words are italian)

open find check GREP and Show Matches , and left to right buttons column right click in 3rd with a ? in a circle. 

find: 

^(.*)\;(.*)\;(.*)\;(.*)$

replace

\2;\1;\4;\3

then button replace&find to see what happens.

I hope that helps 

"Un fiorino!"

Massimo Rainato

massimo...@gmail.com

[Johnny Ragadoo]

Better than a spreadsheet, check out Easy Data Transform. I have no connection to that product other than as a happy user.

It lets you build a map of operations against as many spreadsheets, csv files, fixed length records, json, xml, xlxs, etc., as you want. Output goes to a number of formats including xlxs with tabs. You can join, cross reference, and do all kinds of manipulations.

It is a perfect companion to BBEdit for data mining.

I use my copy to mine insanity from our local property tax data. Millions of rows of fixed length data in 30-40 inputs, no problem.

Found lots of insanity, too. Our appraisal district hates me, which I take as a high compliment.

[Nello Lucchesi]

It turns out that importing a Mint CSV (imposter) into Quicken does not solve my problem because this Quicken import feature is designed to be for moving from Mint → Quicken, not for repeatedly loading of transactions into Quicken.

I apologize for whatever wild goose chase I sent anyone on as a result of my misunderstanding.

Thank you for your help.