Using Sed to format text for Basecamp with Textile tables markup

Sometimes I have data in rows and cells in a spreadsheet I want to copy and paste into Basecamp with the tables. You can use Sed, the UNIX stream editor, to automate formatting the tabbed text into text which will appear in tables in Basecamp.

I copy and paste the data from the rows and cells in the spreadsheet spreadsheet into a text file. I'll name my text file "1".

	File 1:
	VLAN Name Subnet
	1 Default 10.1.1.0/24
	10 Ten 10.1.10.0/24
	20 Twenty 10.1.20.0/24
	30 Thirty 10.1.30.0/24
	40 Fourty 10.1.40.0/24
	50 Fifty 10.1.50.0/24
	sed 's/ / | /g' 1 > 2

view raw gistfile1.sh hosted with ❤ by GitHub

Pseudocode:

Read the file named "1" line by line

For each line substitute each tab with " | "

Save the results in a file named "2"

The space after the first / is actually Ctrl-V Tab. If you type Ctrl-V the shell will record the meta character for the key you type. This will replace the tabs with " | " i.e. space pipe space. The g after the last / makes sed do this globally for the entire line not just for the first instance.

	File 2:
	VLAN | Name | Subnet
	1 | Default | 10.1.1.0/24
	10 | Ten | 10.1.10.0/24
	20 | Twenty | 10.1.20.0/24
	30 | Thirty | 10.1.30.0/24
	40 | Fourty | 10.1.40.0/24
	50 | Fifty | 10.1.50.0/24
	sed 's/^/| /' 2 > 3

view raw gistfile1.sh hosted with ❤ by GitHub

Pseudocode:

Read the file named "2" line by line

For each line substitute each first character with "| "

Save the results in a file named "3"

	File 3:
	| VLAN | Name | Subnet
	| 1 | Default | 10.1.1.0/24
	| 10 | Ten | 10.1.10.0/24
	| 20 | Twenty | 10.1.20.0/24
	| 30 | Thirty | 10.1.30.0/24
	| 40 | Fourty | 10.1.40.0/24
	| 50 | Fifty | 10.1.50.0/24
	sed 's/$/ |/' 3 > 4

view raw gistfile1.sh hosted with ❤ by GitHub

Pseudocode:

Read the file named "3" line by line

For each line substitute each last character with " |"

Save the results in a file named "4"

	File 4:
	| VLAN | Name | Subnet |
	| 1 | Default | 10.1.1.0/24 |
	| 10 | Ten | 10.1.10.0/24 |
	| 20 | Twenty | 10.1.20.0/24 |
	| 30 | Thirty | 10.1.30.0/24 |
	| 40 | Fourty | 10.1.40.0/24 |
	| 50 | Fifty | 10.1.50.0/24 |

view raw gistfile1.sh hosted with ❤ by GitHub

Super Sed:

If you use Super Sed this can be done in one command line consisting of the above operations piped one after another because Super Sed supports Perl-like editing of the file in place.

If you use a Macintosh and MacPorts you can install Super Sed with "sudo port install ssed"

	ssed -i 's/ / | /g' 1| ssed -i 's/^/| /' 1| ssed -i 's/$/ |/' 1
	File 1 (Before):
	VLAN Name Subnet
	1 Default 10.1.1.0/24
	10 Ten 10.1.10.0/24
	20 Twenty 10.1.20.0/24
	30 Thirty 10.1.30.0/24
	40 Fourty 10.1.40.0/24
	50 Fifty 10.1.50.0/24
	File 1 (After):
	| VLAN | Name | Subnet |
	| 1 | Default | 10.1.1.0/24 |
	| 10 | Ten | 10.1.10.0/24 |
	| 20 | Twenty | 10.1.20.0/24 |
	| 30 | Thirty | 10.1.30.0/24 |
	| 40 | Fourty | 10.1.40.0/24 |
	| 50 | Fifty | 10.1.50.0/24 |

view raw gistfile1.sh hosted with ❤ by GitHub