0

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;
Ahmed AU
  • 2,757
  • 2
  • 6
  • 15
  • 1
    I think you should just avoid putting it into a worksheet. Just read the file like you read a txt file (there are tutorials for that) into a variable, then process the data in the variable directly (or push it into an array) and write it back. This would be the most direct and reliable way. – Pᴇʜ Feb 15 '19 at 08:21
  • 3
    CSV and Excel -- now you have far more than two problems. (With apologies to jwz). I'm unclear why you believe that you have to be missing something, though. It seems obvious to me that Excel would feel free to apply the escaping it wants, whenever it wants, as long as it can unambiguously read back the file itself. Most applications are quite selfish, and Excel isn't exceptional in this regard; fine-grained control over the output so that other applications like it better is usually not in the cards. Developers are naturally lazy. (Current author included.) – Jeroen Mostert Feb 15 '19 at 08:23
  • @PEH and Jeroen Mostert, thanks a lot for taking interest in the subject and backup the method adopted by me from experts of your stature. I have a bad habit not be able to move away from any problem till I find it is logically impossible. Also with hope some new features may be added in latest excel versions. Since my goggling could not lead me to something concrete, I posted the question with point no 5 to get it confirmed from some experts in the area instead of living in a fool’s paradise. – Ahmed AU Feb 15 '19 at 10:02
  • 1
    You're almost certainly going to come up short on point 5 -- MS occasionally documents quirky behavior like this in detail, especially when many people run into it as a problem ([Excel's use of floating-point is a good example](https://support.microsoft.com/help/78113/)), but for the most part they're content to leave behavior that doesn't need to be guaranteed unspecified. The exact way Excel chooses to escape values in CSV is one of those, I'm afraid. – Jeroen Mostert Feb 15 '19 at 19:52
  • @AhmedAU Actually your point 5 is asking for a off-site resource which is off-topic to ask here on Stack Overflow according to [ask]: *"Questions asking us to recommend or find a book, tool, software library, tutorial or other **off-site resource** are off-topic for Stack Overflow"* – Pᴇʜ Feb 18 '19 at 07:13
  • @PEH. Thanks. I am deleting the point 5 to adhere to SO guidelines – Ahmed AU Feb 18 '19 at 11:07
  • 2
    The answer is: NO! You can't open, manipulate and save such of `*.csv` file directly in Excel (using standard methods). You have to create custom `CsvReader` & `CsvWriter` class. – Maciej Los Feb 27 '19 at 14:58
  • Thanks @Maciej Los for taking interest in the subject and adding another "NO". – Ahmed AU Feb 28 '19 at 01:16
  • I would use *Scripting.FileSystemObject* with your requirements. – PatricK Feb 28 '19 at 03:41
  • @AhmedAU, as i mentioned, a "NO" is for standard methods. The only way to work-around it is to write custom classes which will handle such of requirements. – Maciej Los Feb 28 '19 at 07:12

3 Answers3

0

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
Asger
  • 3,822
  • 3
  • 12
  • 37
  • Your line of thinking very much matches with mine. However I already tried (multiple) simlar approaches. But the stuck in saving part. Whatever is the output if saved directly with excel is getting distored with double quotes. It also made me understand some year old probem with sql string misbehaving when taken from excel cell vs directly in vba. But thanks for textjoin, i used native appraoch. – Ahmed AU Mar 01 '19 at 00:48
  • @AhmedAU Textjoin shortens the code by one For/Next loop only, but the result is the same. In both cases your CSV is read into the sheet correctly, and rewritten directly as CSV and in the same format. So my answer fulfils all 4 of your conditions, doesn't it? – Asger Mar 01 '19 at 10:11
  • Asger as I mentioned in title of the post and in point 2, I am looking for if any tweak is available (that i may Missed) for **opening the file directly in excel and saving it back directly from excel only**. As matter of fact i am hoping for a confirmation with substantial backup that it is not possible The multiple trails I undertaken just to stretch myself to the closest alternative in the direction. . May go through PEH's & Jeroen Mostert comments in this regard. The workaround solution already given in post2 of Drzemlik. – Ahmed AU Mar 01 '19 at 11:06
  • I understand. At least reading by Querytable and writing by one-string-put are further additions to all those attempts. – Asger Mar 01 '19 at 11:25
0

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
EEM
  • 6,601
  • 2
  • 18
  • 33
  • Congrat for the **Closest Workaround approach** in the direction based on @nicholas answer of the post already referred in OP in conjunction of manipulating the contents of a row to a single cell. Since I failed in my wording in OP to explain the purpose of the post is have 1. Simple and Direct approach 2. Some parameters/ tweaks of `OpenText` and/or `saveAs`( I may missed) 3. to avoid manipulation of contents and workarounds by words “Find replace type” and “preferably” in OP. Workaround with array’s etc are not desired are evident from of the PEH , Jeroen Mostert and Asger Chats. – Ahmed AU Mar 05 '19 at 00:48
  • As I want to accept answer for the efforts and interest in the subject and to keep SO spirit high and same time don’t want to give any wrong signal for future references regarding title of OP that it is possible and mislead future searches when there are numbers of simplest approach is available for the objective with array ‘s and FSO etc. I request you to Edit your post with mentioning it is the “Closest Workaround approach” – Ahmed AU Mar 05 '19 at 00:49
-2

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.

    GetExternalData-FromText

  3. Choose the desired input file.

  4. In 'Text Import Wizard' dialog box,

    Step1: Select 'Delimited' and Click 'Next'

    Step2: Check only 'Semicolon'

    semicolondelimit

    Step3: No changes required, Click 'Finish'

    Click 'OK'

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

Cheers!

Karthick Ganesan
  • 375
  • 4
  • 11