extract gene id form excel and save into new files

Question

[Eva_Maria]

I have a excel file like this

GCF_000582905.1.fna    GCF_000878815.1.fna    GCF_000951795.1.fna
dnaE_11                dnaE_21                dnaE_3
dtdS_35                dtdS_23                dtdS_4
gyrB_4                 gyrB_15                gyrB_4
pntA_29                pntA_23                pntA_29
pyrC_52                pyrC_21                pyrC_4
recA_16                recA_1                 recA_19
tnaA_22                tnaA_16                tnaA_22

I want output as like as a filename GCF_000974905.1.fna which contains

dnaE_11
dtdS_35
gyrB_4
pntA_29
pyrC_52
recA_16
tnaA_22

like this

Please help me

[Naren]

It is a very bad way to use Excel for storing gene names. As it is discussed here and here that how gene names can automatically get converted to dates.

[Ram]

^This. Please DO NOT USE Excel to work with gene names. God forbid you stumble across names like MAR1.

[Jean-Karim Heriche]

I'd say do not use Excel for scientific purposes. You just can't guarantee data integrity with it (gene names are just one example). Unfortunately so many labs (and companies) are addicted to it.

[Michael]

use save as in Excel??

[Eva_Maria]

save as text file

[Jean-Karim Heriche]

This is fine if you only have one or two files but becomes painful when you have dozens of files, especially when they have multiple sheets.

Answer 1

[Jean-Karim Heriche]

The Gnumeric package comes with a neat command line utility called ssconvert that can convert Excel files to plain text formats. Then use your favorite scripting language to reorganize the data. There's also the gnumeric R package (which requires ssconvert) to get the file content directly in R.

Answer 2

[Tej Sowpati]

Adding to the already good answer by Jean-Karim, you can do the following if you have pandas (Python):

from __future__ import print_function  # Delete this line if using python 3
import pandas as pd

data = pd.read_excel('171477.xlsx')    # Change file name to your Excel file

for column in data:
    with open(column, 'w') as file_out:
        print(data[column].to_csv(file_out, index=False))

Save the above script in a file (for example, myscript.py), and then run it like so:

$ python myscript.py

If you don't have pandas (You will get ImportError), you can install it using pip:

$ pip install pandas

Cheers,
TEJ