pythonexcelvbaraspberry-pipscp

Sending an input file from Excel VBA to Raspberry Pi without change in content


I am sending a text file from my Excel to a Raspberry Pi, where the Pi will read that file and get the input inside. The input is just one line and it is a simple number and nothing else (40,38 etc.)

When I run this macro, I can see the input file in my PC perfectly but when I open it inside the Pi, it is changed for example:

Input file in my PC is:

65

Input file in Raspberry is:

ÿþ6

How can I make sure this number is sent to Pi just as it is. Or how do I decode this to something my python script can understand. I will not use decimals with this number and it does not matter if it is sent as a string as I can parse it later inside my python code.

Below is my Excel Macro

Sub Send()

    Dim sleeptime As String
    sleeptime = InputBox("Duration between each reading in seconds?")

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim Fileout As Object
    Set Fileout = fso.CreateTextFile("C:\Users\xx\Desktop\VBA\input.txt", True, True)
    Fileout.Write sleeptime
    Fileout.Close
    Set fso = Nothing
    Set Fileout = Nothing


    Const cstrSftp As String = """C:\Program Files\PuTTY\pscp.exe"""
    Dim strCommand As String
    Dim pUser As String
    Dim pPass As String
    Dim pHost As String
    Dim pFile As String
    Dim pRemotePath As String

    pUser = "pi" '//user on remote system
    pPass = "xx" '//user's password on remote system
    pHost = "192.168.x.xx" '//ip address of remote system
    pFile = "C:\Users\xx\Desktop\VBA\input.txt" '//file to transfer
    pRemotePath = "/home/pi/Temp_Codes" '//directory where file will be transferred to

    strCommand = cstrSftp & " -sftp -l " & pUser & " -pw " & pPass & _
        " " & pFile & " " & pHost & ":" & pRemotePath
    Debug.Print strCommand
    Shell strCommand, 0 ' vbHide

End Sub

And these are the lines inside a script of my Raspberry Pi where I use this input file. I later use this sleeptime value inside time.sleep.

with open('input.txt', 'r') as myfile:
    sleeptime = myfile.read()

sleeptime = float(sleeptime.strip())

Here is a simple code I am running just to test this encoding thing:

#!/usr/bin/env python

import io

with io.open('input.txt', 'r', encoding='utf-8-sig') as myfile:
    sleeptime = myfile.read()

sleeptime = float(sleeptime.strip())

This is the error I am getting so far.

pi@raspberrypi:~/Temp_Codes $ python try.py
Traceback (most recent call last):
  File "try.py", line 6, in <module>
    sleeptime = myfile.read()
  File "/usr/lib/python2.7/codecs.py", line 314, in decode
    (result, consumed) = self._buffer_decode(data, self.errors, final)
  File "/usr/lib/python2.7/encodings/utf_8_sig.py", line 66, in _buffer_decode
    return codecs.utf_8_decode(input, errors, final)
UnicodeDecodeError: 'utf8' codec can't decode byte 0xff in position 0: invalid start byte

Solution

  • When Excel/VBA creates a Unicode file via "CreateTextFile", it's saving it in a UTF-16 encoding. What you're seeing is the BOM (byte order mark).

    You could change

    CreateTextFile("C:\Users\xx\Desktop\VBA\input.txt", True, True)
    

    to

    CreateTextFile("C:\Users\xx\Desktop\VBA\input.txt", True, False)
    

    That will save as an ASCII text file. Or you could make the change on the Pi/Python side by using

    open('input.txt', 'r', encoding='utf-16')