8 Replies Latest reply: Nov 18, 2014 11:34 AM by Oscar Ortiz RSS

    Applymap in Access database

    Paul Wonford

      Can we do an applymap to an Access database?

       

      I've got the following code in Excel but i cant seem to convert it into access

       

      (i'm using

      ODBC

       

      CONNECT32 TO [MS Access Database;DBQ=C:\Users\Desktop\QV\New Microsoft Access Database.accdb];

      )

       

      Map:

       

       

      mapping load

      if(FieldID = 6,DataID,) as DataID,
      Data

      FROM
      [C:\Users\Desktop\QV\Address.xlsx]
      (
      ooxml, embedded labels, table is Sheet1);

      Customer_Name:

      mapping load

      if(FieldID = 5,DataID,) as DataID,
      Data

      FROM
      [C:\Users\Desktop\QV\Address.xlsx]
      (
      ooxml, embedded labels, table is Sheet1);

      Seller:

      mapping load

      if(FieldID = 7,DataID,) as DataID,
      Data

      FROM
      [C:\Users\Desktop\QV\Address.xlsx]
      (
      ooxml, embedded labels, table is Sheet1);

      LOAD

      DataID,
      ApplyMap ('Map', DataID ) as Customer_Number,
      ApplyMap('Customer_Name',DataID) as Customer_Name,
      ApplyMap('Seller',DataID) as Seller


      FROM
      [C:\Users\Desktop\QV\Address.xlsx]
      (
      ooxml, embedded labels, table is Sheet1);




      LOAD

      ApplyMap ('Map', DataID ) as Customer_Number,
      ApplyMap('Customer_Name',DataID) as Customer_Name,
      DataID

      FROM
      [C:\Users\Desktop\QV\Address.xlsx]
      (
      ooxml, embedded labels, table is Sheet1);


        • Re: Applymap in Access database
          Alan Farrell

          You need to use the Select button to select the table from the Access Database

            • Re: Applymap in Access database
              Paul Wonford


              When i try that though it complains i have a circular reference

               

               

              ODBC

               

              CONNECT32 TO [MS Access Database;DBQ=C:\Users\u22471\Desktop\QV\New Microsoft Access Database.accdb];

              Map:

              mapping select

              if(FieldID = 6,DataID,) as DataID,
              Data

              FROM
              [Sheet1];

                • Re: Applymap in Access database
                  Paul Wonford

                  This is what i have

                   

                   

                  ODBC

                   

                  CONNECT32 TO [MS Access Database;DBQ=C:\Users\Desktop\QV\New Microsoft Access Database.accdb];

                  Map:

                  mapping sql select

                  if(FieldID = 6,DataID,) as DataID,
                  Data

                  FROM
                  [Sheet1];

                  Customer_Name:

                  mapping sql select

                  if(FieldID = 5,DataID,) as DataID,
                  Data

                  FROM
                  [C:\Users\u22471\Desktop\QV\Address.xlsx]
                  (ooxml, embedded labels, table is Sheet1);

                  Seller:

                  mapping sql select

                  if(FieldID = 7,DataID,) as DataID,
                  Data

                  FROM
                  [C:\Users\u22471\Desktop\QV\Address.xlsx]
                  (ooxml, embedded labels, table is Sheet1);

                  sql select

                  DataID,
                  ApplyMap ('Map', DataID ) as Customer_Number,
                  ApplyMap('Customer_Name',DataID) as Customer_Name,
                  ApplyMap('Seller',DataID) as Seller


                  FROM
                  [C:\Users\u22471\Desktop\QV\Address.xlsx]
                  (ooxml, embedded labels, table is Sheet1);




                  sql select

                  ApplyMap ('Map', DataID ) as Customer_Number,
                  ApplyMap('Customer_Name',DataID) as Customer_Name,
                  DataID

                  FROM
                  [C:\Users\u22471\Desktop\QV\Address.xlsx]
                  (ooxml, embedded labels, table is Sheet1);


              • Re: Applymap in Access database
                Alan Farrell

                Hi Paul,

                 

                I am not sure what you are trying to do here.

                 

                You have a connect statement to an access database, but yet you are not selecting any tables from the the DB

                 

                If you want to create a Mapping Load from the Access Database you need to Connect and then select the table you require, ensure you choose the fields, must be only two fields for a Mapping Load.

                 

                Below is an example where I have selected the Product Table and created a Mapping Table

                 

                2014-11-18_1607.png

                 

                 

                You can then load the excel tables if you need them and add the ApplyMap part.

                 

                I hope that makes sense

                • Re: Applymap in Access database
                  Paul Wonford

                  So i cant applymap from an access database?

                    • Re: Applymap in Access database
                      Alan Farrell

                      Yes you can.

                       

                      You need to select a table from the Database and make sure you tick the box for Preceding load

                       

                      2014-11-18_1626.png

                      • Re: Applymap in Access database
                        Oscar Ortiz

                        Paul,

                         

                        You can applymap from an access database, you just have to use the proper syntax.

                         

                        Map:

                        mapping

                        Load

                        if(FieldID = 6,DataID,) as DataID,

                        Data

                        ;

                        sql select *
                        FROM
                        [Sheet1];

                        Customer_Name:

                        mapping

                        Load

                        if(FieldID = 5,DataID,) as DataID,

                        Data

                        ;

                        sql select *
                        FROM
                        [C:\Users\u22471\Desktop\QV\Address.xlsx]
                        (ooxml, embedded labels, table is Sheet1);

                        Seller:
                        mapping

                        Load

                        f(FieldID = 7,DataID,) as DataID,

                        Data

                        ;

                        sql select *
                        FROM
                        [C:\Users\u22471\Desktop\QV\Address.xlsx]
                        (ooxml, embedded labels, table is Sheet1);

                        FirstTable:

                        Load

                        DataID,

                        ApplyMap ('Map', DataID ) as Customer_Number,

                        ApplyMap('Customer_Name',DataID) as Customer_Name,

                        ApplyMap('Seller',DataID) as Seller

                        ;

                        sql select  *
                        FROM
                        [C:\Users\u22471\Desktop\QV\Address.xlsx]
                        (ooxml, embedded labels, table is Sheet1);

                        SecondTable:

                        Load

                        ApplyMap ('Map', DataID ) as Customer_Number,

                        ApplyMap('Customer_Name',DataID) as Customer_Name,

                        DataID

                        ;

                        sql select *
                        FROM
                        [C:\Users\u22471\Desktop\QV\Address.xlsx]
                        (ooxml, embedded labels, table is Sheet1);

                         

                        Good luck

                        Oscar