0

I have a log file I'd like to open in Numbers to make sifting through it a lot easier. The problem is that the format isn't CSV. Each line has about a dozen fields, and the fields themselves are separated by spaces. However, some of those fields are strings, which contain space, so I can't just do a find replace for spaces and put the comma in. One field, the date, is actually formatted like this: [06/Feb/2018:03:27:37 +0000]. This appears to be the only spot where []s are used, so I could find/replace those with "s, but I'm still having trouble importing the document. It places every row in a single column.

So, to summarize, my goal is to take an input file and break up each row so that the space is the delimiter, but it ignores spaces within quotes.

  • replace spaces outside [] with tab characters – user151019 Feb 6 '18 at 19:08
  • How do I do that without replacing the spaces inside [] as well? It's a large file. – Jake T. Feb 6 '18 at 19:37
  • 1
    if you have an editor that can use regex (for example textwrangler or bbedit) or you are willing to use sed (in this case remember the -E flag) or to write a small filter in any language you know (for example perl or python), you can first change the brackets into "s and then use the regex s/\ (?=(?:[^"]*"[^"]*")*[^"]*$)/\t/g (it's not that I am this good with regexes, it's explained here: regex101.com/r/sU3fA2/29) – Gio Valerio Feb 6 '18 at 22:45
  • just out of curiosity, but the file is not fixed length fields (but for the log text), is it? – Gio Valerio Feb 6 '18 at 22:47
  • @GioValerio Do you mean the number of fields per row? If so, it does appear to be fixed length fields, with fields with no data having a '-' symbol instead. I've never used RegEx but dang does it look like you can get fancy with it. Thanks for the link / regex. – Jake T. Feb 7 '18 at 15:27
2

if you have an editor that can use regex (for example textwrangler or bbedit)
or you are willing to use sed (in this case remember the -E flag)
or you can write a small filter in any language you know (for example perl or python),
you can first change the brackets into "s and then use the regex:
s/\ (?=(?:[^"]"[^"]")[^"]$)/\t/g
to convert any space not between quotes into tabs, so that Numbers will import it correctly (it's not that I am this good with regexes, it is explained here: regex101.com/r/sU3fA2/29)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .