How to open a delimited text file in a single column

[spiralrain]

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.

 

[rlv01]

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.

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]

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...

[table="width: 500"]
[tr]
	[td]Workbooks.OpenText "C:\Users\Me\Desktop\Testdoc.txt", , , xlDelimited, , , False, False, False, False, False[/td]
[/tr]
[/table]

 

[spiralrain]

@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]

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

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

DataType:=xlDelimited

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

Tab:=True

Defines the delimiting character as the Tab (ascii code 9)

 

[spiralrain]

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]

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

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?

 

[spiralrain]

if you run the macro in XL2010 the result is a worksheet with one column and 500 rows?

yes

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

yes

About how many lines in Testdoc.txt?

It can vary greatly, but most days are around 300 lines.

 

[rlv01]

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.

 

[rlv01]

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:

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