none
Import CSV file which contains .HEX values. RRS feed

  • Question

  • Our new datalogger saves data in the .Hex format instead of the conventional .dec format.

    Who can help me to change the VBA code so the .Hex CSV generates the same output as usuall?

    Microsoft Visual Basic for Applications 7.0   version 1643 

    See the code underneath:

    Sub Import()

        '  CSV file with decimal numbers works great
        '  CSV file is created by a datalogger and measures voltage and current
        '  CSV file contains numbers with a fixed length (always 4 characters)
        '2560, 100,2561, 101,2562, 102,2563, 103,2564, 104,  and so on.....

        '  CSV file with hexadecimal numbers will go wrong
        '  CSV file contains numbers with a fixed length (always 3 characters)
        'A00, 64,A01, 65,A02, 66,A03, 67,A04, 68,  and so on.....
        ' The goal is to get the same results for cell G10 and H10 but now with a .hex input.

        Application.Calculation = xlManual

        Rows("10:1048576").Select              ' Create an empty sheet
        Selection.ClearContents
        Range("P10").Select

        Sheets("Import").Select
        Range("A10").Select                    ' Goto cell A10
        Dim i
        Dim sTemp As String
        Dim CH1_voltage As Single
        Dim CH1_current As Single

        i = FreeFile

        Open "j:\NAMEHERE.txt" For Input As #i      ' Open CSV file "NAMEHERE.txt"
        n = 0
        m = 0
        Do While Not EOF(i)
            'CH1 calculate voltage of Channel 1
            Input #i, sTemp1
            ActiveCell.Offset(n, 0) = sTemp1        'writes raw CSV value "2560" to cell A10
            CH1_voltage = sTemp1 / 100              'calculates voltage
            ActiveCell.Offset(n, 6) = CH1_voltage   'writes calculated value "25,60" to cell G10

            'CH1 calculate current of Channel 1
            Input #i, sTemp2
            ActiveCell.Offset(n, 1) = sTemp2        'writes raw CSV value "100" to cell B10
            CH1_current = (sTemp2 - 50) / 5         'calculates current
            ActiveCell.Offset(n, 7) = CH1_current   'writes calculated value "10,00" to cell H10

            n = n + 1
        Loop

        Close #i
        Calculate
    End Sub

                                                                 
    • Edited by Bschriek Friday, June 29, 2018 12:12 PM
    Friday, June 29, 2018 9:54 AM

All replies

  • You can convert hex to decimal like this:

      decValue = val("&h" & HexDigits)
    I don't know the brand of your datalogger but all the ones I've seen like Agilent have a setup option to define format of output.  The most complex was Instrunet binary output.

    • Edited by mogulman52 Friday, June 29, 2018 1:26 PM
    Friday, June 29, 2018 1:19 PM
  • Hello Mogulman52

    Thank you in advance,

    Sorry but I don't understand how to implement your formula into my VBA code.

    The datalogger is home made and measures at 4 channels/12 bit every 1mSec.

    Then the data is saved to an USB disk by use of a Vdrive3 of FTDI.

    To reduce the amount of data from the SRAMs to the Vdrive we decided to send the data in .Hex format.

    All works well exept my Excel code doesn't accept .hex numbers. In the past the decimal values were ok.

    I'm a hardware guy and don't have knowledge about VBA.  C-language is no problem.

    Therefore this post.

    Best regards,

    Bas

    Friday, June 29, 2018 2:42 PM
  • Something like this:

    Sub Import()
    
        '  CSV file with decimal numbers works great
        '  CSV file is created by a datalogger and measures voltage and current
        '  CSV file contains numbers with a fixed length (always 4 characters)
        '2560, 100,2561, 101,2562, 102,2563, 103,2564, 104,  and so on.....
    
        '  CSV file with hexadecimal numbers will go wrong
        '  CSV file contains numbers with a fixed length (always 3 characters)
        'A00, 64,A01, 65,A02, 66,A03, 67,A04, 68,  and so on.....
        ' The goal is to get the same results for cell G10 and H10 but now with a .hex input.
    
        Application.Calculation = xlManual
    
        Rows("10:1048576").Select              ' Create an empty sheet
        Selection.ClearContents
        Range("P10").Select
    
        Sheets("Import").Select
        Range("A10").Select                    ' Goto cell A10
        Dim i
        Dim sTemp As String
        Dim CH1_voltage As Single
        Dim CH1_current As Single
    
        i = FreeFile
    
        Open "c:\temp\hexcsv.txt" For Input As #i      ' Open CSV file "NAMEHERE.txt"
        n = 0
        m = 0
        Do While Not EOF(i)
            'CH1 calculate voltage of Channel 1
            Input #i, sTemp1
            t1 = Val("&h" & sTemp1)
            ActiveCell.Offset(n, 0) = t1       'writes raw CSV value "2560" to cell A10
            CH1_voltage = t1 / 100               'calculates voltage
            ActiveCell.Offset(n, 6) = CH1_voltage   'writes calculated value "25,60" to cell G10
    
            'CH1 calculate current of Channel 1
            Input #i, sTemp2
            t2 = Val("&h" & sTemp2)
            ActiveCell.Offset(n, 1) = t2        'writes raw CSV value "100" to cell B10
            CH1_current = (t2 - 50) / 5         'calculates current
            ActiveCell.Offset(n, 7) = CH1_current   'writes calculated value "10,00" to cell H10
    
            n = n + 1
        Loop
    
        Close #i
        Calculate
    End Sub
    

    Saturday, June 30, 2018 1:46 AM
  • Hello Mogulman52

    Thank you for your help, your code solved the problem but only for half????

    Most of the .hex values are converted the right way but some are not.

    To investigate the problem i slightly changed the code and hexcsv.txt file.


        'A00,4E0,AC1, 65,123,B00, A0,1C0,A04, 6F       (new input of the hexcsv.txt file)
        'HEX values are converted to DEC values but someting goes wrong here!
        '2560,4,2753,101,291,2816,160,1,2564,6         (output at cell A10 to A19)

    When the input is 4E0, 1C0 or 6F  the output is wrong????

    Sub Import()
    
        ' NEW SET OF SAMPLES, pleae try these and you will see the conversion goes wrong.
        'A00,4E0,AC1, 65,123,B00, A0,1C0,A04, 6F       'hexcsv.txt
        'HEX values are converted to DEC values but someting goes wrong here!
        '2560,4,2753,101,291,2816,160,1,2564,6         'value of cell A10 to A19
    
        'When the input is 4E0, 1C0 and 6F then is goes wrong.
    
        Application.Calculation = xlManual
    
        Rows("10:1048576").Select              ' Create an empty sheet
        Selection.ClearContents
        Range("P10").Select
    
        Sheets("Import").Select
        Range("A10").Select                    ' Goto cell A10
        Dim i
        Dim sTemp As String
        Dim CH1_voltage As Single
        Dim CH1_current As Single
    
        i = FreeFile
    
        Open "c:\temp\hexcsv.txt" For Input As #i
        
        n = 0
        m = 0
        Do While Not EOF(i)
            'CH1 calculate voltage of Channel 1
            Input #i, sTemp1
            t1 = Val("&h" & sTemp1)
            ActiveCell.Offset(n, 0) = t1       'writes the output in cell A10 to A19
            CH1_voltage = t1 / 100
            ActiveCell.Offset(n, 6) = CH1_voltage
    
            n = n + 1
        Loop
    
        Close #i
        Calculate
    End Sub

    Monday, July 2, 2018 7:45 AM
  • Input #i, sTemp1 this causes the problem and does not input .hex values right anytime.

    Because the .hex number is fixed (always 3 char with space if required) maybe there is a

    command to INPUT 3 characters everytime.



    • Edited by Bschriek Monday, July 2, 2018 11:05 AM
    Monday, July 2, 2018 10:03 AM