Return to Level1Techs.com

Any Excel/other Table wizards here to help me out?

helpdesk
#1

Following Problem: I have a text file that has values in it all seperated by new lines. I want to convert that to a table. Example:

Name 1
Value 123.3
Name 2
Value 12.54
Name 3
Value 894302

I’d now like to get to a table with a name and Value Column. What ever way you can aome up with might help.
A Vim macro to replace new lines with delimiters (hard, because to properly import, it would need a delimiter after Name, but new line after Value), or some Excel Wizardry. I have Windows, Linux, SQL, and other tools at hand but am lacking the imagination about how to approach this problem.

1 Like

#2

It’s not clear from your example if your lines actually contain the words “Name” and “Value”. If so, you can search & replace “\nValue” with “,Value” and import the whole thing as CSV.

If not, here’s a quick & dirty python script:

import pandas as pd

raw = pd.read_csv('input.txt', header=None)

out = pd.DataFrame(
        data={
            'Name': raw.values[0::2,0],
            'Values': raw.values[1::2,0],
            })

out.to_csv('output.txt', index=False)

Any recent python should work. You have to install pandas if you haven’t yet: python -m pip install pandas

5 Likes

#3

Man, i didn’t even think about that option.
Replacing the New lines with semicolons ("," was used in the Data) allowed me to easily import that into Excel. Perfect!

3 Likes