Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have imported a text file to excel. The file has numeric and text columns. In the numeric column there are some scientific numbers which are only in text format. When I import the excel file to matlab I face with a problem about the scientific numbers which says these are not number! How can I deal with these numbers? Why that has been arised?

The file was at the beginning a result of one sequencing program. Then I added txt suffix to change it into a text format. And finally I opened it in Microsoft excel and saved as xlsx format. Here is how one row of my file looks like in excel sheet:

ENSMUSG00000078645  -   -   ENSMUSG00000078645  Fam63a  TSS68162    3:95085766-95088779 -   -   **6.41727e-317**    0   0.309616    OK

the bold number is one of those scientific numbers.

Here is some part of original text file:

tracking_id class_code  nearest_ref_id  gene_id gene_short_name tss_id  locus   length  coverage    FPKM    FPKM_conf_lo    FPKM_conf_hi    FPKM_status

ENSMUSG00000088390  -   -   ENSMUSG00000088390  U7  TSS3702 1:3668960-3669024   -   -   0   0   0   OK
share|improve this question
    
Maybe you could give some more information. For example an excerpt from your text file. Can you import the file directly in matlab or do you have to preprocess it with excel? Which function (function call) do you use to import the excel file (from *.xls, from *.csv or from which format) and what is the exact error message? –  Tim Sep 23 '13 at 10:25
    
I have edited my question. Please let me know if any info is missing! –  Rozita Akrami Sep 23 '13 at 10:48
    
What I actually meant were some lines from your "txt" file. But converting your data file into a text file is just renaming it. It seams like the file itself does not contain pure numbers. Maybe you can export it in another format (like csv) or you have to write a dedicated function for interpreting the file using matlab. –  Tim Sep 23 '13 at 10:56
    
I exported it into csv format from unix (tr "\t" ",") but still the same problem! –  Rozita Akrami Sep 23 '13 at 11:33
    
Have you tried loading your (original) file into a matlab cellarray using something like A=textscan(fid,'%s',' ','\n')? You could then use cellfun for further processing. Please add some lines from the original file and the error message matlab gives if it fails. –  Tim Sep 23 '13 at 11:39

2 Answers 2

up vote 0 down vote accepted

Here is a matlab script that imports your example data into a cellarray and then copies its contents to variables named after your headers.

fid  = fopen('tst.tst');
data = textscan(fid,'%s %s %s %s %s %s %s %s %s %d %d %d %s','HeaderLines',2);
fclose(fid)

mytracking_id         = data{1};
myclass_code          = data{2};
mynearest_ref_id      = data{3};
mygene_id             = data{4};
mygene_short_name     = data{5};
mytss_id              = data{6};
mylocus               = data{7};
mylength              = data{8};
mycoverage            = data{9};
myFPKM                = data{10};
myFPKM_conf_lo        = data{11};
myFPKM_conf_hi        = data{12};
myFPKM_status         = data{13};
clear data

Columns read with %d will become vectors of integers (use %g for floating point numbers). Columns read with %s are cell arrays of string, you probably need to further process these.

I chose to use a prefix my, so you do not override functions like length.

share|improve this answer
    
Thanks a lot. It works perfectly. I Just think instead of %g you meant %f! But it's exactly what I wanted. –  Rozita Akrami Sep 23 '13 at 12:43
    
I actually meant %g, but %f would probably work as well. According to the standard %f is fixed point notation (like 0.0001), %e is exponential notation (like 1.0e-4) and %g can mean either. I guess the difference is only important when printing and scan functions are more tolerant. –  Tim Sep 23 '13 at 13:07

The VALUE function in Excel will turn text-formatted scientific notation into the stored number value. For example, if you have the text "4.879E-16" in cell A1, you can use:

=VALUE(A1)

To get the value:

0.0000000000000004879
share|improve this answer
    
No, it doesn't work like this. The value remains null or unknown (#VALUE!). –  Rozita Akrami Sep 23 '13 at 10:50
    
Ah! Your example data reveals a clue... 6.41727e-317 is an extremely small number. Excel can only hold 255 digits of information in a cell. Your data has 316 zeroes in front of the significant digit, so I suspect it is literally too small for Excel to work with. –  Excel Tactics Sep 23 '13 at 10:58
    
I would like to convert these super small numbers into zero. Do you have any suggestion not doing manually? in Matlab I could do it by iscellstr function for each individual cell. I wanted to implement it to all cells by cellfun. But it is not working! I have several of these files so programming in matlab is better than in excel. –  Rozita Akrami Sep 23 '13 at 11:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.