Read two text files without opening and write to another

Hi All,

I have two text files, one is called ID with one column named ID. My other text file is called Link and it countains three columns, Link, ID, and Number. What i am trying to do if it is possible is make excel read the Link table and if the ID in row one is in the ID table, excel will copy the entire row of the link table into a seperate, new, text file. Then move onto the next line and so on and so forth. If the ID in Link is not in the ID file the program will just skip that row and move onto the next row on the Link file. I hope i am explaining this clear enough. This is an extremely small scale example but works the same way. Basically i am querying Link with ID and getting only the rows that the ID's Match. I cannot do this in access because the files themselves are well over 2 GB each, which exceeds access limits. Also Notepad will not open the files, so i need some way of excel to do this in the background without opening the text files. The link table is too big to open in notepad. I am basically trying to skinney the Link table down to a managable size to put into access or excel. If anyone can help me or has any suggestions, they are much appretiated. Thanks. Please let me know if i didn't explain myself well enough to understand.

Hello amotto11,

Will there be just one new text file to hold the matches?

yes, it will just write the full line from Link into one text file and just keep appending the matched lines into that file. Hope that makes sense. Thanks

Hello amotto,

Makes perfect sense. VBA can read and write to the files directly without opening them in an editor program like Notepad, Wordpad, etc.

Yes, that is excactly what i am looking for. I cannot find anything online to help me with my problem. Any help from you would be greatly appretiated.

Hello amotto,

I finally got the time to finish this macro for you. After you add a new module to your workbook's VBA project, copy and paste the macro code below into it. You will probably need to change the file path and file names to match what you are using. The output file will be created if does not exist. The other 2 files must exist or the macro error and stop.

Please Login or Register  to view this content.

Here is sample of the output using the files you posted.

Please Login or Register  to view this content.

Leith,

This is exactly what i am looking for. I can work with this, but i just have one more question. How do i make the output file comma delimited? when i am done with this file i import it into PowerPivot so i need to have something that i comma delimited in order to do this. Thanks so much for all of your help.

I inserted this line as the print statement

Please Login or Register  to view this content.

but i am still getting large spaces in my data that looks like this

Please Login or Register  to view this content.

replace your Do While with this

Please Login or Register  to view this content.

Leith and Mike,

I kept Leith's code because it didn't matter to access if there were spaces in the data or not, as long as they were comma delimited. The program is fairly slow for large files, which is understandable, but is there any way to speed it up?

Also i have a 64 column Link table now so is there any way to write this statement without having to write it 64 times?

Please Login or Register  to view this content.

Hello amotto11,

It is an easy change to make it comma delimited.

Please Login or Register  to view this content.

That looks perfect, Thanks so much Leith for all of your help!

Hello amotto,

Does an ID ever match more than one link? If so, the macro could be sped up by exiting the loop after the data is written.

Yes, in the link file there can be multiple of the same ID's and i want all of them to be captured. Think of it like a relational database, the ID file has one column that is the primary key and the Link file has the same column in it but that column is a foreign key in that table. There is only one iteration in ID and there are multiple in Link. Can you help? Thanks

It can look like this

Link, ID, Number
12,1,23
23,1,32
34,2,35

In this case if in the ID Table was 1 and not 2, i would want the first 2 rows copied, but not the third. The primary key, never repeats, in the Link table is Link, and the foreign key, can repeat but relates to another table, is ID.

Hello amotto,

The macro looks for multiple links that match a single Id. If the complete Link file were moved into an array, it would speed things up a lot. However, if you have large files then could tax your memory and any speed gains could be lost. If you want me to make the change, I will and we can test the result.

Well, I am not too too concerned about the memory or time because most of the time i would run this over night, although i say most of the time, i may have to run it once in a while during the day too. The Link file can be as large as 12,000,000 rows and 64 columns, and the ID file as large as 60,000 rows by 1 column. I am not sure how to make the link file an array, but do you think that it would help to. I am willing to try just about anything if you think it would help.

Hello amotto,

Let's give it a try. The less time it takes the better.

Right now i am running something through it that has the Link as 200,000 rows and 64 columns, with the ID being 10,000 rows. It has been about 25 min, and it is not finished. You can only imagine what it would take to run through 12 million rows with 60,000 criteria. hah

Yes, the less time the better. Thanks again for all of your help

Hello amotto,

Here is the updated version. This will run much faster because the Link data is now in an array.

Please Login or Register  to view this content.

Leith I am getting an error on this line

Please Login or Register  to view this content.

The only thing that i have changed is the file location, also i am running this with my initial link and ID text files that i posted.

give this a try

Please Login or Register  to view this content.

Mike,

That worked for my small example file. and i am now running it on my middle size test file, the one with 200,000 rows in link and 10,000 in ID, i will let you know. Thanks!

Hello amotto,

Sorry, I copied the unedited file. The array is zero based and the UBound returns the number of items. One needs to be subtracted from the UBound.

Please Login or Register  to view this content.

Ok will Link always have more Lines the ID. If so could speed it up by looping thru the Link file and then looping thru the ID will save a lot of loops

Leith,

Yours now worked for the small one.

Mike and Leith,

I am going to try both and see how they respond to the larger files. I will let you know. Please check back with me in a bit to see how it went. Thank you both for your help.

---------- Post added at 12:12 PM ---------- Previous post was at 12:11 PM ----------

Originally Posted by mike7952

Ok will Link always have more Lines the ID. If so could speed it up by looping thru the Link file and then looping thru the ID will save a lot of loops

Mike, Link will always be larger than ID, because ID is the primary key and Link is the foreign key. It will ALWAYS be the case that Link is larger than ID. Could you work up the code for me? Thanks

Ok try this one, how did the other one do?

Please Login or Register  to view this content.

The other one, is still running, i had to make a quick change to my file so i had to restart it. I am going to stop it and try to run this one.

Mike, on that one, it does not put headers on, which i am not too concerned about. But it also repeats the first find twice on the same row, so the data looks like this for the small files

Please Login or Register  to view this content.

Hum

Thry replacing this part

HTML Code: 

---------- Post added at 01:35 PM ---------- Previous post was at 01:33 PM ----------

I took out a couple of Ifs and added the headers

Mike, i am running the one that duplicates the first row, and i will let you know how it goes. So far it has been running for about 10. I will see how long it takes, then probably run Leith's and see how long that takes, If yours is the shortest i will put that code in that you just gave me. I will let you all know. Thanks again both of you for all of your help.

I wish there was some way of seeing how far it was into the file. That way i would know how long it would have left. Mike do you think i should stop it and replace your existing code with the newest you gave me? It has been running for about 20 min now.

Yes I would because the new code gets rid of a couple ifs

Alright, I will start the timer

Mike, after 8 mins i got an error on this line.

Please Login or Register  to view this content.

I have no idea how far the code got.

---------- Post added at 01:04 PM ---------- Previous post was at 01:00 PM ----------

Leith,

I am going to try yours next.

---------- Post added at 01:11 PM ---------- Previous post was at 01:04 PM ----------

Mike the error was

Run-time error '9':
Subscript out of range

Leith,

Time to run yours

Leith,

Yours took about 15 min, and i am going to check to see if it got everything.

The titles are not in the output file, is there some way of getting them in there?

And it did not gather all of the data. I am going to check and see why not, and possibly run it again

Hello amotto,

I made another revision to the macro. Hopefully, you have enough RAM to run this. This reads the Links into a Dictionary object to allow random access of the file data by the ID. This should be quite fast.

Please Login or Register  to view this content.

On one of your Rows in the Linked File has got to be missing the ID field, Im sure of it. If you wanna try again I added an if to check for it and also instead of adding items to array I changed to print to the output file

Please Login or Register  to view this content.

Leith,

This runs extremely fast, although it only out puts like 100 rows, when it should be out putting something like 10,000 rows. I am going to try to dig into this and see if my text files have some errors or something.

Mike,

I will check my text files. It is possible that they have an error, but i tried my best to pick data that didn't. I will look through it again.

Thanks to you both again.

With the code I just uploaded it will skip and not error out, If you want to try it again

Leith,

Yours is only getting a limited number of data, i tried to use a new ID file and it should have gathered right around 3000 rows in the output but it is only getting about 20. Do you maybe know the reason?

Mike,

I am trying your new one now

Anyway we can get these test files your working with?

I wish you could, but this is confidential data to my company. I am an actuary and working with market data that i cannot supply, that is why the data could be wrong in some places. I am trying everything that i can think of to get a scenario where this will work. I am not questioning your code. I am sure it works. Part of this project was to check for errors in the data that we gather. Please stick with me while i try your new code Mike, you and Leith have been such a tremendous help.

I understand. If you look at the values of x and y it should tell you the line in the file that is corrupt

Okay,

I have found something.

I ran both of your codes and they both had the exact same result, the out put file only had about 20 rows of data in them and they needed to have about 3000. Leith's was extremely fast, but both worked just fine. I now know i guess that there is an error somewhere.

What do you guys think could be the error. I am not sure what your code does, i am not that advanced, but there has to be a reason why they only are gathering 20 lines. Is it that they are stopping when they get to an errored line? is it that they skip all errored lines? What is the error, is there no ID field or is there not 64 columns?

The reason i know there are 3000 entries that need to be in the output file is that when i put both tables in to access and query them on the ID i get 3000 rows. I will not be able to do this when the link table is 12 million rows, hence i need the code.

I think i may know the error... In the previous codes, early this morning and yesterday i was changing the code to look in the 1st column of the link table. I believe that both of your codes are looking in the second column like my example. I am so stupid if this is the case. Leith and mike could you guys change your code so that it is looking in the first column for the ID's. If this works, i am so so so sorry for that. or just tell me what i need to change to do this.

Hello amotto,

I found a few omissions in my code. I created a larger test to debug it. This version ran correctly for me and it should for you as well.

Please Login or Register  to view this content.

EDIT: Changed the ID field from 1 to 0. The ID is taken from column 1 instead of 2 in the file.

How about importing both files into and excel file different sheets. Then delete all data other the the ID fields and upload the workbook. My best guess is that the ID are not matching or maybe theres a space in one of the ids and then they wouldnt be equal. you could try adding the trim to remove spaces from the beginning and ending of the ID values

Please Login or Register  to view this content.

---------- Post added at 03:10 PM ---------- Previous post was at 03:08 PM ----------

Just change this

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Leith, I am almost positive it is because it is looking in column 2, when it should be looking in column 1, i am so sorry both of you.

Hello amotto,

I made the change to my last post. It will show where to make the correction.

If this doesn't work i will certainly do that for you mike. Thanks for the suggestion.

Leith,

THAT WORKED, I am so so sorry i was wasting time.

Mike, yours worked as well, but leith's was a little faster.

Thank you both so much for everything. If i come across any errors while checking the next bigger files, i will let you know. Again i am so sorry for the hassle and my stupid mistake, that is not like me.

Hello amotto,

Glad to hear it works. We all make mistakes. Sláinte!

I tested it many more times, that is how fast it was, and it worked wonderfully. Thanks again!

Just wondering how much faster? Im wondering if this is faster then my other code.

Please Login or Register  to view this content.

I will give it a try, in just a sec

---------- Post added at 02:52 PM ---------- Previous post was at 02:46 PM ----------

Mike, it did not seem to be any faster, if anything it seemed maybe a slight bit slower.

Ok im glad Leith was able to help you.