How do you load from any folder?

[davyqliks]

HI,

We are trying to load uk.xlsx from multiple folders.

The idea is that we use filebase name to give us a location code.

So, we have the following folders

NewSales/UK/2014/uk.xlsx

NewSales/UK/2015/uk.xlsx

NewSales/UK/2016/uk.xlsx

NewSales/UK/2017/uk.xlsx

I was hoping, rather than repeat the load script that i could have something like

NewSales/UK/*/uk.xlsx

but this is not recognised. Is there code to allow me to load from multiple folder? What would i replace the /*/ with please.

Thank you in advance

[RonaldDoes]

Hi David,

How about one of these solutions: Load excel files from multiple folders

With kind regards,

Ronald

[marcus_malinow]

Hi David,

you could use a loop through the results of a DirList(). Substitute in your starting folder, and away you go!

Marcus

For each dir in Dirlist('$(startingdir)\*')

    NewSales:

    LOAD *

    FROM [$(dir)\uk.xlsx]

    ;

Next dir

[davyqliks]

Thank you for the answer, i have tried this

[\\FILE**\Support *****\**Files\New*****Dash\UK\Workflow\$(dir)\uk.xlsx]

and on reload i get this

****************Workflow\\uk.xlsx' The system cannot find the file specified.

can you see an error here. I did try this method before logging, thank you in advance

[davyqliks]

Thank you but from this it looks like replacing the folder name with /$/ this did not work for.

THank you for the link though, i will try to make sense of it.

[marcus_malinow]

the issue here is that in your dir variable you have the full path, so change your path to just be

[$(dir)\uk.xlsx]

[davyqliks]

HI,

Thank you,

the trouble is the is multiple folders with UK.xlsx in, not just this one, i risk importing the wrong files.... The issue here with the workflow is the files are so big we split to year. This is the issue i am coming up against.

I hope you find this clear reasoning.

[marcus_malinow]

Hi David,

I understand, try this loop

For each dir in Dirlist('\\FILE**\Support *****\**Files\New*****Dash\UK\Workflow\*')

    NewSales:

    LOAD *

    FROM [$(dir)\uk.xlsx]

    ;

Next dir

[davyqliks]

please help to clarify my code:

I have the following. Are you suggesting i use the same path ' [$(dir)\uk.xlsx] ' for both? i think i am misunderstanding you.

Regards,

[InvoiceListing]:

LOAD [Inv NO.],

     //[SO NO.],

     //[PO NO.] as [PO PA SP],

     [Customer NO.] as [Customer Name],

     [Item Name],

     Status,

    CS as [Invoicee CS],

     [Outstanding Amount] as [Invoice Total], 

     [Issue Date] as [Invoice Date],

          Year([Issue Date]) as [INV Year],

          Month([Issue Date]) as [Inv Month],

          Day([Issue Date]) as [Inv Day],

           Date(monthstart([Issue Date]), 'MMM-YYYY') as MonthYear,

     //J

     Filebasename() as Location

FROM

[\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Invoice\UK.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[WorkflowListing]:

LOAD [PO NO./PA NO.] as [PO PA SP],

     //[Staff BarCode],

     [Staff Name] as [Workflow User],

    // [Staff Title],

     [Steps BarCode],

     //[Step Name],

     [Step Desc],

    // Action,

     Date as [workflow Date],

     Year([Date]) as [WF Year],

          Month([Date]) as [WF Month],

          Day([Date]) as [WF Day],

     Time

     //K

FROM

[\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Workflow\$(dir)\UK.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

[marcus_malinow]

Hi David,

Ok, so your WorkFlowListing load can be changed to enclose it in a loop:

For each dir in Dirlist('\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Workflow\*')

[WorkflowListing]:

LOAD [PO NO./PA NO.] as [PO PA SP],

     //[Staff BarCode],

     [Staff Name] as [Workflow User],

    // [Staff Title],

     [Steps BarCode],

     //[Step Name],

     [Step Desc],

    // Action,

     Date as [workflow Date],

     Year([Date]) as [WF Year],

          Month([Date]) as [WF Month],

          Day([Date]) as [WF Day],

     Time

     //K

FROM

[$(dir)\UK.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

next dir

I'm not so sure about your InvoiceListing as it looks to be retrieving from a different folder structure. Perhaps, if you have subfolder there as well you could enclose this load in another loop

For each dir in Dirlist('\\FILE**\Support *****\QVFiles\NewS****Dash\UK\Invoice\*')

Marcus