[Full Noob] Sorted XLS-List with Text, Adding Text to spezific XML-Line

[IamSoLaZe]

Hi,

im new to Autoit but also i had Programming like 20 Years ago. I am not very Familiar with it.

I would like to ask if somebody can Help me to create a Script:

My Problem:

I have a 3000+ row XLS File with Numerical Adresses in Colums and a Fitting Text for each Adress in a other Colum.

Example:

Adress          Text
Colum 1  Colum 2    Colum 3
8000     1          I am Sensor 1
8000     2          I am Sensor 2
8001     1          I am Sensor 300
8004     5          I am Senser 0815

Now i have got a unsorted XML-Data where i need to add my Text to the right Adress but i am not allowed to Change the Order. Also some "Adress" are not included.

<Items>
    <Item>
        <ID>1-1-Point-8800-2</ID>
        <Label></Label>
    </Item>
    <Item>
        <ID>1-1-Point-8800-1</ID>
        <Label></Label>
    </Item>
    <Item>
        <ID>1-1-Point-8804-5</ID>
        <Label></Label>
    </Item>
</Items>

I would convert my xls in a .csv, and edit the text to have it easier for string opertaions.

8000-1;I am Sensor 1

I think i would take two Arrays and Read every row, AdressArray and TextArray - So my Index would be identical?

I Think this would be my Flowchart:

For Each Cell in AdressArray //For Every Address i got
    If <ID> == CellOfAddressArray == ture //Finding the right Text
    <Label> = "CellofTextarray" //Changing the Text with the same Index from Address
    
    IF <ID> == CellofAddressArray == false // If Address is not Avalible in XML
    CellOfAddressArray +1 //Take the next Address

This need to be my Output:

<Items>
    <Item>
        <ID>1-1-Point-8800-2</ID>
        <Label>I am Sensor 2</Label>
    </Item>
    <Item>
        <ID>1-1-Point-8800-1</ID>
        <Label>I am Sensor 1</Label>
    </Item>
    <Item>
        <ID>1-1-Point-8004-5</ID>
        <Label>I am Sensor 0815</Label>
    </Item>
</Items>

 

 

 My Code Idea but as i said i am unskilled and not able to solve/create it - but i wont Copy paste 3000+ Texts by hand 😉 

Global $XML = @ScriptDir & '\XML.xml' ; Var for XML File
Global $XLS = @ScriptDir & '\Address.xls' ; Var fo XLS File
Global $OutputXml = @ScriptDir & '\Output.xml';
Global $XMLData = FileRead($XML) ; Loading into Ram
Global $XLSData = FileRead($XLS) ; Loading into Ram

Global $VirtualXML = StringSplit($XMLData, @CRLF, 1) ; Data To Array
Global $VirtualXLS = StringSplit($XLSData, @CRLF, 2) ; Data To Array

$i = 0 ;
For $i = $iStart To $iEnd ; Copyed this noticed that i use it for increment Array index
    
    If StringInStr($VirtualXML[$i],$VirtualXLS[$i]) ; Comparing ArrayIndex Copyed this, Mainoperation dont know how to do
    
    $VirtualXML[$i]Label &= $VirtualXLS[$i.2]Text ; Copyed this, Changing the Label with the seccond Colum where Text is located
Next


$OutputXml = FileOpen() ; Open File Copyed this
If $OutputXml <> -1 Then ; Copyed this think it is if file open succesful
    FileWrite() ; Copyed this Write and Save File
    FileClose($OutputXml) ; Copyed this Write and Save File
EndIf

 

Thanks for your Help in advance.

[Andreik]

I used these files as input

Address.xlsx XML.xml

#include <Excel.au3>

Global $sXLSPath = @ScriptDir & '\Address.xlsx'
Global $sXMLPath = @ScriptDir & '\XML.xml'
Global $sXML = FileRead($sXMLPath)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen($oExcel, $sXLSPath, True, False)
$aData = _Excel_RangeRead($oWorkBook, Default, 'A1:C4')
_Excel_BookClose($oWorkBook, False)
_Excel_Close($oExcel, False)

If IsArray($aData) Then
    For $Index = 0 To UBound($aData) - 1
        $sRegex = '<Item>(\s*)<ID>(.*?)' & $aData[$Index][0] & '\-' & $aData[$Index][1] & '<\/ID>(\s*)<Label>(.*?)<\/Label>(\s*)<\/Item>'
        $sReplace = '<Item>${1}<ID>${2}' & $aData[$Index][0] & '-' & $aData[$Index][1] & '</ID>${3}<Label>' & $aData[$Index][2] & '</Label>${5}</Item>'
        $sXML = StringRegExpReplace($sXML, $sRegex, $sReplace)
    Next
EndIf

FileWrite(@ScriptDir & '\Output.xml', $sXML)

Output:

Quote

<Items>
    <Item>
        <ID>1-1-Point-8000-2</ID>
        <Label>I am Sensor 2</Label>
    </Item>
    <Item>
        <ID>1-1-Point-8000-1</ID>
        <Label>I am Sensor 1</Label>
    </Item>
    <Item>
        <ID>1-1-Point-8004-5</ID>
        <Label>I am sensor 0815</Label>
    </Item>
</Items>

 

[IamSoLaZe]

Oh Thanks for that fast answer!

I Added my Files i work with.

 

Those are minimized because of max. 2MB Upload

 

The XML File is already existing and the Text needed to be placed at the right Position.

After Running your Script the Text got duplicated in my Output.

 

#include <Excel.au3>

Global $sXLSPath = @ScriptDir & '\Address.xlsx'
Global $sXMLPath = @ScriptDir & '\XML.xml'
Global $sXML = FileRead($sXMLPath)

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen($oExcel, $sXLSPath, True, False)
$aData = _Excel_RangeRead($oWorkBook, Default, 'A1:C4')
_Excel_BookClose($oWorkBook, False)
_Excel_Close($oExcel, False)

If IsArray($aData) Then
    For $Index = 0 To UBound($aData) - 1
        $sRegex = '<Item>(\s*)<Siid>(.*?)' & $aData[$Index][0] & '\-' & $aData[$Index][1] & '<\/Siid>(\s*)<Label>(.*?)<\/Label>(\s*)<\/Item>'
        $sReplace = '<Item>${1}<Siid>${2}' & $aData[$Index][0] & '-' & $aData[$Index][1] & '</Siid>${3}<Label>' & $aData[$Index][2] & '</Label>${5}</Item>'
        $sXML = StringRegExpReplace($sXML, $sRegex, $sReplace)
    Next
EndIf

FileWrite(@ScriptDir & '\Output.xml', $sXML)

 

xml.xml Address.xls

[Andreik]

You said your sample xml looks like this

<Label></Label>

but what you upload later it's totally different.

[Andreik]

2 hours ago, IamSoLaZe said:

The XML File is already existing and the Text needed to be placed at the right Position.

Here is a version where you can choose between replacing or appending the content:

#include <Excel.au3>

Global $sXLSPath = @ScriptDir & '\Address.xls'
Global $sXMLPath = @ScriptDir & '\XML.xml'
Global $fReplace = True

$oExcel = _Excel_Open(False)
$oWorkBook = _Excel_BookOpen($oExcel, $sXLSPath, True, False)
$aData = _Excel_RangeRead($oWorkBook, Default, 'A1:C499')
_Excel_BookClose($oWorkBook, False)
_Excel_Close($oExcel, False)

Local $oXML = ObjCreate('Microsoft.XMLDOM')

If IsArray($aData) And IsObj($oXML) Then
    $oXML.load($sXMLPath)
    $oXML.setProperty('SelectionLanguage', 'XPath')
    For $Index = 0 To UBound($aData) - 1
        $oNode = $oXML.selectSingleNode("//Configuration/Items/Item/Siid[text() = '1-1-POINT-" & $aData[$Index][0] & "-" & $aData[$Index][1] & "']/following-sibling::*[1]")
        If IsObj($oNode) Then $oNode.Text = ($fReplace ? $aData[$Index][2] : $oNode.Text & $aData[$Index][2])
    Next
    $oXML.save(@ScriptDir & '\output.xml')
Else
    ConsoleWrite('No data in XLSX or invalid XML object.' & @CRLF)
EndIf

Change $fReplace = False and the text will be appended to current text in Label element.