Re: BBEdit 12 columns

[Bee]

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

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

Re: BBEdit 12 columns

[jgill]

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

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

Re: BBEdit 12 columns

[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 0352",300.00,130117.86,"'G 
J  M","'504237-1424”,

as having these field boundaries:

06/04/2010,
DPC,
"'CALL REF.NO. 0552 , TO A/C 0352",
300.00,
130117.86,
"'G J  M",
"'504237-1424",


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


> On 2017-10-16, at 04:27, Ted Burger  wrote:
> 
> 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

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

Re: BBEdit 12 columns

[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  wrote:
> 
> It has nothing to do with BBEdit, the downloaded CSV file doesn't parse 
> correctly in Numbers. The  confuses the column breaks so I only get two 
> columns which border on the semicolon - but I can fix this by replacing 
> '' with '&' in BBEdit. It is the bank that is screwing things up and I 
> have reported it.
> 
> 
> -- 
> This is the BBEdit Talk public discussion group. If you have a 
> feature request or would like to report a problem, please email
> "supp...@barebones.com" rather than posting to the group.
> Follow @bbedit on Twitter:  >
> --- 
> 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+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to bbedit@googlegroups.com 
> .
> Visit this group at https://groups.google.com/group/bbedit 
> .

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

Re: BBEdit 12 columns

[jgill]

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

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

Re: BBEdit 12 columns

[Jeffrey Jones]

On Oct 15, 2017, at 11:45 AM, jgill  wrote:
> 
> Here is a typical line...
> 
> 06/04/2010,DPC,"'CALL REF.NO. 0552 , TO A/C 
> 0352",300.00,130117.86,"'G J  M","'504237-1424",
> 
> The HTML  is the giveaway that the CSV is being generated from a HTML 
> file. When I search and replace '' with '&', the file displays correctly.

I don't understand why having a "" 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  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?

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

Re: BBEdit 12 columns

[Bruce Van Allen]

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

06/04/2010,DPC,"'CALL REF.NO. 0552 , TO A/C 
0352",300.00,130117.86,"'G J  M","'504237-1424",


As I read that line, the separated columns are:

06/04/2010
DPC
"'CALL REF.NO. 0552 , TO A/C 0352"
"'G J  M"
"'504237-1424"

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_

--
This is the BBEdit Talk public discussion group. If you have a 
feature request or would like to report a problem, please email

"supp...@barebones.com" rather than posting to the group.
Follow @bbedit on Twitter: 
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to bbedit@googlegroups.com.
Visit this group at https://groups.google.com/group/bbedit.

Re: BBEdit 12 columns

[jgill]

Yes, converting  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!

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

Re: BBEdit 12 columns

[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



> On Oct 15, 2017, at 11:45 AM, jgill  wrote:
> 
> Here is a typical line...
> 
> 06/04/2010,DPC,"'CALL REF.NO. 0552 , TO A/C 
> 0352",300.00,130117.86,"'G J  M","'504237-1424",
> 
> The HTML  is the giveaway that the CSV is being generated from a HTML 
> file. When I search and replace '' 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:
> 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
> "supp...@barebones.com" rather than posting to the group.
> Follow @bbedit on Twitter:  >
> --- 
> 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+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to bbedit@googlegroups.com 
> .
> Visit this group at https://groups.google.com/group/bbedit 
> .

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

Re: BBEdit 12 columns

[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.

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

Re: BBEdit 12 columns

[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:
>
> 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
"supp...@barebones.com" rather than posting to the group.
Follow @bbedit on Twitter: 
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to bbedit@googlegroups.com.
Visit this group at https://groups.google.com/group/bbedit.

Re: BBEdit 12 columns

[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  wrote:
> 
> I think that the format of the Bank's CSV has been script generated and is 
> not strictly Excel CSV format.
> 
> -- 
> This is the BBEdit Talk public discussion group. If you have a 
> feature request or would like to report a problem, please email
> "supp...@barebones.com" rather than posting to the group.
> Follow @bbedit on Twitter:  >
> --- 
> 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+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to bbedit@googlegroups.com 
> .
> Visit this group at https://groups.google.com/group/bbedit 
> .

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

Re: BBEdit 12 columns

[jgill]

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

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

Re: BBEdit 12 columns

[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.

On Sunday, October 15, 2017 at 3:45:25 PM UTC+1, jgill 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
"supp...@barebones.com" rather than posting to the group.
Follow @bbedit on Twitter: 
--- 
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+unsubscr...@googlegroups.com.
To post to this group, send email to bbedit@googlegroups.com.
Visit this group at https://groups.google.com/group/bbedit.

Re: BBEdit 12 columns

[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  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
> "supp...@barebones.com" rather than posting to the group.
> Follow @bbedit on Twitter:  >
> --- 
> 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+unsubscr...@googlegroups.com 
> .
> To post to this group, send email to bbedit@googlegroups.com 
> .
> Visit this group at https://groups.google.com/group/bbedit 
> .

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

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.

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