Excel to TimeSlips import - Script to replace CRLF with CR then LF with CRLF

Question

Summary: Unless there is a way to 'Save as' from Excel to this format, I want to click on a script that modifies a text file by replacing all CRLF (Carriage Return Line Feeds) with CR and after that replaces all LF with CRLF.

Situation: I have an Excel file that I save as a Tab Delimited Text file [Text (Tab delimited) (*.txt)]. I then import that text file into TimeSlips. Example text in linked image to show line breaks.

For the import to correctly import the description data with line breaks, I need to first open the text file in Notepad++ and replace all the line breaks as mentioned above.

I must replace the CRLF with CR before replacing the LF because if I find/replace LF first, it will alter the CRLF to CRCRLF.

I'm a novice at scripts. I looked at some examples of this and couldn't really wrap my head around what I found.

Like:

@echo off

setlocal EnableDelayedExpansion

set LF=^
%Don't remove%
%these lines%
set "EOL=!LF!" & set "EOL2=!LF!"

for /F %%a in (test.txt) do (
   if %%a equ PROP-SUMMARY set "EOL=!LF!"
   set /P "=%%a!EOL!" < NUL
   set "EOL0=!EOL!" & set "EOL=!EOL2!" & set "EOL2=!EOL0!"
   if %%a equ PROP-VALUES set "EOL=,"
)

from: I want to replace carriage returns with commas in batch

Test Data: enter image description here

Answer 1

I created a PowerShell Script that works nicely.

$original_file ='C:\Users\ItsMeMario\Downloads\TESTING.txt'
$text = [IO.File]::ReadAllText($original_file) -replace "`r`n", "`r"
[IO.File]::WriteAllText($original_file, $text)
$text = [IO.File]::ReadAllText($original_file) -replace "`n", "`r`n"
[IO.File]::WriteAllText($original_file, $text)

And I'm now working on an Excel VBA macro. :/

Answer 2

Created an Excel VBA macro script to export the sheet and remove the line breaks.

I know the code could be consolidated a little bit, but that's beyond my current skill level. Any help would be welcome.

Sub TSexport()
'
' Macro1 Macro
'
'
        Dim sBuf As String
        Dim sTemp As String
        Dim iFileNum As Integer
        Dim sFileName As String

    ' Edit as needed
    sFileName = "C:\Users\ItsMeMario\Downloads\TSimport.txt"

    'This code exports the 'TSimport' sheet to a tab delimited txt file.
        Sheets("TSimport").Select
        Sheets("TSimport").Copy
        ActiveWorkbook.ServerViewableItems.DeleteAll
        ActiveWorkbook.ServerViewableItems.Add ActiveWorkbook.Sheets("TSimport")
        ActiveWorkbook.SaveAs Filename:=sFileName _
            , FileFormat:=xlText, CreateBackup:=False
        ActiveWindow.Close
        

    'This code replaces the line breaks CRLF (Chr(13) & Chr(10)) with CR (Chr(13)) in the exported txt file.
    iFileNum = FreeFile
    Open sFileName For Input As iFileNum

    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & sBuf & vbCrLf
    Loop
    Close iFileNum

    sTemp = Replace(sTemp, Chr(13) & Chr(10), Chr(13))

    iFileNum = FreeFile
    Open sFileName For Output As iFileNum
    Print #iFileNum, sTemp
    Close iFileNum


    'This code replaces the line breaks LF (Chr(10)) with CR (Chr(13) & Chr(10)) in the exported txt file.
    iFileNum = FreeFile
    Open sFileName For Input As iFileNum

    Do Until EOF(iFileNum)
        Line Input #iFileNum, sBuf
        sTemp = sTemp & sBuf & vbCrLf
    Loop
    Close iFileNum

    sTemp = Replace(sTemp, Chr(10), Chr(13) & Chr(10))

    iFileNum = FreeFile
    Open sFileName For Output As iFileNum
    Print #iFileNum, sTemp
    Close iFileNum

    'Add code to update rows from 'ready to export' to 'exported'.

End Sub