How do i remove quotations in text file export. (1 Viewer)

camiramzi

Member
Local time
Today, 15:53
Joined
Oct 30, 2022
Messages
35
Hi i'm using this vba code to convert excel sheets to txt file

Code:
        Sub ExportSheetsToText()
            Dim xWs As Worksheet
            Dim xTextFile As String
               For Each xWs In Application.ActiveWorkbook.Worksheets
                 xWs.Copy
                 xTextFile = CurDir & "\" & xWs.Name & ".txt"
                 Application.ActiveWorkbook.SaveAs Filename:=xTextFile, FileFormat:=xlText
                 Application.ActiveWorkbook.Saved = True
                 Application.ActiveWorkbook.Close
              Next
          End Sub

i have a problem with the VBA for export to text file, as the VBA run the txt file got generated, but the inside data is not exactly like the excel data, for example my data include character quotation mark, and the exported data became double quotation mark, plus the beginning and the ending of the text file there are quotation mark,
quotation.PNG

i tried different VBA code but i ran to the same problem any help thank you.
 

Isaac

Lifelong Learner
Local time
Today, 06:53
Joined
Mar 14, 2017
Messages
8,687
Hi i'm using this vba code to convert excel sheets to txt file

Code:
        Sub ExportSheetsToText()
            Dim xWs As Worksheet
            Dim xTextFile As String
               For Each xWs In Application.ActiveWorkbook.Worksheets
                 xWs.Copy
                 xTextFile = CurDir & "\" & xWs.Name & ".txt"
                 Application.ActiveWorkbook.SaveAs Filename:=xTextFile, FileFormat:=xlText
                 Application.ActiveWorkbook.Saved = True
                 Application.ActiveWorkbook.Close
              Next
          End Sub

i have a problem with the VBA for export to text file, as the VBA run the txt file got generated, but the inside data is not exactly like the excel data, for example my data include character quotation mark, and the exported data became double quotation mark, plus the beginning and the ending of the text file there are quotation mark,
View attachment 112386
i tried different VBA code but i ran to the same problem any help thank you.
I think possibly if you save it as CSV you will accomplish what you are after.
 

camiramzi

Member
Local time
Today, 15:53
Joined
Oct 30, 2022
Messages
35
I think possibly if you save it as CSV you will accomplish what you are after.
I get the same problem in CSV ,
is there a VBA code to prevent quotation from appearing in the beginning of every paragraph.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:53
Joined
May 7, 2009
Messages
18,987
i think that is normal for your excel with delimiter as ".
try exporting the text file to another db.
does it remove the extra quote. i think so.
 

camiramzi

Member
Local time
Today, 15:53
Joined
Oct 30, 2022
Messages
35
i think that is normal for your excel with delimiter as ".
try exporting the text file to another db.
does it remove the extra quote. i think so.
i don't understand what do you mean by exporting the text file to another db.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:53
Joined
May 7, 2009
Messages
18,987
here is a demo. run your export routine, then run the import_csv and import the txt file (will be imported on sheet2)
 

Attachments

  • testExportText.zip
    18.4 KB · Views: 35

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:53
Joined
May 7, 2009
Messages
18,987
When i remove the commas inside the paragraphs the quotation disappear in the export text
then, that won't be the Original text..
because you imported it with quotes, then you expect it to be imported back with quotes also.
 

Isaac

Lifelong Learner
Local time
Today, 06:53
Joined
Mar 14, 2017
Messages
8,687
i found this answer in quora
When i remove the commas inside the paragraphs the quotation disappear in the export text
That's not magic - it's because of CSV ANSI specs, which hence my suggestion.

Anytime you have commas appearing organically in the actual text values, you must quote the columns to be CSV-compliant.

If you don't, if you forcibly remove those quotes from the text somehow (easy, but why would you?) - then you'll have no way to denote columns.

The quotes are your friend :)
 

Users who are viewing this thread

Top Bottom