Search code examples
excelexcel-formula

Excel to dynamically generate SQL script using formula

I am trying to dynamically generate SQL INSERT scripts via Excel formulas

My Excel sheet looks as below.

enter image description here

Below is my formula

="INSERT INTO MYTABLE (ROLE_ID,GROUP,SUBGROUP) values('"&D8&"','"&$E$8&"','"&$F$8&"')"

Now, I want the expected output to be generated as below;

enter image description here

So basically, for Column G, I want the generated script to use data from Columns D, E and F. However, the information of Column D would change as per the current row, Columns E and F would repeat till we get an empty row (or as we can use value '0' in Column D to identify that it should now use new values for GROUP and SUBGROUP)

="INSERT INTO SOME_TABLE (MYROLE) values('"&E7&"');"

INSERT INTO SOME_TABLE (MYROLE) values(null); INSERT INTO SOME_TABLE (MYROLE) values('My Role');

=IF(ISBLANK(E7), "INSERT INTO SOME_TABLE (MYROLE) values(null);", "INSERT INTO SOME_TABLE (MYROLE) values('"&E7&"');")

insert into MYSCHEMA.MY_DESTINATION_TABLE(COL_ID, COL2) select 'USER1', COL2 from MYSCHEMA.MY_SOURCE_TABLE where USER_ID = 'USER1';

INSERT INTO MYSCHEMA.MY_DESTINATION_TABLE (COL_ID, COL2) SELECT COL_ID, COL2 FROM MYSCHEMA.MY_SOURCE_TABLE WHERE COL_ID IN (SELECT COL_ID FROM MYSCHEMA.MY_SOURCE_TABLE);

INSERT INTO MYSCHEMA.MY_DESTINATION_TABLE (COL_ID, DESTINATION_COL2) SELECT COL_ID, SOURCE_COL2 FROM MYSCHEMA.MY_SOURCE_TABLE WHERE COL_ID IN (SELECT COL_ID FROM MYSCHEMA.MY_SOURCE_TABLE);

INSERT INTO MYSCHEMA.MY_DESTINATION_TABLE (DEST_COL_ID, DEST_COL2) SELECT SOURCE_COL_ID, CASE WHEN SOURCE_COL1 = 'My Name 1' THEN 'MY_NAME_1' WHEN SOURCE_COL1 = 'My Name 2' THEN 'MY_NAME_2' -- Add more mappings as needed ELSE SOURCE_COL1 -- Default case, if no match is found END FROM MYSCHEMA.MY_SOURCE_TABLE WHERE SOURCE_COL_ID IN (SELECT SOURCE_COL_ID FROM MYSCHEMA.MY_SOURCE_TABLE);

ALTER TABLE TABLE1 ADD CONSTRAINT FK_MYCONSTRRAINT FOREIGN KEY (PERSON_ID, R_ID) REFERENCES MY_ROLE (PERSON_ID, R_ID);

valueGetter: (params) => { const someNumber = params.data?.someNumber || ""; const ext = params.data?.ext ? Extn:${params.data.ext} : ""; return ${someNumber}${ext}; }

import java.lang.reflect.Field; Field[] fields = obj.getClass().getDeclaredFields(); for (Field field : fields) { field.setAccessible(true); }

"Not able to process request because of exception java.lang.reflect.InaccessibleObjectException: Unable to make field private final byte[] java.lang.String.value accessible: module java.base does not "opens java.lang" to unnamed module @27508c5d"

ALTER TABLE SCHEMA1.TABLE1 MODIFY COL1 VARCHAR2(50) NULL; ALTER TABLE SCHEMA1.TABLE1 MODIFY COL2 VARCHAR2(8) NULL;

axiosPostCall(apiUrl, payload) .then((response) => { responseHandler(response) .catch((e) => { // console.log("Error"); errorHandler(e,setServerErr) }) .finally(() => setLoading(false));

valueGetter: (params) => { const fileName = params.data.someField1 || ""; // Ensure field is not undefined return fileName.replace(/.[^/.]+$/, ""); // Remove the extension },

AUTHORIZATION=$(curl -X POST 'https://site/adfs/oauth2/token' \
-d 'grant_type=password&client_id=ABC-123&username=AB\A23223&password=Test5454@(&resource=ORG:URI:XX-111-111-OService-UAT' \
| jq -r '.access_token')

# Step 2: Extract access_key and secret_access_key using jq
access_key=$(echo "$response" | jq -r '.access_key')
secret_access_key=$(echo "$response" | jq -r '.secret_access_key')

# Step 3: Use aws configure to set the credentials
aws configure set aws_access_key_id "$access_key"
aws configure set aws_secret_access_key "$secret_access_key"

export PATH=$PATH:/usr/local/bin # Replace with the directory containing aws-cli

1/10/2025 02:03 PM ET
1/10/2025 07:06 AM ET


const dateComparator = (date1, date2) => {
  const parseDate = (dateStr) => {
    return new Date(
      dateStr.replace(/(\d+)\/(\d+)\/(\d+) (\d+):(\d+) (\w+) ET/, (match, month, day, year, hours, minutes, period) => {
        let hh = parseInt(hours, 10);
        if (period === "PM" && hh !== 12) hh += 12;
        if (period === "AM" && hh === 12) hh = 0;
        return `${year}-${month}-${day}T${hh.toString().padStart(2, "0")}:${minutes}:00`;
      })
    );
  };

  return parseDate(date1) - parseDate(date2);
};
Solution

  • You can use:

    =IF(D8:D16=0,"","INSERT INTO MYTABLE (ROLE_ID,GROUP,SUBGROUP) values('"&D8:D16&"','"&E8:E16&"','"&F8:F16&"')")

    enter image description here