Transpose vertical format to horizontal from text document??????

[Melee]

The format at the end of this explanation is a sample layout of one segment of the document I would like to convert.

The problem: I have a large amount of data in restricted proprietary Lotus based databases. The databases are not accessible through conventional means. I have been able to retrieve the data from one of the databases into a text file. The file is one long column -- in excess of 2000 pages. I would like to put the data into a format that can be exported into an Access database. It is way too intensive to do manually.

The Question: What is the easiest way to get the data into a table format with the column headings being the text to the left of the ':'??

FYI -- I have dabbled with VB and done some pretty extensive Access databases.
============================
Address1:10888 Lava Rock Rd.
Address2:
Address3:
City: Hilo
State: HA
Zip: 06670

HomePhone:
MobilePhone:
OtherPhone:

EmailNo:

FirstName: George
MiddleInit:
LastName: Smith

ContactTitle: Manager

ClientConsultant: PD

MainContact: No

DocAuthor: Paxia Dania

03810237010041
CompanyNo: 01755826010041
Office^Office,Division^Division,FirstName^First Name,LastName^Last Name,ClientConsultant^Consultant or Team,ShortContactNo^Short Contact Number
WorkPhone: (916) 861-3949
FaxPhone:

COMPANYNAME: Fred's House of Slime

MasterCompanyName: Fred Corp
#03358

 

[Storyteller]

Hello Melee,
I would suggest either =Transpose() or Paste Special-Transpose.

Using your above sample I was able to use Paste Special -Transpose. Mind you I had to clean it up a little first.

I used "Text to Columns" and use the ":" as the seperator to spilt the data. Then used Paste Special - transpose.

You may have to experiment a little to get it right.

Hope this helps.

Michael

 

[Hasit]

Melee, I think Storyteller has assumed that the data is in Excel already. I have assumed it is in Word.

You need to do the following:

First make sure you cope the data into excel - you can copy and paste it straight from Word into Excel. The problem here is that data will come in as merged cells, so the suggestions Storyteller makes will not work staightaway.

Second, you need to split out the fields (column heading vs. data etc). To do this, you have 2 options:

1. You can either use the following commands:

=MID($A1,1,FIND(&quot;:&quot;,$A1)-1) <- THIS WILL CREATE THE HEADING

=MID($A1,FIND(&quot;:&quot;,$A1)+1,100) <- THIS WILL CREATE THE DATA

You have to do it for each cell of data, and the formula should go into column C and D

Or;

2. You could go highlight the entire column (you may need to clean up some of the data and strip out nonsense characters), and use DATA->TEXT TO COLUMNS and follow the prompts.

Once you have done this, you can then use Storyteller's suggestion of COPY->PASTE SPECIAL->TRANSPOSE which will move headings and data in the way you want them. As you are working in Excel, it is then reasonably simple to transfer the data to Access via CSV file (for example).

Of course the other option if you want to spend some money (around $100) is to get a tool to migrate the data from the Lotus database (I am assuming it is a Lotus Notes database) to MS Access. Take a look at this link for some info:

http://www.acc-technology.com/ntwiz.htm

 

[Melee]

Thanks for suggestions. The data is in word. I have been playing with each of the processes mentioned. I was hoping to avoid the manual clean up of the data. This is just the first of several databases that need to have the data extracted. I was thinking maybe a macro -- something to the effect of -- if row x, column A = &quot;CompanyName:&quot; then row x, column Y = row x, Coumn B

In other words--If I have the first two columns filled with the data using the &quot;:&quot; delimiter, then read the first column for the specific text and write it to the column with the label of the same name. Each grouping is separated by #. Can this be done?

 

[Melee]

To Hasit -- I may very well love you!!!!! The web link you gave me might be exactly what I need.

 

[Hasit]

Melee, I am pleased to got that reaction! :~/