How to handle editing a large file for a non-technical user

Question

I have a client who is given a tab delimited .txt file containing hundreds of thousands of rows.

I have a user story as follows:

As a user I want to take the text file and add a new value at the end of each line which contains the concatenated value of two of the columns.

for example if the file read

text_one    text_two

I need to output the following (preferably to a .txt file)

text_one    text_two    text_onetext_two

My first approach was to ask the vendor supplying the file to do the concatenation before providing the file, the easiest way to solve a problem is to eliminate it right? however they are very uncooperative and have point blank refused.

I've looked at building a simple javascript application that does this client side so a non-technical user could select the file using a file selector. This approach has a few problems

• The file could be over a GB in size and so can't be loaded straight into memory, I've tried and the browser crashes
• There is no means to write a file in javascript so I'd need to output the content to the screen and have the user save it (somehow)

I was thinking if I could get around the filesize limitations I could just output the edited content to the page and have the user save the page as a .txt file, however I think there is a better way than using javascript that will still accommodate the users lack of technical know-how.

Please consider this question to be stack agnostic, but bear in mind that a nice little shell script or python script would be deemed unsuitable for a non technical user unless there is a way of "packaging" it nicely for a non-technical user.

Updates

The file is too large to open in excel. The process needs to be run weekly, but it doesn't require scheduling or automation...(yet)

Answer 1

The code bit is trivial. The best way to do the process is to put something between the vendor and the user.

My preference with these things is to get the vendor to transfer the file via FTP (which I hope they are already doing, given it's size). Write your code to grab the file, process it, and put it where the user expects to find it, then set it to run as appropriate (every 5 minutes, every day or whatever).

This is a very common problem.

Answer 2

If I understand correctly, the problem is that you or the user in question is mostly proficient in JavaScript and would like to employ it. If you just drag an drop a file into the Browser window, the DOM and subsequent rendering step will incur great overhead, in your case even leading to premature application termination due to an out of memory exception.

You can use JavaScript standalone. So you could use either use

• node.js, Rhino or
• the Browser's FileReader API and a simple REST Endpoint

You should be able to effortlessly open GB sized files in a FileReader object, with the caveat that the following approach is not the best performing. Then in a loop you would read blocks of say 5MB into memory, process these and push these to a webserver (ideally at localhost/127.0.0.1) You could use a simple php script for that (name it index.php). Something like:

<?php file_put_contents("outfile".date().".tsv", implode('', $_POST); , FILE_APPEND); ?>

Notes:

• You should probably do some filtering on the $_POST variable first, and put a PHP_Session ID into the filename.

• Through another short php/some-language script you could offer the user to compress said output-file and let the user download it. The script checks if the archive already exists, and if not inform the user to try again later.

• You should do the POSTing of the processed content through AJAX calls. If you do not want to use a comprehensive library such as jQuery for that purpose consider using is-lib's setFile function.

Reference:

For an hands-on example of the File-Reader see this project, and look up the MDN Reference for more information.

Good luck.