0

I've been asked to replicate a file sent by the following Excel VBA code. The problem is with the export of the data contained within the "for" loop. The quantity ".111" is being written in the export file as "øSã=", ".222" is being written as "øSc>", and ".333" is being written as "ú~ª>".

I'm not entirely sure what is happening here. But any rate the target legacy system I have send our data to is able to read this data correctly (i.e. it is converted back into the original values).

Does anyone have any thoughts on what is going on and how to replicate this behaviour so that my file can be read?

Type Rigo_File
Status As Integer
Invio As Integer
Codice As String * 13
Quantita As Single
Udm As Integer End Type

Type type_file
Partita As String * 10
Macchina As String * 25
articolo As String * 25
colore As String * 25
note As String * 25
urgenza As Integer
Invio As String * 3
Righi(20) As Rigo_File
End Type

Dim NrOpen As Integer
Dim NomeFile As String, NomeFileTmp As String
Dim Rigo_File
Dim type_file
Dim typeM As type_file
Dim c As Integer

Sub CREATEFILE()
FILEDIR = "C:\"
FILENAMEE = "TESTFILE1.txt"

Partita = Right(Cells(1, 3), 10)
Macchina = Left(Cells(2, 3), 25)
articolo = Left(Cells(3, 3), 25)
colore = Left(Cells(4, 3), 25)
note = Left(Cells(5, 3), 25)
urgenza = CDbl(Cells(6, 3))

With typeM
.Partita = Partita
.Macchina = Macchina
.articolo = articolo
.colore = colore
.note = note
.urgenza = urgenza
.Invio = "001"
For ci = 1 To 20
.Righi(ci).Status = True
.Righi(ci).Invio = 1
.Righi(ci).Codice = Cells(8 + ci, 2)
.Righi(ci).Quantita = Cells(8 + ci, 3)
.Righi(ci).Udm = 1
Next ci
End With

NrOpen = FreeFile
On Error GoTo 0

Open FILEDIR & FILENAMEE For Random Access Write Shared As #NrOpen Len = Len(typeM)

Put #NrOpen, 1, typeM
Close #NrOpen

End Sub

Caledonian Coder
  • 919
  • 1
  • 9
  • 16
  • if the data is being read correctly, then there isn't a worry, but I would look at the representation of numbers in memory: http://stackoverflow.com/questions/13663026/how-to-represent-floating-point-in-binary-ieee – SeanC Apr 18 '13 at 15:01

2 Answers2

1

Put # writes data in a kind of binary format - preceeding fields with length bytes, converting numbers to binary and other useful things. This is no problem as long as you use Get # to read the data in again. Though your file is not strictly human readable.

If you want to write plain text ASCII data, use Print # or Write # instead, and observe the subtile differences between these two (delimiters, terminating CR+LF, etc.).

You may also need to specify each element of TypeM individually in the Print # or Write # statement - I am not sure these two will accept user defined objects.

But beware: your target system which is OK to read files created by Put # may refuse files created by Print # or Write #

MikeD
  • 8,861
  • 2
  • 28
  • 50
  • My problem is how to replicate this binary format using T-SQL since that is all that the legacy system understands. I'm trying to find some documentation on the nature of the conversion. – Caledonian Coder Apr 19 '13 at 10:02
  • ahhh ... that's a different question ... well my first reflex would be to ask Excel read in a plain text file obtained from the database using `Input #1` and output it to another file using `Put #2` – MikeD Apr 19 '13 at 10:09
  • Ideally it would be good to be able to replicate in T-SQL the way that Put# (or the newer 'BinaryWriter') write a single to a file. – Caledonian Coder Apr 19 '13 at 15:19
  • now I am leaving my comfort zone; maybe the `CAST()` or `CONVERT()` functions can be of use – MikeD Apr 22 '13 at 13:42
1

After some hunting around I have found the following:

    Dim bArray As Byte()
    Dim val As Single = 0.111
    Dim sChars as String
    Dim arrChars as String()
    Dim sFinal as string

    bArray = BitConverter.GetBytes(val)
    sChars = BitConverter.ToString(bArray)
    arrChars = Split(sChars, "-")
    For Each sChar as string in arrChars
        sFinal & = ChrW(Convert.ToInt32(sChar, 16))
    Next

This does the same kind of conversion as the Put# method and the BinaryWriter.Write method does, without the need to write to a file.

Caledonian Coder
  • 919
  • 1
  • 9
  • 16