Is it possible to Open a CSv/Txt file with comma and semicolons (semicolon as delimiter) directly in Excel & saving it back without distortion?

Question

This is with reference to my answer to SO post macro separates .csv by comma, despite separator set to semicolon and subsequent post Saving .txt as .csv cancels all changes made by macro in the file. How to prevent it? by @Drzemlik. What at the start was thought to be simple solution to some already negative voted post, during the process of preparing answer it was found that the problem may be worth a bounty?

In my trial, I find while saving semicolon delimited txt/csv files from excel it may introduce some double quotes in the saved file (depending on position of comma, spaces, double quote and semicolon in a line). May refer links Saving a Excel File into .txt format without quotes and link1 and link2.

However, I am not at all satisfied with my workaround approach of opening the Csv/Txt file with Open statement in I/O mode and renaming it. Still I believe I missed out something and there must be Simple and Direct approach to open and save the file in excel only.

1. Text file involved should consist of comma, spaces, double quote and semicolons, while semicolon is to be treated as delimiter.

2. File is to be opened directly in excel using ‘OpenTextorTextToColumns` or likewise, perform some simple truncate operation on a column (say col 2) and saving the same directly from excel.

3. Task may be performed as simple as possible. Most preferably with use of some parameters/ tweaks of OpenTextand/or saveAs that I missed out. Process should be free of Find replace type of manipulation of file content and of introduced double quotes.

4. Finally most challenging is to open the file with .csv extension and directly saving it with .csv extension will be a Kudos.

I am not reproducing any codes (it all there in the links provided), But providing a sample file text for easy trial.

Ln,1  "AND" Col,1;  Ln,1  "AND" Col,2;  Ln,1  "AND" Col,3;  Ln,1  "AND" Col,4;  Ln,1  "AND" Col,5;  Ln,1  "AND" Col,6;  Ln,1  "AND" Col,7;  Ln,1  "AND" Col,8;
Ln,2  "AND" Col,1;  Ln,2  "AND" Col,2;  Ln,2  "AND" Col,3;  Ln,2  "AND" Col,4;  Ln,2  "AND" Col,5;  Ln,2  "AND" Col,6;  Ln,2  "AND" Col,7;  Ln,2  "AND" Col,8;
Ln,3  "AND" Col,1;  Ln,3  "AND" Col,2;  Ln,3  "AND" Col,3;  Ln,3  "AND" Col,4;  Ln,3  "AND" Col,5;  Ln,3  "AND" Col,6;  Ln,3  "AND" Col,7;  Ln,3  "AND" Col,8;
Ln,4  "AND" Col,1;  Ln,4  "AND" Col,2;  Ln,4  "AND" Col,3;  Ln,4  "AND" Col,4;  Ln,4  "AND" Col,5;  Ln,4  "AND" Col,6;  Ln,4  "AND" Col,7;  Ln,4  "AND" Col,8;
Ln,5  "AND" Col,1;  Ln,5  "AND" Col,2;  Ln,5  "AND" Col,3;  Ln,5  "AND" Col,4;  Ln,5  "AND" Col,5;  Ln,5  "AND" Col,6;  Ln,5  "AND" Col,7;  Ln,5  "AND" Col,8;
Ln,6  "AND" Col,1;  Ln,6  "AND" Col,2;  Ln,6  "AND" Col,3;  Ln,6  "AND" Col,4;  Ln,6  "AND" Col,5;  Ln,6  "AND" Col,6;  Ln,6  "AND" Col,7;  Ln,6  "AND" Col,8;
Ln,7  "AND" Col,1;  Ln,7  "AND" Col,2;  Ln,7  "AND" Col,3;  Ln,7  "AND" Col,4;  Ln,7  "AND" Col,5;  Ln,7  "AND" Col,6;  Ln,7  "AND" Col,7;  Ln,7  "AND" Col,8;
Ln,8  "AND" Col,1;  Ln,8  "AND" Col,2;  Ln,8  "AND" Col,3;  Ln,8  "AND" Col,4;  Ln,8  "AND" Col,5;  Ln,8  "AND" Col,6;  Ln,8  "AND" Col,7;  Ln,8  "AND" Col,8;
Ln,9  "AND" Col,1;  Ln,9  "AND" Col,2;  Ln,9  "AND" Col,3;  Ln,9  "AND" Col,4;  Ln,9  "AND" Col,5;  Ln,9  "AND" Col,6;  Ln,9  "AND" Col,7;  Ln,9  "AND" Col,8;
Ln,10  "AND" Col,1; Ln,10  "AND" Col,2; Ln,10  "AND" Col,3; Ln,10  "AND" Col,4; Ln,10  "AND" Col,5; Ln,10  "AND" Col,6; Ln,10  "AND" Col,7; Ln,10  "AND" Col,8;
Ln,11  "AND" Col,1; Ln,11  "AND" Col,2; Ln,11  "AND" Col,3; Ln,11  "AND" Col,4; Ln,11  "AND" Col,5; Ln,11  "AND" Col,6; Ln,11  "AND" Col,7; Ln,11  "AND" Col,8;
Ln,12  "AND" Col,1; Ln,12  "AND" Col,2; Ln,12  "AND" Col,3; Ln,12  "AND" Col,4; Ln,12  "AND" Col,5; Ln,12  "AND" Col,6; Ln,12  "AND" Col,7; Ln,12  "AND" Col,8;
Ln,13  "AND" Col,1; Ln,13  "AND" Col,2; Ln,13  "AND" Col,3; Ln,13  "AND" Col,4; Ln,13  "AND" Col,5; Ln,13  "AND" Col,6; Ln,13  "AND" Col,7; Ln,13  "AND" Col,8;
Ln,14  "AND" Col,1; Ln,14  "AND" Col,2; Ln,14  "AND" Col,3; Ln,14  "AND" Col,4; Ln,14  "AND" Col,5; Ln,14  "AND" Col,6; Ln,14  "AND" Col,7; Ln,14  "AND" Col,8;

Answer 1

This is my shortest approach (neither with Find/Replace nor with double quotes), tested with your mean CSV example on a blank new ActiveSheet.
The second part might not be your intended approach, nevertheless rather short:

Public Sub DealingMeanCSVexample()
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & _
       Application.DefaultFilePath & "\Source.csv", Destination:=Range("$A$1"))
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileSemicolonDelimiter = True
        .Refresh
        .Delete
    End With

    Dim r As Long, s As String
    For r = 1 To ActiveSheet.UsedRange.Rows.Count
        s = s & WorksheetFunction.TextJoin(";", True, ActiveSheet.UsedRange.Rows(r)) & ";" & vbCrLf
    Next r
    s = Left(s, Len(s) - 2)

    Dim handle As Long: handle = FreeFile
    Open Application.DefaultFilePath & "\Dest.csv" For Binary As #handle
    Put #handle, , s
    Close #handle
End Sub

---

As TextJoin is part of newer Excel versions, the loop may be exchanged by this:

Dim r as long, Dim c As Long
For r = 1 To ActiveSheet.UsedRange.Rows.Count
    For c = 1 To ActiveSheet.UsedRange.Columns.Count
        s = s & ActiveSheet.Cells(r, c).Value & ";"
    Next c
    s = s & vbCrLf
Next r

Answer 2

Let's start by defining the

Requirements:
1. To open a text file directly in excel using the semicolon characters as delimiter.
2. Performs a truncation for all values in column 2.
3. Saves the resulting data with a csv extension, while maintain the original layout (e.g. the cells data delimited by semicolons instead of commas, their values not wrapped within double quotes, and any existing double quotes in the cells must not be duplicated), instead of the standard csv format generated by excel.

This approach adds a new workbook, then imports the CSV file using a QueryTable connection, performs the truncation of values in second column and saves the file using the xlTextPrinter format, while naming the file with a csv extension, then closes the workbook used to modify the original file. Afterward, opens the results file in notepad to verify the output layout.

Edit: This is the “Closest Workaround approach” as considered by Op.

Sub TEST()
Dim sFilenameSrc As String, sFilenameTrg As String
sFilenameSrc = "D:\@D_Trash\@Csv_Source.csv"    'change as required
sFilenameTrg = "D:\@D_Trash\@Csv_Target.csv"    'change as required

    Call Open_Csv_As_Semicolon_Delimited_Then_Save_As_Csv(sFilenameSrc, sFilenameTrg)

    Rem Open Target with Notepad
    Shell "notepad.exe " & sFilenameTrg, vbNormalFocus

    End Sub


Sub Open_Csv_As_Semicolon_Delimited_Then_Save_As_Csv(sFilenameSrc As String, sFilenameTrg As String)
Dim wb As Workbook
Dim rg As Range, aData As Variant
Dim aValue As Variant, lRow As Long

    Rem Add Workbook
    Set wb = Workbooks.Add(Template:="Workbook")

    Rem Import Csv File
    With wb.Worksheets(1)
        Rem Set qt = .QueryTables.Add(Connection:="TEXT;" & sFilenameSrc, Destination:=.Cells(1))
        With .QueryTables.Add(Connection:="TEXT;" & sFilenameSrc, Destination:=.Cells(1))
            .SaveData = True
            .TextFileParseType = xlDelimited
            .TextFileSemicolonDelimiter = True
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    End With: End With

    Rem Set Data Range
    Set rg = wb.Worksheets(1).UsedRange

    Rem Truncate 2nd Column
    aData = rg.Columns(2).Value2
    For lRow = 1 To UBound(aData)
        aValue = aData(lRow, 1)
        aValue = Left(aValue, InStrRev(aValue, Chr(34)))
        aData(lRow, 1) = aValue
    Next
    rg.Columns(2).Value2 = aData

    Rem Prepare Data for Save as Csv
    aData = rg.Value2
    rg.ClearContents
    For lRow = 1 To UBound(aData)
        aValue = WorksheetFunction.Index(aData, lRow, 0)
        aValue = Join(aValue, Chr(59)) & Chr(59)
        rg.Cells(lRow, 1).Value2 = aValue
    Next

    rem Save File with csv extension     
    Application.DisplayAlerts = False
    With wb
        .SaveAs Filename:=sFilenameTrg, FileFormat:=xlTextPrinter
        .Close
    End With
    Application.DisplayAlerts = True

    End Sub

Answer 3

If there is no need for a VBA/Macro based solution, is the below any different from what you're expecting,

1. Open a new workbook

2. In 'Data' Tab > In 'Get External Data' group > Click 'From Text' button.

   

3. Choose the desired input file.

4. In 'Text Import Wizard' dialog box,

   Step1: Select 'Delimited' and Click 'Next'

   Step2: Check only 'Semicolon'

   

   Step3: No changes required, Click 'Finish'

   Click 'OK'

You should be good with saving the resulting file in "csv" format.

Cheers!