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. On Saturday, February 18, 2023 at 9:53:44 PM UTC-6 Tom Robinson wrote: > 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 > > > > On 2023-02-19, at 11:54, Nello Lucchesi <[email protected]> wrote: > > 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. > > > -- This is the BBEdit Talk public discussion group. If you have a feature request or need technical support, please email "[email protected]" rather than posting here. Follow @bbedit on Twitter: <https://twitter.com/bbedit> --- You received this message because you are subscribed to the Google Groups "BBEdit Talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/bbedit/c0276517-3af1-4775-b952-66327fff0bd1n%40googlegroups.com.
