Transpose Data Array in batches in Excel.

Question

Hello, I have a question about how to batch Transpose data in Excel.  I work in at an archival institution and have over 75,000 digitized catalog cards.  They are in a PDF format and have been OCR’d.  I am able to make a compound PDF (about 750 cards/pages each) and export the data into a text file and then import it into an excel sheet. The data then fills column A with no spaces.  I have used the Transpose functions to separate the data into columns in my test case.  Because there is so much data, I am wondering if anyone knows how to Transpose data in batch file.  Does a macro for this exist?  The cards have three to five lines of data (the majority have 4 lines):

1 line = Subject Heading (Person -usually last name, first name, place or thing)

2 line= RG# -Collection Name .image number

3 line = image caption

4 line = image caption cont’d (not always applicable)

5 line = image caption cont’d (not always applicable)

This is what the information looks like in Excel:

Ex.

Harding, Warren G.
RG100S-Paul J. Hoover Coll. <br>.3-4
President Harding visits the 60th anniversary of the
Battle of Gettysburg in July, 1923.

The last 3-5 caption lines, I figured I could use the CONCATENATE function and run them all together at the end.

Two other questions: 1. Would it be better to organize the cards by the number of lines in the caption?  2. Is it possible to separate the information in line 2 into three columns (RG#, Collection Name, Image number)?

Any information that anyone could have would be greatly appreciated.

Thanks,

Molly

Answer 1

You can use OpenTextFile, read the file directly, create an array and split the lines with VBA and transform that into the appropriate format in a 2D array, which you can store into a sheet.

That's the way that I would go, I've shown this in this forum several times, search for OpenTextFile in this forum. I can't give an example, because you didn't say how we can determine where one card ends and the next card begins.

If you are not familiar with VBA, upload a sample text file with a few hundred cards to an online file hoster like www.dropbox.com and post the download link here.

Andreas.