Importing multiline records files into Excel cells

M

michaelp

Hello!

I have a text file with many records, each record spans several lines.
Records are delimited with a ^ character (the delimiter occupies a
line). Two consecutive records:

*000nam 2200121 4500
*0011170018nno
*008030813 no sme
*0411 $hger
*100 $aLuther, Martin
*2401 $aDer kleine Katechismus
*2451 $aDr. Morten Luther ucceb katekismusas
*260 $aKristianiast$bGröndahl$c1837
*300 $a87 s.$bport.$c12 cm
^
*000nam 2200157 4500
*0011170064nno
*008051109 no sme
*100 $aStockfleth, N.V.$d1787-1866
*2451 $aAbes ja låkkam-girje
*260 $aKristianiast$bGröndahl$c1837
*300 $a48 s.
*500 $aSkrevet av N.V. Stockfleth
*653 $aABC
*900 $aStockfleth, Niels J. Chr. V.$zStockfleth, N.V.
*900 $aStockfleth, Nils Vibe$zStockfleth, N.V.
^

It is thousands of records, And I would like to remove several hundreds
of them, based on inspecting the records. Time consuming, but no other
way to do it (entirely intellectual).

I would like to do this in Excel instead of in a text editor, and would
like each record
* to be imported into its own cell (say column A)
* line-shifts kept within the cell (eases inspection)

I have so far not found a way to accomplish the former - excel imports
each text-line into its own row.

Any ideas / pointers?

Gratitude

Michael
 
P

PY & Associates

We do not understand your requirements :
* to be imported into its own cell (say column A)
* line-shifts kept within the cell (eases inspection)

but believe it can be done quite easily

Hello!

I have a text file with many records, each record spans several lines.
Records are delimited with a ^ character (the delimiter occupies a
line). Two consecutive records:

*000nam 2200121 4500
*0011170018nno
*008030813 no sme
*0411 $hger
*100 $aLuther, Martin
*2401 $aDer kleine Katechismus
*2451 $aDr. Morten Luther ucceb katekismusas
*260 $aKristianiast$bGröndahl$c1837
*300 $a87 s.$bport.$c12 cm
^
*000nam 2200157 4500
*0011170064nno
*008051109 no sme
*100 $aStockfleth, N.V.$d1787-1866
*2451 $aAbes ja låkkam-girje
*260 $aKristianiast$bGröndahl$c1837
*300 $a48 s.
*500 $aSkrevet av N.V. Stockfleth
*653 $aABC
*900 $aStockfleth, Niels J. Chr. V.$zStockfleth, N.V.
*900 $aStockfleth, Nils Vibe$zStockfleth, N.V.
^

It is thousands of records, And I would like to remove several hundreds
of them, based on inspecting the records. Time consuming, but no other
way to do it (entirely intellectual).

I would like to do this in Excel instead of in a text editor, and would
like each record
* to be imported into its own cell (say column A)
* line-shifts kept within the cell (eases inspection)

I have so far not found a way to accomplish the former - excel imports
each text-line into its own row.

Any ideas / pointers?

Gratitude

Michael
 
D

Dave Peterson

I'm confused about what Line-shifts are, but...

If you import all your data into column A, then maybe you can run a macro that
would essentially do a bunch of copy|pastespecial transpose.

If you want to try:

Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim myBigRng As Range
Dim myArea As Range

Set wks = ActiveSheet

With wks
With .Range("a:a")
.Cells.Replace What:="^", Replacement:="", _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
MatchCase:=False
End With

Set myBigRng = Nothing
On Error Resume Next
Set myBigRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp)) _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myBigRng Is Nothing Then
MsgBox "Nothing found in column A"
Exit Sub
End If

For Each myArea In myBigRng.Areas
myArea.Copy
myArea.Cells(1).Offset(0, 1).PasteSpecial Transpose:=True
Next myArea

On Error Resume Next
.Range("b:b").Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End With
End Sub
 
M

michaelp

I'm confused about what Line-shifts are, but...
Click to expand...

I understand that the confusion was caused by me writing
....
I mean that each record would occupy a signle cell (row, if you like) in
column A. Clumsily expressed. Sorry.
 
D

Dave Peterson

Do you really want each record to be in a single cell in column A or do you want
each record to be a single row--where each column would be a separate field.

You may want to try the macro to see if it works ok.
 
M

mcook

Dave,
I had a similar issue with a text file as michaelp and wanted to let
you know that the code you provided worked perfectly the first
time....I'm now going to go buy a lottery ticket! I had no idea how to
start and thought I would 'google' it first and found your post. Thank
you so much for contributing and keep up the good work.

I hope you have as great a day as mine has started.

Take Care
Mike C
 
Top