How to open a delimited text file in a single column

spiralrain

New Member
Joined
Mar 24, 2018
Messages
23
Hello, I wrote a macro a few years ago in Excel 2010 that opens a series of text files, imports the data, and manipulates it as needed. Our computers are being updated and we are getting Excel 2013. So I'm trying to update the code for my macro in preperation for this. The original line of code (that i'm now having trouble with) is:

Workbooks.OpenText ("C:\Users\Me\Desktop\Testdoc.txt"), DataType:=xlDelimited, Tab:=True

This line of code (in excel 2010) would open the text file in an excel document and all of the lines would be in a single column (which is what I want).

However, using excel 2013 and the same line of code, the text file is opened and often puts the lines in several columns (and once, in a single row with each line in a different column).

The lines of information within the text document have strings, numbers and dates. Each line is seperated by a tab.

I have been googling the problem, but have been unable to find an answer. Any help you can provide will be appreciated.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
Usually when that happens it is because the character used for the column delimiter is appearing in text (where it is not intended to be a column delimiter). Try experimenting with explicitly specifying the text qualifier and consecutive delimiter instead of relying on the defaults. It's possible there were some changes in how 2013 sets those defaults.


Code:
Workbooks.OpenText ("C:\Users\Me\Desktop\Testdoc.txt"), DataType:=xlDelimited, Tab:=True, TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False
Workbooks.OpenText ("C:\Users\Me\Desktop\Testdoc.txt"), DataType:=xlDelimited, Tab:=True, TextQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=True


Workbooks.OpenText ("C:\Users\Me\Desktop\Testdoc.txt"), DataType:=xlDelimited, Tab:=True, TextQualifier:=xlTextQualifierNone
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
Hello, I wrote a macro a few years ago in Excel 2010 that opens a series of text files, imports the data, and manipulates it as needed. Our computers are being updated and we are getting Excel 2013. So I'm trying to update the code for my macro in preperation for this. The original line of code (that i'm now having trouble with) is:

Workbooks.OpenText ("C:\Users\Me\Desktop\Testdoc.txt"), DataType:=xlDelimited, Tab:=True
Try turning all possible delimiters off and see if that works for you...
Code:
[table="width: 500"]
[tr]
	[td]Workbooks.OpenText "C:\Users\Me\Desktop\Testdoc.txt", , , xlDelimited, , , False, False, False, False, False[/td]
[/tr]
[/table]
 
Last edited:

spiralrain

New Member
Joined
Mar 24, 2018
Messages
23
@rlv01 - I tried all three of the examples you gave, but each time the text still appears in multiple columns. If my understanding of the textqualifier (what constitutes text) and the consecutivedelimiter (whether consecutive TABs will be each be seen as a new line) is correct, then their settings shouldn't matter for my needs. The text files don't have quotes within them, and tabs are only used between each line of information. If I'm misunderstanding either of these, then I appologize.
@Rick - I tried your suggestion, and while it did put everything into a single column like I wanted, it sadly also dumped everything into a single row (so everything was in cell A1 smushed up together).
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771

ADVERTISEMENT

The lines of information within the text document have strings, numbers and dates. Each line is seperated by a tab.

Maybe I'm missing something. Do you mean each LINE is separated by a tab or each COLUMN is separated by a tab? A tab character is not a newline character as far as the Workbooks.OpenText method is concerned. Windows and/or UTF-8 text files use CRLF (carriage return & line feed) characters to indicate a line break, while Unix text files just use LF. Workbooks.OpenText Method is only going to care about CRLF when it comes to whether to treat a group of characters from the text file as a line. It will not see a TAB as a line separation. The entire function of the Workbooks.OpenText Method is to open and parse text file data into columns


This statment in your code
Code:
Workbooks.OpenText ("C:\Users\Me\Desktop\Testdoc.txt"), DataType:=xlDelimited, Tab:=True

should open the text file and then for each (CRLF) line of data in that file, chop up that line and put the pieces into different columns

Code:
DataType:=xlDelimited

Tells it to chop up the data based on a delimiting character

Code:
Tab:=True

Defines the delimiting character as the Tab (ascii code 9)
 
Last edited:

spiralrain

New Member
Joined
Mar 24, 2018
Messages
23
Either I'm missing something too (which is completely possible, I'm not exactly great at coding) or I'm not explaining this very well.

The text files are being created by a machine. On the Machine, a line of text is shown, then a return<enter> followed by the next line. Easily readable. However, when it dumps the information at the end of the day, it does so in a text file, instead of a return <enter> between each line, there is a Tab. Opening the text file in Notepad, it reads as one really really long run-on sentence.

Opening that text file using the afore mentioned line of code, used to create an excel sheet with each line (all of the information between each Tab) on a different row, but all within a single column. Which is what I'm trying to get to happen using Excel 2013.

I hope I explained that better. Please let me know if I can provide any more info.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771

ADVERTISEMENT

Ok, let's say this text file has 500 lines of data, each ending with a tab char (so that it looks like a run-on sentence if you open it in notepad). If I understand what you are saying then when your macro uses the statement

Code:
Workbooks.OpenText ("C:\Users\Me\Desktop\Testdoc.txt"), DataType:=xlDelimited, Tab:=True


to open the file, then if you run the macro in XL2010 the result is a worksheet with one column and 500 rows?

If you run the macro in XL2013 the result is one row and 500 columns?

(If the latter, then that's kind of what I'd expect from Workbooks.OpenText, even under XL2010)
About how many lines in Testdoc.txt?
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
I built my own tab delimited test file and ran it under XL2010. The results are as expected, i.e. it puts all the data in one row.

File_Parse2010_Example.jpg
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
771
It seems to me that the OpenText method is behaving as it should under Excel 2013. Why not just add some code to your macro to transpose the data after opening the file? A simple example:

Code:
Sub TestOpen()
    Dim R As Range
    Dim fname As String


    fname = "C:\Users\Me\Desktop\Testdoc.txt"
    Workbooks.OpenText (fname), DataType:=xlDelimited, Tab:=True


    With ActiveSheet
        Set R = .UsedRange    'assumes that when the file is opened, all data will be in row 1
        R.Copy
        R.Range("A1").Offset(1).PasteSpecial Paste:=xlPasteValues, Transpose:=True
        R.EntireRow.Delete
    End With
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,122,352
Messages
5,595,657
Members
414,006
Latest member
Davefromlondon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top