AnsweredAssumed Answered

Format from Excel file importing incorrect

Question asked by BIGCHAW on May 6, 2015
Latest reply on May 6, 2015 by BIGCHAW

Hi Everyone,

     Having a issue when attempting to read rows from a excel file into LISA.  As I said, I am importing a rather large excel file which was made manually from a copybook.  There are two sheets in the excel file, one where the business enters their data, and the second sheet is where all the formulas live that format the data so it looks proper, to adhere to the coypbook format.  When I fill in the excel sheet and do a manual copy and paste into a text editor such as UltraEdit, everything looks good.  All formats line up, file is correct size and everything.

     When I have LISA import the row, and then use the "Test and Keep" button, after it reads the rows, I check the sample in the dialog box that opens, and find that the formats start to go wrong on this one field.  When I look at that one field in Excel, everything is good.  To be honest, that field is just a copy and paste of the previous section, since the section this field exists within, iterates 12 times.  Instances 1-4 are perfectly fine, and in instance 5 it goes wrong   I have NO CLUE why LISA is showing a issue here and I'd rather not have to manually create a whole new excel sheet, which honestly I dont think is the answer anyways.  There must be something LISA is doing incorrectly

 

Here is the data and formula that is in the spreadsheet:

Data = 8900

Formula =IF(ENTER_VALUES_HERE!IW2>0,"+","") & TEXT(ENTER_VALUES_HERE!IW2,"000000000000")

 

The outcome yields +000000008900 since my fields need the sign and have to have 12 numeric digits.  As I said, i have like 50 other fields that have this exact same format and those work perfectly, but once I hit this iteration it all goes wrong

 

Does LISA hold onto old formulas or have issues with very large excel files?  Here is a snapshot of the Test and Keep results, refer to "TRGT_SYS_PRISTINE_BAL_AMT5_2 where you can see the value is 15 and not the +00000000008900.  Although you can see the COMBINED balance amount above it is just fine   I've validated Excel 5 times and all the formulas look the same, unless Excel is holding onto something incorrect

 

lisa.jpg

Outcomes