How to convert a big XLSX file to XML file

[zhenqin]

Hi everyone,  

In my project of converting XLSX to XML, my client provided me:a XSD file, a sample XML file which contains more than 670,000 lines and the XLSX format Excel templete which has 6 worksheets convering 6 difference category.

below are what I was working but neither workes. Any solutions and suggestions are highly welcome.

1. utilzed SAS XML mapper 9.45 to create sas maps file for first category, SAS creates 56 sas datasets for the first category after having SAS map file and I hope I can update 10 out off 56 datasets as requirements then export XML library to create new a XML file contains updating data and hope to complete the rest 5 categories in this  way. But this afteroon I was told only one SAS dataset can be utilzed XML map file to rebuild XML file.

2. Import XLSX into  SAS and create a llist of Macro variablles then use the follow code to update existing XML:

filename new_xml "...XML";

thanks for your viewing and comments

data _null_;

file new_xml;

put _infile_;

dataline4;

%str(<?xml version="1.0" encoding="utf-8"?>
<contactInfo xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="e.xsd">
<record>
<name>John Smith</name>
<company>ABC Company</company>
<phone>5555-4567</phone>
<datehired>2020-01-20</datehired>
<salary>&macrovariable</salary>
</record>
...)
;;;
run;

I am using SAS base 9.4 in windows 10 

[andreas_lds]

Avoiding data in macro variables is always something to strive for. Suggesting a solution without having access to the data is difficult, could you minimalise the data so that you could post it? Working with Excel is something that should be avoided, if it is planned to run the process regularly. Sooner than later the guessing process used to read Excel-files returns something not fitting to the following steps in your code.