Jump to content

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


 Share

Go to solution Solved by czardas,

Recommended Posts

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 by ChrisBair
Link to comment
Share on other sites

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 by DicatoroftheUSA
Link to comment
Share on other sites

  • Solution

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.

;

#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. :doh:

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 by czardas
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...