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.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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:
Upvote 0
@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).
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,261
Members
448,558
Latest member
aivin

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
Back
Top