Code: Select all
I am trying to parse an xml file to csv. I then want to sort this csv file by one of the columns and display that in notepad. The xml file looks like this:
Code: Select all
<?xml version="1.0" encoding="utf-16"?>
<Searches>
<Search Name="Billy, Jean (#1234)" Id="15" Date="31.07.2018 11:17:00" SearchString="1234"/>
...
</Searches>
What I want to get as a file is this:
Code: Select all
Billy, Jean (#1234) 15 31.07.2018 11:17:00 1234
Doe, John (#1244) 23 22.06.2018 09:12:33 1244
...
Code: Select all
Name="Billy, Jean (#1234)" Id="15" Date="31.07.2018 11:17:00" SearchString="1234"
Name="
Id="
Date="
SearchString="
and the ending double quotes?
And how can I sort my file by colums, e. g. Name or Date?
Actually my AHK code looks like this:
Code: Select all
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn ; Enable warnings to assist with detecting common errors.
SendMode Input ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
in_file = %A_Desktop%\search.xml
out_file = %A_Desktop%\search.txt
ifexist,%out_file%
FileDelete, %out_file%
FileRead, file,%in_file%
if ErrorLevel
{
MsgBox,4144,Warning,Failed to load`n`n%in_file%`n`nExit Application.
ExitApp
}
;Loop, Read, %in_file%
;{
;total_lines = %A_Index%
;}
;MsgBox, Total number of lines in xml file: %total_lines%
loop parse, file, `n
{
while position := RegExMatch(file, "(?|(<\w*\s\w*=")|("\s\w*=")|("\/>))", match_pattern, A_Index=1?1:pos+StrLen(match_pattern))
{
result .= match_pattern "`n"
MsgBox % result
}
}
FileAppend, %result%, out_file
;MsgBox % result
Run notepad %out_file%
ExitApp
^x::
ExitApp
Code: Select all
loop parse, file, `n
I then tried to get rid of these by testing this code using regex:
Code: Select all
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn ; Enable warnings to assist with detecting common errors.
SendMode Input ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory.
; ------------------------------------------------------------------------------
; parse xml file and convert to csv, then sort by date and display sorted file
; ------------------------------------------------------------------------------
file_xml = %A_Desktop%\search.xml
out_file = %A_Desktop%\search.txt
ifexist,%out_file%
{
FileDelete, %out_file%
Sleep, 500
}
FileRead, in_file,%file_xml%
if ErrorLevel
{
MsgBox,4144,Warning,Failed to load`n`n%file%`n`nExit Application.
ExitApp
}
; ------------------------------------------------------------------------------
; manipulate xml file
; ------------------------------------------------------------------------------
Loop, Read, %file_xml%
{
total_lines = %A_Index%
}
loop parse, in_file, `n
{
if (A_Index = 1 or A_Index = 2 or A_Index = total_lines)
{
line = ""
}
else if (A_Index = total_lines -1) ;last line without cr
{
line := Trim(A_LoopField)
FileAppend, %line%, %out_file%
}
else
{
;line .= A_LoopField . "`n"
;MsgBox,4144,,%line%
line := Trim(A_LoopField) . "`n"
FileAppend, %line%, %out_file%
}
}
; ------------------------------------------------------------------------------
; manipulate out_file
; ------------------------------------------------------------------------------
total_lines = ""
FileRead, in_file, %out_file%
if ErrorLevel
{
MsgBox,4144,Warning,Failed to load`n`n%file%`n`nExit Application.
ExitApp
}
Loop, Read, %out_file%
{
total_lines = %A_Index%
}
ifexist,%out_file%
{
FileDelete, %out_file%
Sleep, 500
}
loop parse, in_file, `n
{
if (A_Index < total_lines)
{
StringTrimLeft, line, A_LoopField, 21
StringTrimRight, line, line, 3
line .= "`n"
FileAppend, %line%, %out_file%
}
else ;(A_Index = total_lines)
{
StringTrimLeft, line, A_LoopField, 21
StringTrimRight, line, line, 3
FileAppend, %line%, %out_file%
}
line := RegExReplace(line, "(\w*=")")
line := RegExReplace(line, "("\s+)", "\t")
}
; ------------------------------------------------------------------------------
; To do
; ------------------------------------------------------------------------------
; _extract every string between double quotes, delet rest of line
; _convert every 2nd double quote to a comma, delet all remaing double quotes.
; Thus generate a csv file
;_sort csv file by time stamp or name etc.
; ------------------------------------------------------------------------------
Run notepad %out_file%
ExitApp
^x::
ExitApp
Thanks a lot!