I have an old DB system from which I've exported data out to several
..ASC text files. I need to massage it to use it in Excel. The
original DB is proprietary, so I can't get to its tables with the
normal tools. All I can do is "export" the data in a text format, and
I have no control over that format.

Each record in the original DB has 8 fields, and there are about 1000
records. The data exports as a single field column, as opposed to
delimited, so I've got about 8000 rows, each row containing 1 field.
The first row is indexed (I guess for fields 1 through 8) as "01", and
then data. Row 2 is "02" & data, Row 3 is "03" & data; so forth and so
on, with row 9 starting over at "01". The exported data looks as
follows:

01XXXXX
02VCVFRD
03LOIKJH
04JYTRFDE
05LKIIUJH
0698jujg43
07jh765rfde
08KJH&^%$fd
01HG54433ee
02IKJU*&^%677
etc., etc.

I need to parse the data horizontally into 9 columns to look like a
normal table, with each field in its own cell (the first column would
be my index). Something like this:

1001 XXXXX VCVFRD LOIKJH JYTRFDE LKIIUJH etc.....
1002 HG54433ee IKJU*&^%677 etc.....

One kicker: Sometimes, all 8 fields are not there, so you will have a
section of the original column where it goes 01, 02, 03, 04, 05, and
then back to 01 again, so you just can't say put the first item in A1,
the second in A2, etc. You have to check that "index".

I have to do this with 20 different "tables". If you can get me
through this one, I can probably extrapolate the concept to the others.

I've done a bit with Excel, so I'm not helpless, but I'm not sure how
to go about this.

Any help would be appreciated.

Latenite

Martin Fishlock wrote:
I think you will find this will do the job. copy it into a workbook and make
the download active and run it it will make a new sheet.

Option Explicit

Sub convertdb()
Dim wsS As Worksheet
Dim wsD As Worksheet
Dim lRowD As Long
Dim lastcol As Integer
Dim col As Integer
Dim rCell As Range

lastcol = 9999
lRowD = 0
Set wsS = ActiveSheet
Set wsD = Worksheets.Add
For Each rCell In wsS.UsedRange
col = Left(rCell.Value, 2) ' current column
If col < lastcol Then ' check if new row col < lsst col
lRowD = lRowD + 1 ' new row
End If
lastcol = col 'record the column for next time
wsD.Cells(lRowD, 1) = lRowD + 1000 ' record no (+1000)
wsD.Cells(lRowD, col + 1) = _
Right(rCell.Value, Len(rCell.Value) - 2) ' data
Next rCell
End Sub

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Latenite" wrote:

I have an old DB system from which I've exported data out to several
..ASC text files. I need to massage it to use it in Excel. The
original DB is proprietary, so I can't get to its tables with the
normal tools. All I can do is "export" the data in a text format, and
I have no control over that format.

Each record in the original DB has 8 fields, and there are about 1000
records. The data exports as a single field column, as opposed to
delimited, so I've got about 8000 rows, each row containing 1 field.
The first row is indexed (I guess for fields 1 through 8) as "01", and
then data. Row 2 is "02" & data, Row 3 is "03" & data; so forth and so
on, with row 9 starting over at "01". The exported data looks as
follows:

01XXXXX
02VCVFRD
03LOIKJH
04JYTRFDE
05LKIIUJH
0698jujg43
07jh765rfde
08KJH&^%$fd
01HG54433ee
02IKJU*&^%677
etc., etc.

I need to parse the data horizontally into 9 columns to look like a
normal table, with each field in its own cell (the first column would
be my index). Something like this:

1001 XXXXX VCVFRD LOIKJH JYTRFDE LKIIUJH etc.....
1002 HG54433ee IKJU*&^%677 etc.....

One kicker: Sometimes, all 8 fields are not there, so you will have a
section of the original column where it goes 01, 02, 03, 04, 05, and
then back to 01 again, so you just can't say put the first item in A1,
the second in A2, etc. You have to check that "index".

I have to do this with 20 different "tables". If you can get me
through this one, I can probably extrapolate the concept to the others.

I've done a bit with Excel, so I'm not helpless, but I'm not sure how
to go about this.

Any help would be appreciated.

Latenite



Martin,

Thank you for the quick reply. I will try it in the morning.

Latenite