VBA for Text-to-Columns

Topic

New Reply

I run a report from a legacy system that only displays results to a txt file. I can copy-paste the results into one column of an Excel spreadsheet and then run the Text-to-Columns feature to parse out the results. But because it’s from a print file the report’s page headings appear and cause the column-breaks in text-to-columns to require adjustment.

I’ve worked out the column break positions across the ruler. How can I get these values into a macro?

The values for the column breaks are: 33, 40, 49, 60, 69, 76, 80, 97, 114, 116, 133, 150, 167, 184

Reply | Quote

Replies

Can you import the text file and use space as the field delimiter? Then you won’t need to worry about exact spacing.
Can you post a sample of the data?

cheers, Paul

Reply | Quote

Paul’s way is ideal but can you make the headers a permanent part of the worksheet then paste the copied data (minus the header) in the cell below the worksheet header and run Text-to-Columns?

Reply | Quote

Maudibe & Paul,
I tried something like that long ago, perhaps it was in Lotus. I used a sequence of dashes and pipes and parsed out the data that way. Looked like this: ——–|—-|————|— and I saved column titles along with the parse line. Formatting the text to a monospace font like Courier helped, too.
I even reverted to trying that here. I’m just not steady enough to double-click those dang vertical lines to remove without adding a new one here or there and then having to double-click that one, too. And trying to drag them is, well it’s a drag. Maybe I need a more precise mouse (if they even exist). But Rory’s solution works great.

Reply | Quote

You can specify the positions in the FieldInfo argument – e.g.:

Code:

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(32, 1), Array(39, 1), Array(48, 1), Array(59, 1), Array(68, 1), Array(75, 1), Array(79, 1), Array(96, 1), Array(113, 1), Array(115, 1), Array(132, 1), Array(149, 1), Array(166, 1), Array(183, 1)), TrailingMinusNumbers:=True

The first digit of each array is the position, which is zero based.

Reply | Quote

HI Rory. Can you tell me what the 2nd parameter is for, or point me to somewhere I can get info on this Array() function. I have got a macro that reads in a very large textfile, and I find that occasionally it doesn’t parse properly, so instead of parsing 0.0AB into 2 columns 0.0 and AB, on random rows I am getting 0.0A and B. It doesn’t do it for smaller files, only gfor very large ones.

Reply | Quote

I’ve found the meaning of the 2nd parameter, and also done some more testing. My problem is definitely due to the size of the file I am reading in. If I read in the whole file (153000 rows) then almost half the rows have the parsing problem. It’s not just on one particular field, but mainly where a numeric field is followed by an alpha field. The bad rows are not consecutive. I’ve found that if I cut the file down to 90000 rows everything is fine, but at 100000 rows I start having problems – at row 45000 – very strange.

Any explanation for this weird behaviour would be appreciated.

Reply | Quote

Thanks Rory. I got busy with other tasks so I didn’t get to try your solution until just now. It appears to be shifted one character but I’m sure that my bad. Anyway, fixing it will help me better understand the code (I hope).

I would have liked to supply a sample report for others to test but the clients names listed in the report would have had to be scrambled and that would’ve taken way too long.

Reply | Quote

It’s probably better to start your own question for this.

Reply | Quote