Saving a Excel File into .txt format without quotes

Question

I have a excel sheet which has data in column A.There are many special characters in the cells.When I save the sheet in .txt format I get inverted commas at the start of each line. I tried both manually and by macro saving the file in .txt format.Why is it so? How to remove them? I am not able to remove the quotes. Attaching a pic enter image description here

I assumed you must be saving the worksheet with comma separators. Perhaps whoever voted to close the question assumed the same. However, having saved worksheets with tab separators, I can confirm that Excel places unnecessary quotes around values that contain commas. My only solution is to construct the lines with VBA which would be a slow approach. — Tony Dallimore, Jul 16 '12 at 09:44
May I save folk some time by saying Excel puts a cell in quotes if the cell has commas (and also if the cell already has quotes). The ticked solution is the only one that works because the popular solution at end, suggesting use file format xlTextPrinter, moves everything around to present it as printable, which includes truncating columns (ie: losing text), moving off page text to end, etc. — www-0av-Com, Jan 24 '18 at 18:14

score 19 · Answer 1 · edited Nov 13 '13 at 16:44

19

I see this question is already answered, but wanted to offer an alternative in case someone else finds this later.

Depending on the required delimiter, it is possible to do this without writing any code. The original question does not give details on the desired output type but here is an alternative:

PRN File Type

The easiest option is to save the file as a "Formatted Text (Space Delimited)" type. The VBA code line would look similar to this:

ActiveWorkbook.SaveAs FileName:=myFileName, FileFormat:=xlTextPrinter, CreateBackup:=False

In Excel 2007, this will annoyingly put a .prn file extension on the end of the filename, but it can be changed to .txt by renaming manually.

In Excel 2010, you can specify any file extension you want in the Save As dialog.

One important thing to note: the number of delimiters used in the text file is related to the width of the Excel column.

Observe:

Excel Screenshot

Becomes:

Text Screenshot

edited Nov 13 '13 at 16:44

TravelinGuy

323
3
6

answered Jul 26 '12 at 02:36

nicholas

2,969
20
39

1

Thanks for this. The column width had thrown me off the scent when I was trying to figure it out on my own, but your thorough notes allowed me to salvage the effort! – Jason R. Escamilla Oct 18 '15 at 17:10
In Excel 2010, saving to .html, I was limited to approx 255 characters per line before getting unpredictable results. – Jason R. Escamilla Dec 11 '15 at 14:27
4

Works BUT if your cells span wider than the width of a printout they'll get moved to the bottom of the page and disrupt your data. – TadLewis Apr 06 '18 at 16:49

score 12 · Accepted Answer · edited Jan 07 '22 at 04:58

12

This code does what you want.

LOGIC

Save the File as a TAB delimited File in the user temp directory
Read the text file in 1 go
Replace "" with blanks and write to the new file at the same time.

CODE

Private Declare Function GetTempPath Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Private Const MAX_PATH As Long = 260

'~~> Change this where and how you want to save the file
Const FlName = "C:\Users\Siddharth Rout\Desktop\MyWorkbook.txt"

Sub Sample()
    Dim tmpFile As String
    Dim MyData As String, strData() As String
    Dim entireline As String
    Dim filesize As Integer
    
    '~~> Create a Temp File
    tmpFile = TempPath & Format(Now, "ddmmyyyyhhmmss") & ".txt"
    
    ActiveWorkbook.SaveAs Filename:=tmpFile _
    , FileFormat:=xlText, CreateBackup:=False
    
    '~~> Read the entire file in 1 Go!
    Open tmpFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
    strData() = Split(MyData, vbCrLf)
    
    '~~> Get a free file handle
    filesize = FreeFile()
  
    '~~> Open your file
    Open FlName For Output As #filesize
    
    For i = LBound(strData) To UBound(strData)
        entireline = Replace(strData(i), """", "")
        '~~> Export Text
        Print #filesize, entireline
    Next i
    
    Close #filesize
    
    MsgBox "Done"
End Sub

Function TempPath() As String
    TempPath = String$(MAX_PATH, Chr$(0))
    GetTempPath MAX_PATH, TempPath
    TempPath = Replace(TempPath, Chr$(0), "")
End Function

SNAPSHOTS

Actual Workbook

enter image description here

After Saving

enter image description here

edited Jan 07 '22 at 04:58

Nimantha

6,405
6
28
69

answered Jul 16 '12 at 12:31

Siddharth Rout

147,039
17
206
250

@ankitagrawal: please see additional answer, below. if spaces are adequate delimiters, you can get by with built-in functionality of Excel – nicholas Jul 26 '12 at 02:37
the above code leaves the temporary file behind, which could be cleaned up with something like this `With New FileSystemObject If .FileExists(yourFilePath) Then .DeleteFile yourFilepath End If End With` – ekkis May 13 '15 at 23:55
You can also delete that file using `Kill tmpFile` with OERN @ekkis – Siddharth Rout May 14 '15 at 06:32
1

@ekkis: OERN: On Error Resume Next :) – Siddharth Rout May 15 '15 at 16:52
One question about this code. Notice the two blank lines at the end of your text file? One is from the Upper and lower bound difference (0 to number of rows in Excel used, instead of 1 to number of rows in Excel used). The other is most likely that when you write to the new file the line ends with a vbCrLf. How can I modify your code to remove the last vbCrLf? I changed to this to remove one of the blank lines (For i = LBound(strData) To UBound(strData) -1) – mooseman Aug 12 '15 at 19:48
@mooseman: See [This](http://blogs.technet.com/b/heyscriptingguy/archive/2006/09/11/how-can-i-delete-just-the-last-line-of-a-text-file.aspx) there are other examples as well. Just type `vb6 remove last blank line from text file` in Google – Siddharth Rout Aug 13 '15 at 05:30

Eric Gross · Answer 3 · 2015-01-29T17:59:06.263

9

Ummm, How about this.

Copy your cells.
Open Notepad.
Paste.

Look no quotes, no inverted commas, and retains special characters, which is what the OP asked for. Its also delineated by carriage returns, same as the attached pict which the OP didn't mention as a bad thing (or a good thing).

Not really sure why a simple answer, that delivers the desired results, gets me a negative mark.

edited Jan 29 '15 at 17:59

answered Mar 19 '14 at 19:57

Eric Gross

156
1
7

4

it would be better to suggest a non-manual solution. manual solutions are costly and not really solutions – ekkis Mar 28 '15 at 01:24
3

This doesn't work for me. It's still quoted in Notepad. – shazbot Jul 07 '15 at 13:37
I do not get why this answer gets negative votes. It is a simple solution that fits my needs. – Nikos Tsokos Jun 07 '16 at 11:19
This is no more manual than saving an XLS or XLSX file as a .txt file from Excel. It's a simple column selection, copy, and paste to a new .txt file in Notepad. Takes a lot less time than the so-called automated solutions. This is how we create them when we need them occasionally for our ADP upload interface.It's dirt simple and it works. It's also the accepted solution in other forums, such as here: http://superuser.com/questions/206060/saving-a-tab-separated-file-in-excel-without-quotes – Jim Oct 06 '16 at 21:45
1

I upvoted it, this answer was super simple and worked fine. – Brandon Spilove Mar 06 '17 at 13:16
It doesn't deliver the desired results - I still get quotes in notepad. – gatinueta May 17 '18 at 09:08

score 2 · Answer 4 · answered Mar 11 '14 at 22:20

I just spent the better part of an afternoon on this

There are two common ways of writing to a file, the first being a direct file access "write" statement. This adds the quotes.

The second is the "ActiveWorkbook.SaveAs" or "ActiveWorksheet.SaveAs" which both have the really bad side effect of changing the filename of the active workbook.

The solution here is a hybrid of a few solutions I found online. It basically does this: 1) Copy selected cells to a new worksheet 2) Iterate through each cell one at a time and "print" it to the open file 3) Delete the temporary worksheet.

The function works on the selected cells and takes in a string for a filename or prompts for a filename.

Function SaveFile(myFolder As String) As String
tempSheetName = "fileWrite_temp"
SaveFile = "False"

Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long

Set myRange = Selection
'myRange.Select
Selection.Copy

'Ask user for folder to save text file to.
If myFolder = "prompt" Then
    myFolder = Application.GetSaveAsFilename(fileFilter:="XML Files (*.xml), *.xml, All Files (*), *")
End If
If myFolder = "False" Then
    End
End If

Open myFolder For Output As #2

'This temporarily adds a sheet named "Test."
Sheets.Add.Name = tempSheetName
Sheets(tempSheetName).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

LastCol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column
LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For i = 1 To LastRow
    For j = 1 To LastCol
        CellData = CellData + Trim(ActiveCell(i, j).Value) + "   "
    Next j

    Print #2, CellData; " "
    CellData = ""

Next i

Close #2

'Remove temporary sheet.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'Indicate save action.
MsgBox "Text File Saved to: " & vbNewLine & myFolder
SaveFile = myFolder

End Function

score 2 · Answer 5 · answered Oct 01 '18 at 16:20

The answer from this question provided the answer to this question much more simply.

Write is a special statement designed to generate machine-readable files that are later consumed with Input.

Use Print to avoid any fiddling with data.

Thank you user GSerg

score 1 · Answer 6 · answered Feb 25 '15 at 10:36

I have the same problem: I have to make a specific .txt file for bank payments out of an excel file. The .txt file must not be delimeted by any character, because the standard requires a certain number of commas after each mandatory field. The easiest way of doing it is to copy the contect of the excel file and paste it in notepad.

score 1 · Answer 7 · answered Mar 11 '20 at 06:39

I was using Write #1 "Print my Line" instead I tried Print #1, "Print my Line" and it give me all the data without default Quote(")

Dim strFile_Path As String
strFile_Path = ThisWorkbook.Path & "\" & "XXXX" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".txt"
Open strFile_Path For Output As #1

Dim selectedFeature As String

For counter = 7 To maxNumberOfColumn

        selectedFeature = "X"
        Print #1, selectedFeature
        'Write #1, selectedFeature

Next counter
Close #1

Thanks. Print instead of write works. No need for all the other longer codes here. — Blaisem, Jun 28 '20 at 15:18

score 0 · Answer 8 · answered Jun 06 '18 at 11:19

PRN solution works only for simple data in the cells, for me it cuts only first 6 signs from 200 characters cell.

These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are +1

51 = xlOpenXMLWorkbook (without macro's in 2007-2016, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2016, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2016, xls)

From XlFileFormat FileFormat Property

Keep in mind others FileFormatNumbers for SaveAs method:

FileExtStr = ".csv": FileFormatNum = 6
FileExtStr = ".txt": FileFormatNum = -4158
FileExtStr = ".prn": FileFormatNum = 36

Saving a Excel File into .txt format without quotes

8 Answers8

PRN File Type

Linked

Related