Jump to content
Macro Express Forums

Deleting columns in a tab separated text file


Recommended Posts

Is there any information I can get to help me solve this riddle?


I am looking to use either text file processing or ASCII, but I think ASCII might be out of the question because initially there are greater than 99 columns. I want to delete excessive columns BEFORE I use ASCII processing to make sure there are fewer than 99 columns, but I don't know how to delete columns and all respective data under those columns. Oh, by the way, the information is stored in a text file as tab separated values.


Can anyone point me in the right direction?



Link to comment
Share on other sites

2 methods spring to mind.

1) Use Excel; load the text file, delete the relevant columns, save the file, then load it into ME

2) Process the file in ME by loading it with the Text File command, count your way through each line to locate the "columns" you don't want (looking for the tab character as your delimiter between columns), erase the text between whichever tab values you no longer want, write the file back out, then reprocess it with the Ascii command.

Link to comment
Share on other sites

Paul's suggestion #2 is what I was trying to describe to you. Personally I don't use the text file process but that's an clever way to avoid looking for the next CRLF. Lets say you want to get the data in column number 101. Once you have the row in a string var simply start a repeat loop that will run 100 times. In each case find the position of the tab (assuming it's a tab separated file, you can change the delimiter if need be) say to N1. Now delete part of text 1 to N1 and repeat. Once out of the loop find the position of the next tab again and copy part of text 1 to N1 again to T2. Now trim T2 to get rid of the extra tab and you have the contents of the 101st item!

Link to comment
Share on other sites

OK, I might be onto something. Can anyone tell me what is wrong with this? Am I going in the right direction?

<ASCIIC:2:1:9><IVAR2:05:01:0><BTFBEG:001:000001:000001:C:\Documents and Settings\Pat\Desktop\Contact Files\Contact.txt><TMVAR2:09:10:01:000:000:><REP3:08:000008:000001:0001:1:01:T2><NMVAR:08:05:0:0000001:0:0000000><IFVAR2:4:01:8:T2T><BREAK><ENDIF><IVAR2:01:13:10:%T2%><TMVAR2:11:10:00:001:N01:><ENDREP><BTFEND><DIS:<TVAR2:02:01:blah blah blah blah Orders halb halb halb halb><DIS:<IVAR2:01:13:2:Orders><DIS:<NMVAR:02:01:1:0000001:2:0000001><DIS:<TMVAR2:11:02:00:001:N01:><DIS:<TMVAR2:04:02:00:000:000:>

Link to comment
Share on other sites

Spent almost all night trying to get it, but I need a little bit of help. This file has the headers I am using to first get the header name and then get the value underneath it. I think deleting the columns was a good idea, but I think I got it using text processing instead.


So far I have this:


<ASCIIC:2:1:9><IVAR2:05:01:0><BTFBEG:001:000001:000001:C:\Documents and Settings\Pat\Desktop\Contact Files\Contact.txt><TMVAR2:09:10:01:000:000:><REP3:08:000008:000001:0001:1:01:T2><NMVAR:08:05:0:0000001:0:0000000><IFVAR2:4:01:8:T2T><BREAK><ENDIF><IVAR2:01:13:10:%T2%><TMVAR2:10:11:10:001:N01:><IVAR2:02:12:11><IVAR2:03:13:11:%T2%><TMVAR2:11:11:00:N02:N03:><TMVAR2:11:10:00:001:N01:><ENDREP><BTFEND><DIS:<TVAR2:02:01:blah blah blah blah Orders halb halb halb halb><DIS:<IVAR2:01:13:2:Orders><DIS:<NMVAR:02:01:1:0000001:2:0000001><DIS:<TMVAR2:11:02:00:001:N01:><DIS:<TMVAR2:04:02:00:000:000:>


Just to let anyone know, there are only 2 lines in each text file. One is the header and the other is the information.

I think I need to use text processing to single out the "information" line in the text file. How can I get the data underneath the column header? Should I do the same thing and use text processing to target the value. If so, how?





Link to comment
Share on other sites

If I remember correctly you originally get this data from the clipboard, correct? In that case I would avoid ever writing it to a file which precludes the text file process commands. It's better to just do it all in memory as I have demonstrated. What you wrote shows you have the right idea but I think you will 'get' my sample macro even though it's a slightly different approach.


Having said that... One fault I found with your macro was the Text File Process. If there are only two rows and the first is the header row you should start processing at line 2 not line 1. I think your attempt to test for the Tab (T2) is trying to do that but the header row will contain tabs as well so if I understand this correct it's not going to work.


One note on my macro and how it gets past the header row. In most cases the actual character count of the header row doesn't vary so it's easy to set the chunk to be deleted to a specific amount. But if it does vary then you need to get the position of the first CRLF, increment 1 since there are two characters, and delete that chunk.

Link to comment
Share on other sites

Hello All!


I was able to find a way to isolate tab separated columns using text file process.


Cory, I took some of your ideas too! i used a text file instead of the clipboard even though it could be modified to work that way.


<ASCIIC:2:1:9><IVAR2:05:01:0><BTFBEG:001:000001:000001:C:\Documents and Settings\Pat\Desktop\Contact Files\Contact.txt><TMVAR2:09:10:01:000:000:><REP3:08:000008:000001:0001:1:01:T2><NMVAR:08:05:0:0000001:0:0000000><IFVAR2:4:10:8:T2T><BREAK><ENDIF><IVAR2:01:13:10:%T2%><TMVAR2:10:11:10:001:N01:><IVAR2:02:12:11><IVAR2:03:13:11:%T2%><TMVAR2:11:11:00:N02:N03:><TMVAR2:11:10:00:001:N01:><IFVAR2:1:11:1:ID/Status><NMVAR:07:05:0:0000006:0:0000000><TMVAR2:09:12:11:000:000:><ENDIF><ENDREP><BTFEND><IVAR2:07:01:0><BTFBEG:004:000002:000001:C:\Documents and Settings\Pat\Desktop\Contact Files\Contact.txt><TMVAR2:09:05:04:000:000:><REP3:08:000008:000001:0005:1:01:T2><NMVAR:08:07:0:0000001:0:0000000><IFVAR2:4:05:8:T2T><BREAK><ENDIF><IVAR2:10:13:5:%T2%><TMVAR2:10:06:05:001:N10:><IVAR2:08:12:6><IVAR2:09:13:6:%T2%><TMVAR2:11:06:00:N08:N09:><TMVAR2:11:05:00:001:N10:><IFVAR2:5:06:1:N7T><DIS:<NMVAR:07:05:0:0000006:0:0000000><TMVAR2:09:07:06:000:000:><ENDIF><ENDREP><BTFEND>



Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...