Data360 Analyze

 View Only

  • 1.  Excel Node - Import Help!!

    Posted 01-28-2024 21:47
    Edited by andrew darnell 01-28-2024 22:11

    HI Guys

    I am hoping that someone can help me with importing data using the excel node.

    The excel books have multiple pages and I just wish to import a specific worksheet and specific column names.

    Since these workbooks were put together by humans the worksheets can have upper or lower case within the worksheet names (e.g 'Data or DATA'). So when i try to import this data if I code for 'DATA" then the workbooks saved with 'Data' fail the whole job.

    I only need 4 columns from the worksheet however, they are spaced apart and I cannot use the start and end function as it would import unnecessary data.

    Columns B

    Columns CW (Can be labeled as 'Amt_Paid or 'Amount_Paid'

    Columns DD

    Columns DW



    ------------------------------
    andrew darnell
    Knowledge Community Shared Account
    ------------------------------



  • 2.  RE: Excel Node - Import Help!!

    Employee
    Posted 01-29-2024 11:08

    Hi Andrew,

    I won't try to answer the question about how to map field names to the fields you are looking for, or sheet names to the sheets you are looking for.  Maybe the "Fuzzy Join" could be helpful.

    I just want to point out that the Excel node can be configured to write the sheets of an Excel file to BRD files that can be read back by a "BRD File" input node.  So you could read the Excel file into multiple BRD files.  And then if you can determine dynamically which ones are the ones to be processed, your are good to go.

    The Excel node also can supply generic column names so that you can look at the data which contains the column name as data to determine dynamically which ones are the ones you want. 

    This matching process does not sound easy or reliable, Good luck.



    ------------------------------
    Ernest Jones
    Precisely Software Inc.
    PEARL RIVER NY
    ------------------------------

    Original Message


  • 3.  RE: Excel Node - Import Help!!

    Employee
    Posted 01-30-2024 09:09

    I had another thought on what to do with mapping the various column names, because for a production environment I would not want to use a Fuzzy match to column and sheet names because I would not be able to trust it.

    Instead, what I've seen work well in another context is to create a mapping table.  Map the various sheet names and column names you find to normalized names for processing.



    ------------------------------
    Ernest Jones
    Precisely Software Inc.
    PEARL RIVER NY
    ------------------------------

    Original Message


  • 4.  RE: Excel Node - Import Help!!

    Posted 01-30-2024 18:39

    HI Ernest

    Thanks for your time and effort looking into my issue.

    I was hoping that I could utilize the Excel node and just be advised on what I need to key into the WorkbookSpec, the options above are a bit too advance for my basic understanding of d360.

    I've gone down the very manual route of just exporting as csv that one worksheet out of all 36 excel books, wish I didn't need to as I have tried using the examples in the help section for that node but it just doesn't get me where I need to be.



    ------------------------------
    andrew darnell
    Knowledge Community Shared Account
    ------------------------------

    Original Message