BBEdit 12 columns

[jgill]

Unless I am completely misunderstanding, I thought that BBEdit 12 could open a CSV file and display it in columns like a spreadsheet. I can find any way to do this.

My bank allows me to download a CSV file of my account and Numbers doesn't know how to handle it because of the quote marks and commas. It must be an Excel thing, but it doesn't work on a Mac.

[Ted Burger]

Here is a script that I keep in Bbedit's script folder to clean up Excel's CSV formatting.

I get why Excel does this, but I do not get why they do it even in a TAB delimited file.

tell application "BBEdit"

activate

replace "\" " using " " searching in text 1 of text window 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

replace " \"" using " " searching in text 1 of text window 1 options {search mode:literal, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

replace "^\"\\(" using "\\(" searching in text 1 of text window 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

replace "^\"" using "" searching in text 1 of text window 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

replace "\"$" using "" searching in text 1 of text window 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

replace "\"\"" using "\"" searching in text 1 of text window 1 options {search mode:grep, starting at top:true, wrap around:false, backwards:false, case sensitive:false, match words:false, extend selection:false}

end tell

Thanks,

Ted

***********************  Ted Burger  ****************************
t...@tobsupport.com      *********     www.tobsupport.com

On Oct 15, 2017, at 10:45 AM, jgill <joegille...@gmail.com> wrote:

Unless I am completely misunderstanding, I thought that BBEdit 12 could open a CSV file and display it in columns like a spreadsheet. I can find any way to do this.

My bank allows me to download a CSV file of my account and Numbers doesn't know how to handle it because of the quote marks and commas. It must be an Excel thing, but it doesn't work on a Mac.

--
This is the BBEdit Talk public discussion group. If you have a
feature request or would like to report a problem, please email
"sup...@barebones.com" rather than posting to the group.
Follow @bbedit on Twitter: <http://www.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 bbedit+un...@googlegroups.com.
To post to this group, send email to bbe...@googlegroups.com.
Visit this group at https://groups.google.com/group/bbedit.

[jgill]

Thanks for the script Ted but it does absolutely nothing to my CSV file. I've tried running it from Script Debugger and from the BBEdit script menu but there are no changes to the file whatsoever.

[jgill]

I think that the format of the Bank's CSV has been script generated and is not strictly Excel CSV format.

[Ted Burger]

I just opened a CSV file in Numbers and it worked just fine.

Thanks,

Ted

***********************  Ted Burger  ****************************
t...@tobsupport.com      *********     www.tobsupport.com

On Oct 15, 2017, at 11:13 AM, jgill <joegille...@gmail.com> wrote:

I think that the format of the Bank's CSV has been script generated and is not strictly Excel CSV format.

[Ted Burger]

A true CSV file looks like:

"15412049","15552675","CS1 - ""Tools"" to Manage Performance, Enhance Communication, and Coach for Results","139730","Overall presenter rating","5","June 20 2017 09:34AM"

Some simple rules:

Columns are separated by commas.

Columns are enclosed in quotes.

Quotes within a column are double quoted.

Thanks,

Ted

***********************  Ted Burger  ****************************
t...@tobsupport.com      *********     www.tobsupport.com

On Oct 15, 2017, at 11:13 AM, jgill <joegille...@gmail.com> wrote:

I think that the format of the Bank's CSV has been script generated and is not strictly Excel CSV format.

[jgill]

Thanks for your help Ted. Yes, Numbers can open CSV files, it just can't open CSV files from *my Bank* correctly. As I said, they are script generated and probably work fine in Excel, but they don't work in Numbers. They are non-standard to all accounts! But anyway, my original question was about viewing CSV files in columns in BBEdit, I can parse the maverick Bank CSV files into proper CSV.

On Sunday, October 15, 2017 at 3:45:25 PM UTC+1, jgill wrote:

[Scott in Pollock]

It won't display it any differently than any other text file. The column support added allows you to copy/paste/delete/move text that makes up a column in a delimited file. There is a variety of conventions for CSV files with the most common looking like:

"item1 of record 1","item2 of record 1","item3 of record 1","item4 of record 1"

"item1 of record 2","item2 of record 2","item3 of record 2","item4 of record 2"

Numbers should have no problem with this kind of CSV data. If your bank's data is in a different format post an example.

[jgill]

Here is a typical line...

06/04/2010,DPC,"'CALL REF.NO. 0552 , TO A/C 03520000",300.00,130117.86,"'Gxxxx J &amp; M","'504237-00001424",

The HTML &amp; is the giveaway that the CSV is being generated from a HTML file. When I search and replace '&amp;' with '&', the file displays correctly.

Anyway, you answered my question. BBEdit can't display columns visually.

On Sunday, October 15, 2017 at 3:45:25 PM UTC+1, jgill wrote:

[Scott in Pollock]

You can use Markup->Utilities->Translate HTML to Text… to strip out any tags, elements, and escapes in one step.

[Ted Burger]

If that file were really in correct Excel CSV format you could do the following.

Search for "," quotecommaquote and replace it with a TAB character backslasht.

Then goto BBedit Preferences in Editor Defaults and set the "Tab Width" to a bigger number like 10.

Now for a file like yours, with somewhat narrow consistent columns, it will be displayed in columns in BBedit.

Thanks,

Ted

***********************  Ted Burger  ****************************
t...@tobsupport.com      *********     www.tobsupport.com

[jgill]

Yes, converting &amp; to & is not the issue here. The issue is that the downloaded file won't open correctly in Numbers without processing it in BBEdit and BBEdit won't give me the columnar view by itself, so it is a two step process. Okay, I can live with that and build a suitable script to handle it.

I've just run the gauntlet of the bank's support line - who didn't even know what an ampersand was until I explained. There doesn't seem to be any way for a mere mortal to contact anybody concerned with web development at NatWest. 'Thanks for the feedback' was all I could get. Grrr!

[Bruce Van Allen]

On 10/15/17 at 8:45 AM, joegille...@gmail.com (jgill) wrote:

>06/04/2010,DPC,"'CALL REF.NO. 0552 , TO A/C
>03520000",300.00,130117.86,"'Gxxxx J &amp; M","'504237-00001424",

As I read that line, the separated columns are:

06/04/2010
DPC

"'CALL REF.NO. 0552 , TO A/C 03520000"

"'Gxxxx J &amp; M"
"'504237-00001424"

Normally, .csv files quote data to eliminate ambiguity as to the
column separators, etc.

Your bank's system seems to be doing this with an extra single
quote (') after the opening double quote.

So you *might* be able to do a search & replace: find all cases
of "' and replace with " (that's find single-quote double-quote,
replace with just a double-quote). Then see whether Numbers will
open the file correctly.

HTH
--

- Bruce

_bruce__van_allen__santa_cruz__ca_

[Jeffrey Jones]

On Oct 15, 2017, at 11:45 AM, jgill <joegille...@gmail.com> wrote:
>

> Here is a typical line...
>
> 06/04/2010,DPC,"'CALL REF.NO. 0552 , TO A/C 03520000",300.00,130117.86,"'Gxxxx J &amp; M","'504237-00001424",
>
> The HTML &amp; is the giveaway that the CSV is being generated from a HTML file. When I search and replace '&amp;' with '&', the file displays correctly.

I don't understand why having a "&amp;" in the file would be any problem for Numbers. In fact, it is not a problem in experiments I did.

If I save your "typical line" as a .csv file, it opens with no problem in Numbers. I tried duplicating the line several times to make a multi-line CSV; still no problem for Numbers (v 4.3).

Could there be something else that BBEdit is changing? Perhaps the &amp; code is a non sequitur? I thought about line endings, but I tried all three BBEdit choices in my experiment. Numbers seems agnostic. Unless maybe your file has mixed line endings?

[jgill]

It has nothing to do with BBEdit, the downloaded CSV file doesn't parse correctly in Numbers. The &amp; confuses the column breaks so I only get two columns which border on the semicolon - but I can fix this by replacing '&amp;' with '&' in BBEdit. It is the bank that is screwing things up and I have reported it.

[Ted Burger]

As I indicated before, the correct column separator in a CSV file is a "," (quotecommaquote) not just a simple , as it appears to be in your sample.

When I look at your sample line I only see one instance of "," therefore you should only have two columns in that line.

It would be interesting to see what happens if you were to replace all the , characters with "," just to see what Numbers would do with it then.

Thanks,

Ted

***********************  Ted Burger  ****************************
t...@tobsupport.com      *********     www.tobsupport.com

On Oct 15, 2017, at 4:07 PM, jgill <joegille...@gmail.com> wrote:

It has nothing to do with BBEdit, the downloaded CSV file doesn't parse correctly in Numbers. The &amp; confuses the column breaks so I only get two columns which border on the semicolon - but I can fix this by replacing '&amp;' with '&' in BBEdit. It is the bank that is screwing things up and I have reported it.

[Tom Robinson]

The proposed spec, and in my experience, says the quotes are optional:

> 5. Each field may or may not be enclosed in double quotes (however
> some programs, such as Microsoft Excel, do not use double quotes
> at all). If fields are not enclosed with double quotes, then
> double quotes may not appear inside the fields. For example:
>
> "aaa","bbb","ccc" CRLF
> zzz,yyy,xxx

https://www.ietf.org/rfc/rfc4180.txt

I parse this:

06/04/2010,DPC,"'CALL REF.NO. 0552 , TO A/C 03520000",300.00,130117.86,"'Gxxxx J &amp; M","'504237-00001424”,

as having these field boundaries:

06/04/2010,
DPC,

"'CALL REF.NO. 0552 , TO A/C 03520000",
300.00,
130117.86,
"'Gxxxx J &amp; M",
"'504237-00001424",

<empty field>

Which is how both Numbers 4.3 and LibreOffice 5.4.1.2 on MacOS read it — so I’m not seeing the problem. I’m guessing OP would like different column splits?

Cheers

[jgill]

I don't want to put my bank account statement online, for obvious reasons but if I change '&amp;' to '&', it works as expected in Numbers 4.3 otherwise it doesn't. The semicolon throws everything out.

[Bee]

Out of curiosity and completeness...
Have you tried LibreOffice Calc?