VBA: Edit HEX of Existing CSV Style File

[InoSiX]

I have a file produced by a proprietary third party application that is effectively a CSV with one specific exception: the first 3 rows do not follow a tradtional CSV format.

I can open the file in Excel, and the file represents itself correctly. If I open, hit save (changing nothing) and then attempt a reimport, the reimport fails. If I open the file with Notepad, hit save, and reimport, the file import succeeds. I used a hex editor (HxD) to review the original file and compare it against the file that gets saved after opening in Excel and discovered the issue.

As I have identified the issue, I'll save space by providing an example that only includes the header (s) and the first data entry line. To prevent confusion, I have limited the cell contents in the example. It isn't the contents of the cells that are the problem, it's how Excel ultimately saves it (correctly, but my third part application rejects it).

Original Exported Cells
A1 'title 1
A2 'title 2
A3 'title 3
A4 B4 C4 D4 E4 F4 G4 H4 I4 'table header
A5 B5 C5 D5 E5 F5 G5 H5 I5 'first line of data

Note: only cells A1, A2 and A3 in the original file contain data, the remaining cells in that row are empty. The original file opened in HxD shows this:

A1 (CR) (LF)
A2 (CR) (LF)
A3 (CR) (LF)
A4 (TAB) B4 (TAB) C4 (TAB) D4 (TAB) E4 (TAB) F4 (TAB) G4 (TAB) H4 (TAB) I4 (CR) (LF)
A5 (TAB) B5 (TAB) C5 (TAB) D5 (TAB) E5 (TAB) F5 (TAB) G5 (TAB) H5 (TAB) I5 (CR) (LF)

Where (CR) is HEX: 0D
(LF) is HEX: 0A
(TAB) is HEX: 09

The issue is that when Excel saves the original file after opening, it saves the file with additional (TAB) (as might be expected given how CSV traditionally work). It becomes:

A1 (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (CR) (LF)
A2 (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (CR) (LF)
A3 (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (CR) (LF)
A4 (TAB) B4 (TAB) C4 (TAB) D4 (TAB) E4 (TAB) F4 (TAB) G4 (TAB) H4 (TAB) I4 (CR) (LF)
A5 (TAB) B5 (TAB) C5 (TAB) D5 (TAB) E5 (TAB) F5 (TAB) G5 (TAB) H5 (TAB) I5 (CR) (LF)

Using HxD, if I remove all the (TAB) characters from the A1, A2 and A3 rows and save, the third party application accept the file. This has been a project I have been working on for just under a week. I have overcome every other hurdle but this doesn't exist within Excel per se. I need the solution to exist with something I can run from Excel (i.e. VBA). Third party helper apps are OK.

Basically I need a VBA script to replace the raw HEX:

09 09 09 09 09 09 09 09 0D 0A
with
0D 0A

Does anyone have a resource to help me out here?

P.S. I did locate this post:

Write hex values to binary file in VBA Excel

Hello to all, I want to write the hexadecimal values to a binary file in order they look the same when I open in hex editor. My current code is this: Sub Write2Binary() Dim i As Integer Dim nFileNum As Integer Dim sFilename As String sFilename = "D:\OutputPath\Test.bin" strBytes = "F3...

www.mrexcel.com

 

[InoSiX]

Alternatively, another solution I came up with, was to have a master file from an original export that only contained the first 4 rows (A1 through A4). A5 and all data after A5 contain the data entry lines. If I produced a CSV that contain nothing but data lines, I could merge the master file with the data entry CSV. Obviously the solution couldn't involve opening the file in Excel, as Excel is the problem in the first place.

 

[yky]

Alternatively, another solution I came up with, was to have a master file from an original export that only contained the first 4 rows (A1 through A4). A5 and all data after A5 contain the data entry lines. If I produced a CSV that contain nothing but data lines, I could merge the master file with the data entry CSV. Obviously the solution couldn't involve opening the file in Excel, as Excel is the problem in the first place.

Can you join the two files by doing a TYPE command in the DOS window, something like "TYPE mycsv.csv >> mycsvheader.csv"? If that works, you may write a batch file to simplify the process.

 

[InoSiX]

Can you join the two files by doing a TYPE command in the DOS window, something like "TYPE mycsv.csv >> mycsvheader.csv"? If that works, you may write a batch file to simplify the process.

Thanks for replying. The problem with CSV is CSV expects all columns to be filled OR separated with the delineator. Whatever row has the most columns ends up setting the standard for total delineators per line.

The file I am working with requires:

A1 (CR) (LF)
A2 (CR) (LF)
A3 (CR) (LF)
A4 (TAB) B4 (TAB) C4 (TAB) D4 (TAB) E4 (TAB) F4 (TAB) G4 (TAB) H4 (TAB) I4 (CR) (LF)
A5 (TAB) B5 (TAB) C5 (TAB) D5 (TAB) E5 (TAB) F5 (TAB) G5 (TAB) H5 (TAB) I5 (CR) (LF)

If saved as CSV, it ends up becoming:

A1 (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (CR) (LF)
A2 (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (CR) (LF)
A3 (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (TAB) (CR) (LF)
A4 (TAB) B4 (TAB) C4 (TAB) D4 (TAB) E4 (TAB) F4 (TAB) G4 (TAB) H4 (TAB) I4 (CR) (LF)
A5 (TAB) B5 (TAB) C5 (TAB) D5 (TAB) E5 (TAB) F5 (TAB) G5 (TAB) H5 (TAB) I5 (CR) (LF)

I did end up putting together another solution that doesn't involve the act of exporting to CSV and/or editing the HEX.

I will be attaching that in a follow up post.

 

[InoSiX]

Ok, below is a solution to the problem I was having. It's rough and I have not cleaned it up or supplied much pseudo code.

Function ExportFile()
Dim myString As Variant, rng As Range, cellValue As Variant, i As Integer, j As Integer

Dim init_filename As String
    init_filename = Names("InitFilename").RefersToRange(1, 1)                                               'Obtain default file name saved in a cell on a title page
    
Dim export_lrow As Integer
    export_lrow = Worksheets("FILE_EXPORT").Cells(Rows.Count, 1).End(xlUp).row                                'Locate the last row in the export page we are working with
    
Dim fPath As String
    fPath = Application.ActiveWorkbook.Path                                                                 'define a default folder as the current folder the workbook was opened in

Dim outputFile As String
    outputFile = Application.GetSaveAsFilename(InitialFileName:=fPath & "\" & init_filename, _
                                                FileFilter:="My File Type (*.mft), *.mft*")    'Present user with SAVE AS prompt, using fPath and define file type
                                                
    Set rng = Worksheets("FILE_EXPORT").Range("A1:I" & export_lrow)                                           'using the last row, define the range we are working with
    
    Open outputFile For Output As #1                                                                        'open a file ready to accept input

For i = 1 To rng.Rows.Count                                                                                 'this gets executed from left to right, top to bottom (in that order)
    For j = 1 To rng.Columns.Count                                                                          'All cells in first row, followed by the next row (rinse and repeat until last row)
    cellValue = rng.Cells(i, j).Value                                                                       'obtain the value at the current cell in the loop
        If IsEmpty(rng.Cells(i, j).Value) Then                                                              'we are skipping any rows that have no content
            'Do Nothing
        ElseIf IsEmpty(rng.Cells(i, j + 1).Value) Then                                                      'After determining current cell isn't empty, check next cell. If empty, add a carriage return and line feed
                myString = myString & cellValue & vbCrLf                                                    'Begin constructing the string that ultimately feeds the file
        Else                                                                                                'If not the last input in the row..

                myString = myString & cellValue & vbTab                                                     '..add cellValue and a tab (the delineator)

        End If
    Next j 'Cycle column
Next i 'Cycle row

Print #1, myString 'We are using the Print command rather than Write command. The Write command adds a " to the begining and a trailing "
            'myString is the complete, constructed item we push into the file
Close #1 'Close the file we opened earlier
End Function