load data from 2 excel sheets

[Anonymous]

hi ,

i want to load data from 2 excel sheets names below.

1.2014 US REMS Audit Workbook DOL

2.2015 US REMS Audit Workbook DOL

Tab Names:

2014 Audit stats Tracking

2015 Audit stats Tracking

i want to combine data as a single Table with automation(Not Manual Concatenation)

OUTPUT Table contains 2014,2015 Audit stats Tracking Data

Thanks in advance

[Anonymous]

Hi Rudolf,

thanks for your valueable time

its working fine with your data set

but its throwing error and getting hang with my actual data set

here is the error screen shot

thanks

[Anonymous]

Hello,

I misunderstood (i am not able to load your larger Excel file).

YOu donot Need the inner Loop, I just changed it (but could be improved more)

it works for the 2014 Excel. please try with both files and let me know

LET vExcelSheet = ' US REMS Audit Workbook DOL.xlsx';
LET vTab = ' Audit stats Tracking';

LET vExcelYear='2014';

for i=1 to 2 // go through all excel sheets


let vExcel =  vExcelYear & vExcelSheet;
Let vTabYear = '2014';

//for n=1 to 2 // go through all tabs

let vExcelTab = '[' & vTabYear & vTab & ']';
DATALOAD:
LOAD *
FROM

(ooxml, embedded labels, table is $(vExcelTab));
Let vTabYear=vTabYear+1;
//next n;
Let vExcelYear=vExcelYear+1;
next i;

[Anonymous]

hi its working for 2014 data.but it should take 2015 as well

its stopping upto 2014 only

how to add 2015 audit stats tracking data to 2014

thanks

[Anonymous]

can you provide a smaller Excel sheet of 2015? I have a bad Connection right now

I could try with both of your Excel sheets

[Anonymous]

Hi Are.

I hope this helps, be careful with the order of columns.

[tamilarasu]

Hi,

  I could find a column name mismatch in both the sheets. I have changed the name to Date Certificate Issued, so that we can do concatenation.

You can change the path in front end input box.

sub ScanFolder(Root)

for each FileExtension in 'xlsx'

   for each FoundFile in filelist( Root & '\*.' & FileExtension)

Let vYear = Left( subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1),4);

Let vSheetName = vYear & ' Audit stats Tracking';

    Data:     

      LOAD [Audit Date],

     [DEA or NABP Number],

     [Pharmacy ID],

     [Pharmacy Name],

     Address,

     City,

     State,

     PharmacyTYpe,

     LeadAuditor,

     AuditType,

     AccountManagerName,

     MAAccountDirector,

     [Product Audited],

     [Overall Audit Result],

     [Observation Rating],

     [Observation Yes or No],

     [Repeat Observations],

     [Tier 1],

     [Tier 2],

     [Number of Counselors],

     [Date Audit Report Issued],

     [Date Response Received],

     [Date Certificate Issued],

     [Effectiveness Check],

     Comments,

     subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1) as FileName

FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');

next FoundFile

next FileExtension

end sub

Call ScanFolder('$(vFilePath)') ;

Note: If your column names are same, you can use Load * in above script. Also it can work for future years of data .

Let me know.

[Anonymous]

Hi Nagaraju,

iam getting below error

please find my path in application variable

please find the error attached

thanks for your valuable time

[tamilarasu]

Can you check the excel which you are trying to load (2014.xlsx). Above error shows that, there is no Audit Date column in your excel.

[Anonymous]

hi Nagraj,

thanks for your Time .its working fine

but

in same folder i have excels which is not related to this load.

code seaching in those excel sheets and throwing error Audit date Field not available.

it should Consider only "2014 US REMS Audit Workbook DOL","2015 US REMS Audit Workbook DOL","2016 US REMS Audit Workbook DOL",2017 US REMS Audit Workbook DOL ......so on

where i need to apply this condition in script

thanks for valuable time

Are Babu

[tamilarasu · Accepted solution]

Hi Are BaBu,

I just added an extra If statement to check the file name. Check the attachment.

sub ScanFolder(Root)

for each FileExtension in 'xlsx'
   for each FoundFile in filelist( Root & '\*.' & FileExtension)

Let vFileName = subfield('$(FoundFile)','\',SubStringCount('$(FoundFile)','\')+1);

if (Wildmatch(vFileName, '*US REMS Audit Workbook DOL*')) then

Let vYear = Left(vFileName,4);
Let vSheetName = vYear & ' Audit stats Tracking';

    Data:    
      LOAD [Audit Date],
     [DEA or NABP Number],
     [Pharmacy ID],
     [Pharmacy Name],
     Address,
     City,
     State,
     PharmacyTYpe,
     LeadAuditor,
     AuditType,
     AccountManagerName,
     MAAccountDirector,
     [Product Audited],
     [Overall Audit Result],
     [Observation Rating],
     [Observation Yes or No],
     [Repeat Observations],
     [Tier 1],
     [Tier 2],
     [Number of Counselors],
     [Date Audit Report Issued],
     [Date Response Received],
     [Date Certificate Issued],
     [Effectiveness Check],
     Comments,
     '$(vFileName)' as FileName
FROM [$(FoundFile)] (ooxml, embedded labels, table is '$(vSheetName)');

   End If   

   next FoundFile
next FileExtension
end sub

Call ScanFolder('$(vFilePath)') ;