Replacing data in one array with data from another and then writing to a file

[ChrisBair]

OK, so yesterday I got major help from the forum in parsing a "csv" (actually tab delimited) at '?do=embed' frameborder='0' data-embedContent>> Now comes the next stage. I started a new topic to keep things clean.

So we are sending text strings off to our translation department in a specifically formatted text file, they translate them and then we have to put those translated strings back into the "csv" from whence they originated so it can be imported back into SalesForce (they are knowledge base articles).

The original file looks like this: https://dl.dropboxusercontent.com/u/6943630/Answer__kav.csv

and the file we get back from translation looks like this: https://dl.dropboxusercontent.com/u/6943630/translations.properties (it's a text file)

The Answer__kav.csv gets pulled into an array using the code #entry1154488'>czardas posted yesterday. Col11 (the Historical_Change_Notes__c field) is a nuisance due to its line feeds and should really just get removed from the final output so it stops messing things up. 

In translations.properties I had to delimit stuff with first a pipe and then a = (the translation people ignore anything before the equals). I suspect I can use something like 

StringRegExp($sText, '(kA+\w*)\|(\d*)\=(.*)', 3)

But I probably need it in a 2D array just like the first file so that I'll be able to match the first columns and then replace the contents of the third column with the translated text from the second array. Oh and did I mention that the translated strings will often be Korean, Japanese or Chinese? so I'm guessing it all has to support UTF-8. Incidentally, the first line of the translations.properties file is a hash commented reference to the original file that the translated strings match up to.

I'm guessing that if I hack away at this for a week or so I might be able to do it but arrays aren't my strong suite.

Edited February 19, 2014 by ChrisBair

[DicatoroftheUSA]

Search the helpfile for "unicode" and "endian", it may answer your question about korean text. But it should work.

if you use flag 4 in StringRegExp instead of three, it will return a 2d result. eg "StringRegExp($sText, '(kA+w*)|(d*)=(.*)', 4)"

If you need to filter what is left of the pipe? "?:" will match but exclude it eg "(?:kA+w*)|(d*)=(.*)"

Also the default gui font for auto-it seems  to not render Korean, at least on my computer. If you are using a gui, try adding the following at the beginning of the gui function

GUISetFont(8,400,1,"Arial Unicode MS"), or whatever font that you know that works for you.

What are you having trouble with regarding arrays?

Edited February 19, 2014 by DicatoroftheUSA

[czardas]

I'm not sure if this is exactly what you want. You will need to test it. It may be possible to simply use _ArrayToCSV, but that only adds double quotes to fields that absolutely require them. The format is very specific and your software may not accept any TSV, so I have recreated the same format. This should work if there is no corruption in the files, but you need to test it properly. There may be more efficient methods and I may have overlooked something.

;

expandcollapse popup

#include 'CSVSplit.au3' ; see link above

Local $sFilePath = @ScriptDir & "\Answer__kav.csv" ; Change this to you own file path
Local $sData = _ReadFile($sFilePath)
If @error Then Exit

Local $aTSV = _CSVSplit($sData, @TAB) ; Parse TAB Separated Values (TSV)
_ArrayDisplay($aTSV)

$sFilePath = @ScriptDir & "\translations.properties.txt" ; Change this to you own file path
Local $sData = _ReadFile($sFilePath)
If @error Then Exit

Local $aTranslation = StringRegExp($sData, "[\r\n].+", 3)
If @error Then
    ConsoleWrite("Syntax Error - TERMINATED" & @LF)
    Exit ; Problem needs reporting.
EndIf

;_ArrayDisplay($aTranslation)

Local $aTemp
For $i = 0 To UBound($aTranslation) -1
    $aTemp = StringSplit($aTranslation[$i], "|=")
    If $aTemp[0] <> 3 Then
        ConsoleWrite("Syntax Error - IGNORED" & @LF)
        ; Here you probably want to make some kind of report.
        ContinueLoop ; Or throw an error and exit the script.
    EndIf

    $aTemp[1] = StringRegExpReplace($aTemp[1], "[\r\n]", "")

    ; Not assuming the translated file data will be returned in the same order.
    For $j = 1 To UBound($aTSV) -1 ; The search process could possibly be optimized
        If $aTSV[$j][0] = $aTemp[1] And $aTSV[$j][1] = $aTemp[2] Then
            $aTSV[$j][2] = $aTemp[3]
            ContinueLoop
        EndIf
    Next
Next
_ArrayDisplay($aTSV)

; Convert back to TSV
Local $sNewTSV = ""
For $i = 0 To UBound($aTSV) -1
    $sNewTSV &= $aTSV[$i][0]
    For $j = 1 To UBound($aTSV, 2) -1
        $sNewTSV &= @TAB & """" & $aTSV[$i][$j] & """"
    Next
    $sNewTSV &= @CRLF
Next

MsgBox(0, "Press Escape To Close", $sNewTSV) ; Seems to have worked.

; Write $sNewTSV to a new file here and run some tests .

Func _ReadFile($sFilePath)
    Local $hFile = FileOpen($sFilePath)
    If $hFile = -1 Then Return SetError(1) ; Unable to open file

    Local $sData = FileRead($hFile)
    If @error Then
        FileClose($hFile)
        Return SetError(2) ; Unable to read file
    EndIf
    FileClose($hFile)

    Return $sData
EndFunc

;

Oops there is one thing - I forgot to add back the quotes.

A second small change made for optimization.

---

To write the file and test the results, add the following lines to the end of the code above: You need to add some error checking - for FileOpen() and FileWrite().

$sFilePath = @ScriptDir & "\Answer__kav_Result.csv"
$hFile = FileOpen($sFilePath, BitOr(128, 2)) ; UTF-8 with BOM

FileWrite($hFile, $sNewTSV)
FileClose($sFilePath)

Local $sData = _ReadFile($sFilePath) ; Test the resulting file.
If @error Then Exit

Local $aTSV = _CSVSplit($sData, @TAB) ; Parse TAB Separated Values (TSV)
_ArrayDisplay($aTSV)

Edited February 19, 2014 by czardas

[ChrisBair]

wow czardas, you're like my hero or something! As far as I can tell it handled combining the two together perfectly. Cool Beans!

[czardas]

Okay good. It's useful to assess my own code sometimes, and provide a new example for CSVSplit.

Edited February 20, 2014 by czardas