How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Josia Hernandez Your Own Question
Josia Hernandez
Josia Hernandez,
Category: Programming
Satisfied Customers: 8
Experience:  OWNER at JTANOW
95281006
Type Your Programming Question Here...
Josia Hernandez is online now
A new question is answered every 9 seconds

I'm having a hard time loading excel worksheet into matlab

Customer Question

I'm having a hard time loading excel worksheet into matlab because matlab doesn't like all empty row in the excel source file. Any ideas how to get rid of empty rows on multiple tabs?
Submitted: 10 months ago.
Category: Programming
Customer: replied 10 months ago.
I have many files like this one (see attached). I'd like a macro that will delete empty rows that aren't complete across all fields. I don't care of the data is ordered/sorted, so long as the fields are not mixed up. As a matter of fact, I recorded a macro using a custom sort, it seems to work and generates a very short VBA script.
I'm not proficient enough in VBA to write a macro that will loop through all worksheets (and maybe even files) that cleans up my data. Hope you can help. If you have questions, don't hesitate to contact me.
Customer: replied 10 months ago.
Posted by JustAnswer at customer's request) Hello. I would like to request the following Expert Service(s) from you: Secure Remote Assistance. Let me know if you need more information, or send me the service offer(s) so we can proceed.
Expert:  Josia Hernandez replied 10 months ago.

Hello there...

Expert:  Josia Hernandez replied 10 months ago.

Im look at the file but do not see any empty rows. What are you referring to?

Customer: replied 10 months ago.
hmm... that's interesting?! Try this file.
Expert:  Josia Hernandez replied 10 months ago.

are you referring to whole empty rows? or empty cells? what number number your referring to?

Customer: replied 10 months ago.
this definitely has some empty rows.
Customer: replied 10 months ago.
Are familiar with matlab? If yes, I can tell you exactly the kind of problem I'm having.
Expert:  Josia Hernandez replied 10 months ago.

I am. Whats going on?

Expert:  Josia Hernandez replied 10 months ago.

On the 2005 file, i can see the empty rows, there is no need for a macro to delete the rows, since it can be done once.

Customer: replied 10 months ago.
I'm trying to load the table to matlab. But I get this error.
As you can see here is my matlab code:
%% ------------------
% load using xls
for i = 68:height(path)
file_name = path.file_name(i);
sheet_name = path.sheet_name(i);
[num,txt,~] = xlsread(char(file_name),char(sheet_name));
disp(i/height(path));
end
clear i-----
something is happening at loop 68 which is file:
'/Users/dastanaitzhanov1/Dropbox/MATLAB/StockData/WallStreetCourier/amex_2010.xls' and
sheet:
'AMEX SI 08.2010'
Expert:  Josia Hernandez replied 10 months ago.

If you click details can you send me the data?

Customer: replied 10 months ago.
See below:
------------
MATLAB crash file:/Users/dastanaitzhanov1/matlab_crash_dump.13668-1:------------------------------------------------------------------------
Segmentation violation detected at Wed Jul 20 20:09:24 2016
------------------------------------------------------------------------Configuration:
Crash Decoding : Disabled
Crash Mode : continue (default)
Current Graphics Driver: Unknown hardware
Current Visual : Quartz
Default Encoding : ISO-8859-1
Host Name : Dastans-MacBook-Pro.local
MATLAB Architecture : maci64
MATLAB Root : /Applications/MATLAB_R2015a.app
MATLAB Version : 8.5.0.197613 (R2015a)
OpenGL : hardware
Operating System : Darwin 15.5.0 Darwin Kernel Version 15.5.0: Tue Apr 19 18:36:36 PDT 2016; root:xnu-3248.50.21~8/RELEASE_X86_64 x86_64
Processor ID : x86 Family 6 Model 69 Stepping 1, GenuineIntel
Virtual Machine : Java 1.7.0_60-b19 with Oracle Corporation Java HotSpot(TM) 64-Bit Server VM mixed mode
Window System : QuartzFault Count: 1Abnormal termination:
Segmentation violationRegister State (from fault):
RAX = 00000000000002cf RBX = 0000000118432c50
RCX = 00000001183e1fa0 RDX = 0000000000000001
RSP =(###) ###-####dd23b RBP =(###) ###-####9af60
RSI = 0000000118432c50 RDI = 00000000000002cfR8 = 00000000000002cf R9 = 0000000000000000
R10 = 00000000000002cf R11 = 0000000118432c50
R12 =(###) ###-####9cb40 R13 = 000000012918db8e
R14 =(###) ###-####9b420 R15 = 002bfc0015c6fccfRIP =(###) ###-####8e020 RFL = 0000000100000002CS =(###) ###-####9b9e8 FS = 0000000100000000 GS = 00007f91b58387e0Stack Trace (from fault):
[ 0] 0x0000000110974964 /Applications/MATLAB_R2015a.app/bin/maci64/libmwfl.dylib+00031076 _ZN2fl4diag15stacktrace_base7captureERKNS0_14thread_contextEm+00000052
[ 1] 0x000000011097802b /Applications/MATLAB_R2015a.app/bin/maci64/libmwfl.dylib+00045099 _ZN2fl4test17terminate_handledEv+00000763
[ 2] 0x0000000110977ac7 /Applications/MATLAB_R2015a.app/bin/maci64/libmwfl.dylib+00043719 _ZN2fl4diag13terminate_logEPKcPK17__darwin_ucontext+00000119
[ 3] 0x000000011049ff9b /Applications/MATLAB_R2015a.app/bin/maci64/libmwmcr.dylib+00380827 _Z32mnRunPathDependentInitializationv+00002875
[ 4] 0x00000001104a0217 /Applications/MATLAB_R2015a.app/bin/maci64/libmwmcr.dylib+00381463 _Z32mnRunPathDependentInitializationv+00003511
[ 5] 0x000000011049e3a6 /Applications/MATLAB_R2015a.app/bin/maci64/libmwmcr.dylib+00373670 mnFatalSignalHandler+00000262
[ 6] 0x00007fff97f7652a /usr/lib/system/libsystem_platform.dylib+00009514 _sigtramp+00000026
[ 7] 0x00007f91b5887b90 <unknown-module>+00000000
[ 8] 0x000000012918f0c8 /Applications/MATLAB_R2015a.app/toolbox/matlab/iofun/private/biffparse.mexmaci64+00020680 mexFunction+00000584
[ 9] 0x00000001155f31db /Applications/MATLAB_R2015a.app/bin/maci64/libmex.dylib+00070107 mexRunMexFile+00000091
[ 10] 0x00000001155ef9f1 /Applications/MATLAB_R2015a.app/bin/maci64/libmex.dylib+00055793 _ZN7Mfh_mex30runMexFileWithSignalProtectionEiPP11mxArray_tagiS2_+00000113
[ 11] 0x00000001155efbcd /Applications/MATLAB_R2015a.app/bin/maci64/libmex.dylib+00056269 _ZN7Mfh_mex13dispatch_fileEiPP11mxArray_tagiS2_+00000285
[ 12] 0x000000011567e4bd /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_dispatcher.dylib+00386237 _ZN8Mfh_file16dispatch_fh_implEMS_FviPP11mxArray_tagiS2_EiS2_iS2_+00000621
[ 13] 0x000000011567e5f3 /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_dispatcher.dylib+00386547 _ZN8Mfh_file11dispatch_fhEiPP11mxArray_tagiS2_+00000035
[ 14] 0x0000000115c71628 /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+03614248 _ZN20ResolverFunctionDesc12CallFunctionEiPP11mxArray_tagiS2_+00000184
[ 15] 0x0000000115c71f6a /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+03616618 _ZN8Resolver13CallMFunctionEiiP10_m_operandP17m_operand_storageiS1_S3_Pi+00001626
[ 16] 0x0000000115c72b07 /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+03619591 _Z22inResolveMFunctionCallP16_m_function_desciiP10_m_operandP17m_operand_storageiS2_S4_PiP13inMarshalTypeiPK19mpsTypeSequenceNlhsPFP11mxArray_tagiE+00000231
[ 17] 0x0000000115ae12bd /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+01974973 _ZN9accelImpl13MFunctionCallEPP8_accelOp+00000365
[ 18] 0x0000000115ae18e4 /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+01976548 _ZN9accelImpl4ExecEv+00000180
[ 19] 0x0000000115ae164e /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+01975886 _ZNK9accelCode4CallEP13inMarshalTypePi+00000094
[ 20] 0x0000000115c274f6 /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+03310838 _ZN5inJit17ExecuteHotSegmentEP15_inJitAccelInfoP7opcodesPiPl+00003894
[ 21] 0x0000000115a20ff8 /Applications/MATLAB_R2015a.app/bin/maci64/libmwm_interpreter.dylib+01187832 inPcodeWalker+00000968
[ 22
Expert:  Josia Hernandez replied 10 months ago.

The information doesnt give me much, but normally issues like this are best taken to http://www.mathworks.com/support/contact_us/

Customer: replied 10 months ago.
I've looked everywhere. So far, no luck. How about this? Can you help convert each worksheet in each xls file into a separate xls file? The naming convention can be fileName_sheetName.xls?
Customer: replied 10 months ago.
Basically, I want a sheet2xls macro.
Expert:  Josia Hernandez replied 10 months ago.

Sub Splitbook()

'Updateby20140612

Dim xPath As String

xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each xWs In ThisWorkbook.Sheets

xWs.Copy

Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"

Application.ActiveWorkbook.Close False

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

Expert:  Josia Hernandez replied 10 months ago.

That code will split the workbook into multiple files and save them within the same folder.

Customer: replied 10 months ago.
Almost there! Can you change the path to folder name = source xls file and individual sheets = sheetName.xls? Also, the folder should be in the same folder as the source xls file?
Expert:  Josia Hernandez replied 10 months ago.

You should be able to.

Customer: replied 10 months ago.
My VBA skills are terrible, but let me try.
Expert:  Josia Hernandez replied 10 months ago.

let me know

Customer: replied 10 months ago.
I feel like like "MkDir" should be somewhere in
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"
Customer: replied 10 months ago.
how about something like this:
Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs "\" & xWs.Name & ".xls"
Customer: replied 10 months ago.
that gives me a syntax error. Please guide.
Customer: replied 10 months ago.
Also trying this:
newPath = MkDir(xPath & "\" ThisWorkbook.Name)
Application.ActiveWorkbook.SaveAs Filename:=newPath & "\" & xWs.Name & ".xls"
Expert:  Josia Hernandez replied 10 months ago.

That didnt work either?

Customer: replied 10 months ago.
no yet. But I think I'm close
Expert:  Josia Hernandez replied 10 months ago.

is newPath = MkDir(xPath & "\" ThisWorkbook.Name)
Application.ActiveWorkbook.SaveAs Filename:=newPath & "\" & xWs.Name & ".xls" giving a syntax error?

Customer: replied 10 months ago.
Frankly, $39 is a little steep for something this simple
Expert:  Josia Hernandez replied 10 months ago.

Im just the tech, I dont have any control over pricing.

Customer: replied 10 months ago.
I understand. Can you help me change the script to create a folder with name of the original source file in the source files's folder and dump the new individual xls files into this new folder?
Expert:  Josia Hernandez replied 10 months ago.

i just got confused. Let me reread that

Customer: replied 10 months ago.
new folder path in the same folder as the source file,
source file name = new folder name,
new xls files in the new folder.
Expert:  Josia Hernandez replied 10 months ago.

Everything im trying is giving me an error. Let me keep trying.

Customer: replied 10 months ago.
Wow Josiah! If you're getting errors, no wonder I can't figure it out.
Expert:  Josia Hernandez replied 10 months ago.

The initial one worked for me. Did it work for you.

Customer: replied 10 months ago.
the one you sent me?
Customer: replied 10 months ago.
it does work. It just doesn't put the files in the right folder
Customer: replied 10 months ago.
Also, in Matlab that file gives me the following error:Error using xlsread (line 247)
File contains unexpected record length. Try saving as Excel 98.Any ideas why?
Expert:  Josia Hernandez replied 10 months ago.

is that on one of the big files or the individuals?

Customer: replied 10 months ago.
it's on the individual file: AMEX SI 8.2005.xls
when this file was part of the source file it terminate the whole matlab session, now it just gives me this error.
Expert:  Josia Hernandez replied 10 months ago.

let me look over it

Expert:  Josia Hernandez replied 10 months ago.

On MAtlab whats on the line?

Customer: replied 10 months ago.
I don't understan
Expert:  Josia Hernandez replied 10 months ago.

When the line 247 error comes up send me a screenshot

Expert:  Josia Hernandez replied 10 months ago.

and the more info

Customer: replied 10 months ago.
the error is not terminal, like before. See atached screenshot
Expert:  Josia Hernandez replied 10 months ago.

can you try saving the file as excel 98 and then retry, i want to see if it makes the difference.

Customer: replied 10 months ago.
I'd have to do that in the macro you provided. where and how do I need to change the code?
Customer: replied 10 months ago.
I'm pretty beat here. Let's resume tomorrow?
Customer: replied 10 months ago.
brain is done for today
Expert:  Josia Hernandez replied 10 months ago.

Ok. perfect.

Expert:  Josia Hernandez replied 10 months ago.

Good night.

Customer: replied 10 months ago.
Good night!
Expert:  Josia Hernandez replied 10 months ago.

Any update?

Customer: replied 10 months ago.
No, not yet. I've reached out to a friend who is a software developer to help me with the code.
Expert:  Josia Hernandez replied 10 months ago.

Let me know, i have asked a couple of friends too and can't get something to do exactly what we need. Let me know. I will continue looking.

Customer: replied 10 months ago.
are you online?
Customer: replied 10 months ago.
My macro is working for the most part, a few file not converting properly.

Related Programming Questions