help me excel in spite of excel

March 18, 2015 4:06 PM

ran a test that spits out a text file, need to excel it. having issues. more inside.

i have a text file that spits out from a test i'm running. test name is on one line, pass/fail status is on the next line, like so:

testname
status

when i open the text file in excel, this makes two rows in one column. i need to take the 2nd row (pass/fail) and move it to a column next to the test name so they are next to each other. except there's roughly 250 tests, and the stuff that's on google is not working for me, and i do not want to do them all one by one. particulars: mac office 2011, i did not write the test and cannot contact the person that did to make them put it into a csv or otherwise delimited file. i can't believe i'm wasting a question on this, but tell me like i'm five how to do this, as i am not an excel person at all. i also have textwrangler if this helps at all.

thanks!

posted by koroshiya to Computers & Internet (13 answers total)

Assuming that the second row has a limited number of values (only pass/fail) I would do this:

Assume the text is imported into column A paste this into column B

In the column next to the text enter:
=IF(AND(A1<>"Pass", A1<>"Fail"), A2,"")

(change the Pass/Fail to your results as needed)

This will check to see if the value in the cell to the left is a test result. If not, it picks up the result from the next line down. If it is a test result, it returns blank.

After this has hopefully worked, copy/paste all the data as values and filter out the superfluous lines.
posted by NoiselessPenguin at 4:20 PM on March 18, 2015

Assuming your data is in column A, put this text in columns B & C.

B1: "=LEFT(A1,FIND("
",A1,1)-1)"

C1: "=RIGHT(A1,LEN(A1)-FIND("
",A1,1))"

What the formula is searching for is a carriage return (alt+enter). I'm using Excel 2007 and this works for me.
posted by JimBJ9 at 4:22 PM on March 18, 2015

Is your status predictable, like "Pass" or "Fail" or is it diagnostic-type stuff?
posted by Lyn Never at 4:23 PM on March 18, 2015

Response by poster: it's just PASSED/FAILED, lyn.
posted by koroshiya at 4:32 PM on March 18, 2015

Alternately, in TextWrangler you can open the original text file, then do a search for "\rPASSED" and replace it with "\tPASSED". Make sure the “Grep” checkbox is checked. That will replace the carriage return (\r) with a tab (\t). Then do a second search & replace with FAILED.
posted by bcwinters at 4:42 PM on March 18, 2015 [7 favorites]

If the second line of results is always either PASSED or FAILED, you can capture that with =RIGHT(A1,6). Then, to capture the file name, =LEFT(A1,LEN(A1)-6). Copy down.
posted by JimBJ9 at 4:48 PM on March 18, 2015

In a text editor, I would replace

[carriage return character]PASSED with

***PASSED

and then the same for FAILED, and then I would replace *** with comma-space (, ).

Then I would open it in Excel as a comma delimited file.

(I have to clean up all kinds of awful Quickbooks dumps and other horrible data files a lot. The *** is an old friend of mine.)
posted by Lyn Never at 4:56 PM on March 18, 2015 [3 favorites]

If you import it into Excel all in column A, you can paste =INDEX(A:A,ROW()*2-1) into column B, and =INDEX(A:A,ROW()*2) into column C, and those will copy across the correct rows from column A into columns B & C, respectively. If you add headers, etc, you may need to subtract values from the row offset, but otherwise, that should work quickly and easily.
posted by ambrosen at 5:32 PM on March 18, 2015

If you have Excel 2007 or later, you can just select your original column, hit "filter" on the "data" tab and you'll get a dropdown with checkboxes to select values found within that column.

Select none, then scroll through and select PASSED and FAILED only.

Copy and paste the resulting filtered column onto a new sheet.

Go back to the original column, select all in the filter dropdown then deselect PASSED and FAILED.

Copy and paste this filtered column next to the other on the new sheet.

The original data order will be maintained in each filtered then pasted column.
posted by protorp at 12:32 PM on March 19, 2015

Assuming the spreadsheet is pretty orderly, I would create the array that I want to the right of the data that is there (except for unwanted blank lines). For example, if a10 is Math Exam, and a11 is Pass, you could set f10 = +a10, and g10 = +a11. The formulas can be copired to new rows. Then select the new block to the right and copy/paste values onto a new tab. You can get rid of unwanted blank rows by sorting.
posted by SemiSalt at 2:33 PM on March 19, 2015

In the terminal, the command
paste -d "," - - < thefile.txt > output.csv

will turn thefile.txt from

test 1
passed
test 2
passed
test 3
failed
test 4
passed

into

test 1,passed
test 2,passed
test 3,failed
test 4,passed

as the file output.csv

Which I would test import into excel if I was at work, but I don't have it on my mac at home.

(credit: an old stack overflow question)
posted by ctmf at 4:32 PM on March 19, 2015

If the file has blank lines, that will screw paste up. So to remove them first,

sed '/^$/d' thefile.txt | paste -d "," - - > output.csv
posted by ctmf at 4:55 PM on March 19, 2015

Response by poster: thanks for the answers, everyone. i eventually just googled around and ended up writing a python processor to do what i wanted it to do.
posted by koroshiya at 4:18 PM on April 3, 2015