Convert Python Pretty table to CSV using shell or batch command line

Question

Whats an easy way convert the output of Python Pretty table to grammatically usable format such as CSV.

The output looks like this :

C:\test> nova list

    spu+--------------------------------------+--------+--------+------------+-------------+-----------------------------------+
     | ID                                   | Name   | Status | Task State | Power State | Networks                          |
     +--------------------------------------+--------+--------+------------+-------------+-----------------------------------+
     | 6bca09f8-a320-44d4-a11f-647dcec0aaa1 | tester | ACTIVE | -          |  Running     | OpenStack-net=10.0.0.1, 10.0.0.3 |
     +--------------------------------------+--------+--------+------------+-------------+-----------------------------------+

Answer 1

Perhaps this will get you close:

nova list | grep -v '\-\-\-\-' | sed 's/^[^|]\+|//g' | sed 's/|\(.\)/,\1/g' | tr '|' '\n'

This will strip the --- lines Remove the leading | Replace all but the last | with , Replace the last | with \n

Answer 2

Here's a real ugly one-liner

import csv

s = """\
  spu+--------------------------------------+--------+--------+------------+-------------+-----------------------------------+
     | ID                                   | Name   | Status | Task State | Power State | Networks                          |
     +--------------------------------------+--------+--------+------------+-------------+-----------------------------------+
     | 6bca09f8-a320-44d4-a11f-647dcec0aaa1 | tester | ACTIVE | -          |  Running     | OpenStack-net=10.0.0.1, 10.0.0.3 |
     +--------------------------------------+--------+--------+------------+-------------+-----------------------------------+"""

result = [tuple(filter(None, map(str.strip, splitline))) for line in s.splitlines() for splitline in [line.split("|")] if len(splitline) > 1]

with open('output.csv', 'wb') as outcsv:
    writer = csv.writer(outcsv)
    writer.writerows(result)

I can unwrap it a bit to make it nicer:

splitlines = s.splitlines()
splitdata = line.split("|")
splitdata = filter(lambda line: len(line) > 1, data)
# toss the lines that don't have any data in them -- pure separator lines
header, *data = [[field.strip() for field in line if field.strip()] for line in splitdata]

result = [header] + data
# I'm really just separating these, then re-joining them, but sometimes having
# the headers separately is an important thing!

Or possibly more helpful:

result = []

for line in s.splitlines():
    splitdata = line.split("|")
    if len(splitdata) == 1:
        continue  # skip lines with no separators
    linedata = []
    for field in splitdata:
        field = field.strip()
        if field:
            linedata.append(field)
    result.append(linedata)

Answer 3

@AdamSmith's answer has a nice method for parsing the raw table string. Here are a few additions to turn it into a generic function (I chose not to use the csv module so there are no additional dependencies)

def ptable_to_csv(table, filename, headers=True):
    """Save PrettyTable results to a CSV file.

    Adapted from @AdamSmith https://stackoverflow.com/questions/32128226

    :param PrettyTable table: Table object to get data from.
    :param str filename: Filepath for the output CSV.
    :param bool headers: Whether to include the header row in the CSV.
    :return: None
    """
    raw = table.get_string()
    data = [tuple(filter(None, map(str.strip, splitline)))
            for line in raw.splitlines()
            for splitline in [line.split('|')] if len(splitline) > 1]
    if table.title is not None:
        data = data[1:]
    if not headers:
        data = data[1:]
    with open(filename, 'w') as f:
        for d in data:
            f.write('{}\n'.format(','.join(d)))

Answer 4

Here's a solution using a regular expression. It also works for an arbitrary number of columns (the number of columns is determined by counting the number of plus signs in the first input line).

input_string = """spu+--------------------------------------+--------+--------+------------+-------------+-----------------------------------+
     | ID                                   | Name   | Status | Task State | Power State | Networks                          |
     +--------------------------------------+--------+--------+------------+-------------+-----------------------------------+
     | 6bca09f8-a320-44d4-a11f-647dcec0aaa1 | tester | ACTIVE | -          |  Running     | OpenStack-net=10.0.0.1, 10.0.0.3 |
     +--------------------------------------+--------+--------+------------+-------------+-----------------------------------+"""

import re, csv, sys
def pretty_table_to_tuples(input_str):
    lines = input_str.split("\n")
    num_columns = len(re.findall("\+", lines[0])) - 1
    line_regex = r"\|" + (r" +(.*?) +\|"*num_columns)
    for line in lines:
        m = re.match(line_regex, line.strip())
        if m:
            yield m.groups()

w = csv.writer(sys.stdout)
w.writerows(pretty_table_to_tuples(input_string))