Converting text into CSV format

[Howard]

I have this data (a sample below). Each student has four lines of data. I need to convert it into CSV format, so it can work in Excel. How can I do that in BBEdit? I have version 14.6.9.

Sample Data

Student One
1
0
Oct 06, 2024, 09:31 PM
Student Two
1
0
Oct 06, 2024, 08:23 PM
Student Three
1
0
Oct 04, 2024, 01:55 PM

Picture of data

[bruce linde]

fastest way?

   replace every instance of “Student” with “xxxxxStudent”

   replace every line return with “\t” (a tab)

   select all and remove line breaks

   replace every instance of ”xxxxxStudent” with “\rStudent”

   select all, copy, paste into new/blank excel doc

   elapsed time maybe 30 seconds

   8-)

or, wait for actual regex tips and tricks from the gang.   8-)

On Oct 28, 2024, at 2:12 PM, Howard <leadwi...@gmail.com> wrote:

I have this data (a sample below). Each student has four lines of data. I need to convert it into CSV format, so it can work in Excel. How can I do that in BBEdit? I have version 14.6.9.

<CSV Data.jpg>

--
This is the BBEdit Talk public discussion group. If you have a feature request or believe that the application isn't working correctly, please email "sup...@barebones.com" rather than posting here. Follow @bbedit on Mastodon: <https://mastodon.social/@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 view this discussion visit https://groups.google.com/d/msgid/bbedit/f428fcca-c84f-4249-8fc3-82ecbcec53e3n%40googlegroups.com.

<CSV Data.jpg>

[Howard]

In the actual data, instead of "Student One" and so on, are the actual students' first and last names.

[bruce linde]

and are the names “last_name, first_name”? “first_name last_name”? are there ever middle names or inititals?

are the “1”s and “0”s also representative? are they ALWAYS 1’s or 0’s?

when you’re asking folks how best to use bbedit to deal with your actual data, i would suggest posting said actual data… 

sigh.

--
This is the BBEdit Talk public discussion group. If you have a feature request or believe that the application isn't working correctly, please email "sup...@barebones.com" rather than posting here. Follow @bbedit on Mastodon: <https://mastodon.social/@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 view this discussion visit https://groups.google.com/d/msgid/bbedit/91cfa984-07d7-4899-b629-3a23df22a9b6n%40googlegroups.com.

bruce linde
http://clockhappy.com/
510.206.9730 mobile

["Jan Erik Moström"]

On 28 Oct 2024, at 22:56, bruce linde wrote:

> when you’re asking folks how best to use bbedit to deal with your actual data, i would suggest posting said actual data…

Show some example data and the expected output. From what I can see (and how I interpret the question) it could be a very simple regexp or a more complex one.

= jem

[Howard]

There are just first and last names, no middle names nor initials. For privacy reasons, I was unable to use the real names. With regard to the numbers, they are not just zeros and ones. Usually, however, they are from 0-5, but can be higher.

Here is revised sample data;

Sample Data

John Doe
4

1

Oct 06, 2024, 09:31 PM

Peter Pollard
2

0
Oct 06, 2024, 08:23 PM

Cathy Cone
4
3

Oct 04, 2024, 01:55 PM

I would like the above data to be easy for me to put into Excel. Here is how I would like the output to be formatted:

John Doe, 4,1,Oct 06, 2024, 09:31 PM

Peter Pollard,2,0,Oct 06, 2024, 08:23 PM

Cathy Cone,4,3,Oct 04, 2024, 01:55 PM

In Excel, the names and dates will be formatted as text, and the others as numbers.

Thanks,

Howard

[Jim Straus]

Assuming it is always 4 lines:

Search: ([^\t]*)\n(.*)\n(.*)\n(.*)\n

Replace: \1\t\2\t\3\t\4\n

The first part captures the total contents of the four lines.

(The first pattern [^\t]* says only lines without tabs, just to make sure the wildcard doesn’t try to grab the newly generated line)

The second replaces those four lines with a tab separated line

-Jim Straus

On Oct 29, 2024, at 6:06 AM, Howard <leadwi...@gmail.com> wrote:

There are just first and last names, no middle names nor initials. For privacy reasons, I was unable to use the real names. With regard to the numbers, they are not just zeros and ones. Usually, however, they are from 0-5, but can be higher.

--

This is the BBEdit Talk public discussion group. If you have a feature request or believe that the application isn't working correctly, please email "sup...@barebones.com" rather than posting here. Follow @bbedit on Mastodon: <https://mastodon.social/@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 view this discussion visit https://groups.google.com/d/msgid/bbedit/71480dfc-9aaa-48d4-b47d-771a01fcda30n%40googlegroups.com.

[Massimo Rainato]

Well, change “ AM\r” into “ AM@“ and “ PM\r” into “ PM@\r” and pick the same result using another way

--

This is the BBEdit Talk public discussion group. If you have a feature request or believe that the application isn't working correctly, please email "sup...@barebones.com" rather than posting here. Follow @bbedit on Mastodon: <https://mastodon.social/@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 view this discussion visit https://groups.google.com/d/msgid/bbedit/91cfa984-07d7-4899-b629-3a23df22a9b6n%40googlegroups.com.