Its tax time! was thinking about writing something new and unique task, I worked on. Right at that moment, my team gave me this task.
Our company acquired another company. new company payroll history we received is not sufficient to generate ACA files in Oracle Cloud. So, we brought this tax year’s ACA information in Excel format.
I need to convert this excel data to xml (as per the irs tax form 1095C format).
Tools used: R 4.3 , Excel
library(xml2)
library(dplyr)
library(readxl)
setwd(dir = "C:\\Users\\amalla\\Desktop\\ACA\\excel to XML")
# read the XML structure from Excel sheet
XmlStructure <- read_excel(path = "C:\\Users\\amalla\\Desktop\\ACA\\excel to XML\\1095C Sample.xlsx", sheet = "Structure")
XmlData <- read_excel(path = "C:\\Users\\amalla\\Desktop\\ACA\\excel to XML\\1095C Sample.xlsx", sheet = "Sheet2")
XmlData <- XmlData[c(-1,-2),-1 ]
colnames(XmlData) <- XmlStructure$MAPPING
# data cleaning
XmlData$`Contact Telephone Number` <- str_replace_all(XmlData$`Contact Telephone Number`, "-", "")
XmlData$`Contact Telephone Number` <- as.character(XmlData$`Contact Telephone Number` )
dataStr <- structure(XmlStructure)
xml <- xmlTree()
# names(xml)
xml$addTag("Form1095CRootNode", close=TRUE, attrs=c(lineNum="0", recordType=""))
rootnodelist <- data.frame()
for (i in 1:nrow(XmlData)) {
print(i)
rootnodelist <- NULL
xml$addTag("Form1095CUpstreamDetail", close=FALSE, attrs=c(lineNum="0", recordType=""))
for (j in 1:length(names(XmlData))) {
if(is.na(XmlStructure[j, 2])){next}
if(XmlStructure[j, 4]=="Y"){
strsplit(as.character(XmlStructure[j, 3]), split = "[/]") -> nodes
if (length(nodes[[1]]) > 1) {
print(nodes[[1]])
lapply(nodes[[1]], function(x){xml$addTag(as.character(str_replace(x, "irs:", ""), close=FALSE))})
xml$addTag(as.character(str_replace(XmlStructure[j, 2], "irs:", "")), XmlData[i, j])
rootnodelist <- rbind(rootnodelist,nodes[[1]])
}
}
xml$addNode(as.character(str_replace(XmlStructure[j, 2], "irs:", "")), XmlData[i, j])
}
xml$closeTag()
print(i)
}
cat(saveXML(xml), file = "sampelXml.txt")
References: Ofcourse! I googled for some tips/tricks/code snippets. Didn’t get any which worked 90-100% range. Few articles/resources helped me to achieve my task, which are mentu
https://stackoverflow.com/questions/41943939/read-excel-xml-file-into-r