Take the 2-minute tour ×

I've got a problem at work that requires me to insheet some MASSIVE tab-separated values files (think 8-15 GB .txt files) into my PostgreSQL DB, but I've run into a problem with the way the data was formatted in the first place. Basically, the way we are given the data (and unfortunately we cannot get the data in a better format), there are some backslashes that appear and cause a return/new line.

So, there are lines (rows of data, tab-delim) that get chopped up into multiple lines, where the last character of line n is a \ , and the first character of line n+1 is a tab. Usually line n will be broken up into 1-3 additional lines (e.g. line n ends in a "\", lines n+1 and n+2 start with a tab and end with a "\", and line n+3 starts with a tab).

I need to write a script that can work with these huge files (this will run on a linux server with 192 GB of RAM) to look for the lines that begin with a tab, and then remove the return (and "\" wherever it exists) and save the text file.

To recap, the customer's logging program splits the original line N into lines n, n+1, and sometimes n+2 and n+3 (depending on how many \ characters appear in line N), and I need to write a python script to recreate the original line N.

share|improve this question

closed as not a real question by casperOne Jul 11 '12 at 14:42


Post a small sample with anything sensitive redacted. Expected input and output are a good way to illustrate this kind of question. –  MattH Jul 10 '12 at 14:28
add comment

3 Answers

up vote 0 down vote accepted

This is based on @user665637's good answer.


import re, sys

pat_incomplete = re.compile(r'\\\s*$')
pat_indented = re.compile(r'^\t')

    _, fname_in, fname_out = sys.argv
except ValueError:
    print("Usage: python line_joiner.py <input_filename> <output_filename>")

with open(fname_in) as in_f, open(fname_out, "w") as out_f:
    lines = iter(in_f)
        line = next(lines)
        s = pat_incomplete.sub('', line)
    except StopIteration:
        print("Input file did not contain any data")

    for line in lines:
        line = pat_incomplete.sub('', line)
        if pat_indented.match(line):
            s += pat_indented.sub('',line)
            s = line


  • Uses "raw strings" for the regular expressions, which are easier to read.

  • Takes an output filename from the command-line arguments and writes to that file. Prints a message and exits if the user provides the wrong number of arguments. When we unpack sys.argv to get the arguments, we use the convention of using the variable name _ for parts we don't care about.

  • Does not strip line endings, so the output file will have the same sort of line-endings as the input file. (When joining lines, of course it strips the line endings to do the join.)

  • Does not filter out blank lines from the input. It's a little bit tricky, but by making an iterator and calling next() on it, it gets the first input line before starting the loop; thus s starts out with a valid value instead of None, and we don't have to test it each time to see whether to print it or not. The original if lastLine: test, on an input line that was stripped, would not only protect against the initial None value of lastLine but would also filter out any blank lines from the input.

  • If you have to use this with Python 3.0 or Python 2.6, you can't have a with statement that does two open() calls; but you can just turn it into two nested with statements, each of which does a single open().

share|improve this answer
THANK YOU SO MUCH!!! You have saved me tons of time. Let me know if there is anything I can do for you (charity you'd like something donated to? etc)! –  user1514979 Jul 10 '12 at 19:20
You're welcome! If you really want to you could give another small donation to the EFF, but please don't feel you must. –  steveha Jul 10 '12 at 19:20
add comment

import re,sys

lastLine = None
incomplete = re.compile("\\\\+$")
indented = re.compile("^\\t")

for line in open(sys.argv[1]):
    line = line.rstrip()
    line = incomplete.sub("", line)
    if indented.match(line):
        lastLine += indented.sub("",line)
        if lastLine:
            print lastLine
            lastLine = None
        lastLine = line

print lastLine

Basically, i'm ignoring the \ at the end since the tab on the next line tells you that it's a continuation anyways.

share|improve this answer
Thanks a bunch! As promised, $10 given to the EFF (not quite sure how to directly post an image in the comments, so here's a link): link Now, this code seems like it would work, but how would I get it to read in a text file and save a corrected text file. In other words, I would imagine using it with the command: –  user1514979 Jul 10 '12 at 17:08
<continued...> python cleanData.py uglyData.tsv which would then save the cleaned data in a text file named uglyData-CLEAN.tsv or something along those lines? –  user1514979 Jul 10 '12 at 17:14
+1 for a good answer. I made a few tweaks to it and posted my own version, but full credit to the source. –  steveha Jul 10 '12 at 19:18
to run it, save the file as, say cleanData.py then run: python cleanData.py myfile.tsv > clean.tsv'. I'm assuming you're on a unix type platform to be able to do the stdout redirect to a file. if on windows, tweak the python script to write to a file instead of printing to stdout. –  user665637 Jul 12 '12 at 14:51
add comment

Replace the "\n" sequence by nothing :

In [20]: a="blabla\tblabla\tblabla\\\n\tblabla\tblabla"

In [21]: print(a)
blabla  blabla  blabla\
    blabla  blabla

In [22]: a=a.replace('\\\n', '')

In [23]: print(a)
blabla  blabla  blabla  blabla  blabla


share|improve this answer
As promised, $10 given to the EFF: –  user1514979 Jul 10 '12 at 17:06
@user1514979: If the answer was helpful, you should upvote or accept it. Donation is nice but cannot replace customs around here. –  Erwin Brandstetter Jul 10 '12 at 18:13
This doesn't answer the question. He has input lines in a specific format that need to be merged, and the input files are huge so the need to be read line-by-line. –  steveha Jul 10 '12 at 18:22
add comment
