Importing text using macros to identify tags and merge data into columns

Thank you so much shg. It worked and I have studying the code to understand how to better myself as a vba developer.

I have run the macro in a large dataset and it generally work perfectly. There are a few minor situations in which the code seems to not work correctly (and its not the macro's fault) because the data is missing or presented differently.

The first situation is when there are several tags of the same kind (e.g. multiple authors and each is identified by the tag "A1 - ". Is there a way for it to only use the first instance of the tag and ignore the rest.

The second situation is when the date is presented as not as just the year, but also the month and date (e.g. 2010/04/10). Is there a way to only include the year (i.e. first four intigers).

Last situation I found that is problematic is when the information is possibly located in several different tags. This is similar to the first problem with the author names, but the journal name could be presented in one of several tags (or presented in different ways in several tags). I only want the journal name as presented in the first tag, but if not present I would like the macro to look for the name in one of the other tags. The tags is order of importance are "JF - " , "JO - " , "JA - " , "J1 - " , "J2 - ".

Sorry for not noticing these possible bugs from the start.

Your efforts are highly appreciated.

Best wishes,

Abousetta

Hi shg,

Thanks for all your help. I have run the code on real time from a large database and I have come across a bug which I can't explain. In the attached examples, the macro should get the author name and date but for some reason returns blanks. The weird thing is that with similar references they seem to return the information. The only thing I can tell that seems to be the same in all of them is that the date is only four digits and the backslashes for the missing month and year (e.g. 2010///). What I don't understand is that with other references, this is not an issue and so I don't know how to circumvent this problem.

Thanks again for all your help.

Abousetta

N.B. Sorry that I uploaded the file as .doc file instead of .txt because the file was too big to upload as txt

sorry about that... I thought I attached it. Here it goes again

Abousetta

Also, as I am trying to self-teach myself vba, could you please tell me what function sInp has? I have tried to google it but have not found an explaination. I have only found it used in several macros, but I still don't understand what it is doing.

Thanks.

Abousetta

Sorry for the confusion. Here is a text file with the actual output with the problematic citations. I have provided a samples since the upload tool will only allow me to upload a txt no larger than 100kb. That was the reason I originally saved it as doc because it was around 500kb.

Abousetta

the under 100kb text file

Abousetta

Hi shg,

I have been investigating the possible source of the bug and I think I might understand now where the problem lies. As far as I can tell, there are two sources that are causing the isolated incidences:

1) Some of the data in the same tag is seperated by a carriage return.
2) Some of the tags are not six characters, but instead five characters

These two issues seem to be confusing the macro. Is there a way to specifically only look at information that begins after a carriage return, but has the exact tag details that I described previously.

The first attachement (Updated Bugs.txt) contains three examples that have the problem. The second attachment (Updated Bugs (problem solved).txt) shows that once I removed the problematic areas from the text file, it works perfectly.

Thank you again for all your time and consideration.

Abousetta

Thank you once again shg...

I have run the updated code you sent and so it works like a charm.

Have a great weekend.

Abousetta

Thanks shg... you have been very helpful and patient with me.

If possible, I have found one (hopefully) last issue I hope you will be able to help me resolve.

The code works flawlessly according to the guidelines I have previously described. But I have another situation, and I have not been able to modify the code correctly to run correctly. The situation is related to the titles of the reference. The references could contain a comma or could be divided over several tags (all which will be "T1 - ". The problems are:

1) Once the code reaches the comma in the title, it stops that line and moves to the next tag, but the comma is part of the title and not a stopper

2) The title can be divided into several tags with the same six character id "T1 - ". I would like to include all the data from all the tags in a sequential order with a semicolon between them. Is this possible.

Apologies for not picking up these problems earlier. I have been trying all morning to modify your code so I don't have to bother you again, but the student is still learning from the master .

Thanks again for everything.

Abousetta

P.S. attached is one example where both problems exist

You're 100% correct and I apologize. I have been starring at my screen for so long that things have become jumbled in my head. What I have done is extend your code to include a new tag (title) in the next column (C). I have been trying to make it work so I can later add as many of the possible tags as needed per project.

The problem appeared for the title, abstract and key word tags which contain free text (including commas, semicolons and/or colons). The code stops reading once it finds a comma and will not allow me to combine multiple tags (with the same tag id in the same cell with a demarcation (e.g. semicolon) between them) as it only uses data from the last available tag with the specific tag id.

The modified code that I have been using for the added title tag is below:

Please Login or Register  to view this content.

Thanks again and I apologize for the lack of clarity on my part.

Best wishes,

Abousetta

Thanks... as always worked like a charm. Now it reads the whole line and doesn't stop at the commas.

Is there another piece of code I should add when there are multiple tags and I want to concatenate them into one cell. For example for title (T1 - ; T1 - ; T1 - ) or (T1 - ; T2 - ; T3 - ). Knowing this option will be of great help because there are over 50 possible tags that I can either place in separate columns or combine into one column. Some will have preference over others, while some should be concatenated with others in the same cell.

The final question (that I can think of) relates to when the tag is divided by return carriages. The example below all belong to one tag, but because of the return carriages between them (for simplified reading) the macro only returns the first row. I could do a global search first and replace all return carriages with with char(10) or char(13) but then all the tags will be merged into one tag (a really big paragraph).

Your continued help and support is greatly appreciated.

Abousetta

P.S. Here is the current code I was working with:

Please Login or Register  to view this content.

Here is an example that should capture all the possible scenerios.

We would like the data from the txt file attachment to go into the folloming cells:

* column (E) for the key words ("KW - ")
* column (F) for the notes ("N1 - ")
* column (G) for the volume, issue, page number start to page number finish ("VL - "("IS - "):"SP - " - "EP - "

Notes:
* Column (E) gets data from several tags all being ("KW - ") and so should separate the entries by a semicolon
* Column (F) gets data from one tag ("N1 - ") but the data is separated by return carriages
* Column (G) gets data from several different tags and should put brackets around "IS - " followed by a colon and a dash between "SP - " and "EP - "

The attached xlsm file contains what the code up to now and how I would like it to appear.

Thanks again.

Abousetta

Hi shg,

I have posted the example as requested.

Thanks again for all your help.

Abousetta

Thank you for all your help. I know that I have been troublesome, but I am truly grateful for all your help.