none
Macro to import and decode values from a .csv file. RRS feed

  • Question

  •        

    I have a .csv file with a lot of HEX values like       4E0,AC1,065,123,B00, 0A0,1C0,A04, 6F etc.

    These numbers are actually measurement values from my datalogger.

    But these values are now coded by an XOR function just before the values are saved to my .csv file.

    I started with a simple test and coded all values by function"XOR" 0E0E.    So actually 0E0E is my key.

    The original measurement value is for example 4E0 and after the coding the result is E0E.

    Now I need to decode my value of E0E to 4E0 again by use of the macro in Excel.

    This can be done by the "XOR" 0E0E function again.

    I already managed to write all .csv values to different rows in decimal format but these values are all still coded.

    Who can help me to add a line to de-code my values?

    Underneath my code:

                                                 

    Sub Import()


        Application.Calculation = xlManual

        Rows("9:1048576").Select              ' Create an empty sheet
        Selection.ClearContents

        Sheets("Import").Select
        Dim i
        Dim sTemp As String
        Dim CH1_voltage As Single
        Dim CH1_current As Single
        Dim CH2_voltage As Single
        Dim CH2_current As Single

        Dim vars() As String
        Dim str As String
        Dim j As Long

        i = FreeFile

        myFile = Application.GetOpenFilename()
        Open myFile For Input As #1

        N = 0
        k = 10
        bas = 1
        'm = 0
        Do While Not EOF(i)
            Line Input #i, str
            vars = Split(str, ",")
            For j = LBound(vars) To UBound(vars) Step 4

                'CH1 calculate voltage of Channel 1
                t1 = Val("&h" & vars(j))                  'reads Hex and converts to Decimal raw value
                ActiveCell.Offset(N, 0) = t1              'writes Decimal raw value to cell A10 but still coded!

      '          t1 =  coded value XOR 0E0E        or something like that.......................
      '          ActiveCell.Offset(N, 1) = t1         Here I like to see the decoded decimal value......

                ActiveCell.Offset(N, 2) = (N)            'writes mSeconds

              N = N + 1

            Next j
        Loop

        Close #i
        Calculate
    End Sub



    • Edited by Bschriek Tuesday, June 25, 2019 2:50 PM
    Tuesday, June 25, 2019 2:49 PM

All replies

  • Hi

    I do not get the same results that you have.

    For example,

    Dim v As Integer = &H4E0 Dim v2 As String = (v Xor &HE0E).ToString("X4")

    ' &HAEE Dim v3 As String = ((v Xor &HE0E) Xor &HE0E).ToString("X4")

    ' &H4E0

    where the first XOR of&H4E0 returns &HAEE not &HE0E (I think you retyped the key instead of the value?)

    Anyway, the above code get back to the original value by repeating the XOR with the same key.


    Regards Les, Livingston, Scotland



    • Edited by leshay Tuesday, June 25, 2019 3:33 PM
    Tuesday, June 25, 2019 3:32 PM
  • Hi

    Here is code to read a CSV file of the format you show, and return a List(Of String) of the Decoded values (there is also a 'crosds_check' list that shows the values when XOR'd again using the dame key). It will Stop at the Stop line and let you hover/view the values.

    Option Strict On
    Option Explicit On
    Public Class Form1
    	Dim EncodedFilePath As String = IO.Path.Combine(My.Computer.FileSystem.SpecialDirectories.Desktop, "EnCoded.txt")
    	Dim Coded As New List(Of String)
    	Dim DeCoded As New List(Of String)
    	Dim cross_check As New List(Of String)
    	Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    		Coded = IO.File.ReadAllText(EncodedFilePath).Split(","c).ToList
    
    		DeCoded = DeCode(Coded, "E0E")
    
    
    		cross_check = DeCode(DeCoded, "E0E")
    
    		Stop
    	End Sub
    	Function DeCode(CD As List(Of String), key As String) As List(Of String)
    		Dim lst As New List(Of String)
    		For Each s As String In CD
    			lst.Add((Convert.ToInt32(s, 16) Xor Convert.ToInt32(key, 16)).ToString("X3"))
    		Next
    		Return lst
    	End Function
    End Class


    Regards Les, Livingston, Scotland


    • Edited by leshay Tuesday, June 25, 2019 4:35 PM
    Tuesday, June 25, 2019 4:34 PM
  • Dear Les, 

    Thank you very much!

    Although I understand the C-language quite well I don't understand Visual Basic at all.

    But with your help and some try and error I managed to get it all work well.

    You are right about the result of my very first post, I mixed up the value and key.

    I don't know why but the code underneath works great........

    ####################################

    Do While Not EOF(i)
            Line Input #i, str
            vars = Split(str, ",")
            For j = LBound(vars) To UBound(vars) Step 4

                'CH1 calculate voltage of Channel 1
                t1 = Val("&h" & vars(j))                  'reads coded Hex and converts to Dec value but stil coded.
                ActiveCell.Offset(N, 0) = t1              'writes coded Dec value to cell A10

                t2 = (t1 Xor &HE0E)                       'Coded Dec value is XORed by E0E (decoded)
                ActiveCell.Offset(N, 1) = t2              'The decoded Dec value is written to cel B10

                ActiveCell.Offset(N, 2) = (N)            'writes mSeconds to C10
                     
                N = N + 1
         
            Next j
        Loop
        
        Close #i

    ##########################################

    Many thanks,

    Bas

    Tuesday, June 25, 2019 7:06 PM
  • Hi,

    If your issue is solved,please mark the helpful as answer,or you have anything else about this issue, please feel free to contact us.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, June 27, 2019 8:07 AM