Home

Excel 97-2003 worksheet format issue

%3CLINGO-SUB%20id%3D%22lingo-sub-759963%22%20slang%3D%22en-US%22%3EExcel%2097-2003%20worksheet%20format%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-759963%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20receiving%20excel%2097-2003%20output%20as%20follows%20from%20a%20third%20party%20application.%20The%20data%20looks%20like%20below.%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EDetail%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EPort%3C%2FTD%3E%3CTD%3EBench%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAvg%20%25%20Wgt%3C%2FTD%3E%3CTD%3ETot%20Rtn%3C%2FTD%3E%3CTD%3ECTR%3C%2FTD%3E%3CTD%3EAvg%20%25%20Wgt%3C%2FTD%3E%3CTD%3ETot%20Rtn%3C%2FTD%3E%3CTD%3ECTR%3C%2FTD%3E%3CTD%3ETot%20Attr%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPVAST%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E100.000000%3C%2FTD%3E%3CTD%3E3.496272%3C%2FTD%3E%3CTD%3E3.496272%3C%2FTD%3E%3CTD%3E100.000000%3C%2FTD%3E%3CTD%3E3.647863%3C%2FTD%3E%3CTD%3E3.647863%3C%2FTD%3E%3CTD%3E-0.146255%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ECash%3C%2FTD%3E%3CTD%3E5.865017%3C%2FTD%3E%3CTD%3E0.000000%3C%2FTD%3E%3CTD%3E0.000000%3C%2FTD%3E%3CTD%3E0.000000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E-0.204005%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EAUD%3C%2FTD%3E%3CTD%3E5.800607%3C%2FTD%3E%3CTD%3E0.000000%3C%2FTD%3E%3CTD%3E0.000000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E0.000000%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhen%20parsed%20as%20comma%20separated%20sequence%20of%20string%20gives%20me%20the%20following%20output%3CBR%20%2F%3E%3CBR%20%2F%3E%22Detail%2C%2C%2C%2C%2C%2C%2C%2C%22%3CBR%20%2F%3E%22%2C%2C%2CPort%2C%2C%2CBench%2C%2C%22%3CBR%20%2F%3E%22%2C%2C%2CAvg%20%25%20Wgt%2CTot%20Rtn%2CCTR%2CAvg%20%25%20Wgt%2CTot%20Rtn%2CCTR%22%3CBR%20%2F%3E%22PVAST%2C%2C%2C100%2C3.496272%2C3.496272%2C100%2C3.647863%2C3.647863%22%3CBR%20%2F%3E%22%2CCash%2C%2C5.865017%2C0%2C0%2C0%2C%2C%22%3C%2FP%3E%3CP%3E%22%2C%2CAUD%2C5.800607%2C0%2C0%2C%2C%2C%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enow%20when%20I%20open%20the%20above%20file%20in%20excel%20and%20save%20it%20with%20a%20different%20file%20name%2C%20firstly%20the%20size%20of%20the%20file%20increases%20by%2020%20KB%20and%20now%20when%20parsed%20gives%20me%20following%20output%3CBR%20%2F%3E%22Detail%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%22%3CBR%20%2F%3E%22%2C%2C%2CPort%2C%2C%2CBench%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%22%3CBR%20%2F%3E%22%2C%2C%2CAvg%20%25%20Wgt%2CTot%20Rtn%2CCTR%2CAvg%20%25%20Wgt%2CTot%20Rtn%2CCTR%2CTot%20Attr%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%22%3CBR%20%2F%3E%22PVAST%2C%2C%2C100%2C3.496272%2C3.496272%2C100%2C3.647863%2C3.647863%2C-0.146255%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%22%3CBR%20%2F%3E%22%2CCash%2C%2C5.865017%2C0%2C0%2C0%2C%2C%2C-0.204005%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%2C%22%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3EI%20am%20expecting%20the%20second%20output.%20But%20not%20sure%20why%20this%20difference%20in%20behavior%20when%20the%20file%20content%20is%20same.%20I%20checked%20it%20with%20Excel%20Compare%20tool%20also.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-759963%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%2097-2003%20worksheet%20format%20issue%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
saneeshp
Occasional Visitor

I have been receiving excel 97-2003 output as follows from a third party application. The data looks like below.

Detail
   PortBench 
   Avg % WgtTot RtnCTRAvg % WgtTot RtnCTRTot Attr
PVAST 100.0000003.4962723.496272100.0000003.6478633.647863-0.146255
 Cash5.8650170.0000000.0000000.000000  -0.204005
  AUD5.8006070.0000000.000000   0.000000

 

when parsed as comma separated sequence of string gives me the following output

"Detail,,,,,,,,"
",,,Port,,,Bench,,"
",,,Avg % Wgt,Tot Rtn,CTR,Avg % Wgt,Tot Rtn,CTR"
"PVAST,,,100,3.496272,3.496272,100,3.647863,3.647863"
",Cash,,5.865017,0,0,0,,"

",,AUD,5.800607,0,0,,,"

 

now when I open the above file in excel and save it with a different file name, firstly the size of the file increases by 20 KB and now when parsed gives me following output
"Detail,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"
",,,Port,,,Bench,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"
",,,Avg % Wgt,Tot Rtn,CTR,Avg % Wgt,Tot Rtn,CTR,Tot Attr,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"
"PVAST,,,100,3.496272,3.496272,100,3.647863,3.647863,-0.146255,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"
",Cash,,5.865017,0,0,0,,,-0.204005,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,"

I am expecting the second output. But not sure why this difference in behavior when the file content is same. I checked it with Excel Compare tool also.

 

 

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies