importing .csv file as text with line breaks preserved

[Matt McClure]

I'm trying to import a .csv file where some fields are numbers with
leading zeros, and others have embedded line breaks.

If I name the file with the .csv extension, the line breaks within
fields are preserved, but the leading zeros are missing because every
field is interpreted as General instead of Text.

If I name the file with the .txt extension, I can manually change fields
to Text as appropriate, but I can't figure out any way to preserve the
line breaks within fields. All line breaks are interpreted as the end
of a row. The manual aspect of this process is less than ideal too.

What I'm looking for is an automatic import that preserves line breaks
within fields and interprets all fields at Text rather than General.
I'd settle for a somewhat manual process that achieved this also.

Is this possible?

Thanks,
Matt

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (Cygwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQE/IU9erGgMpZIoLA8RAgZvAJ9ra1BaZJ8oe+f7hmsSxYqpisip2ACgkz6f
EjgnDxuDbM1Kn7PkSR2HF/o=
=f1l4
-----END PGP SIGNATURE-----

 

[Dan E]

If you don't mind macros, this will work

Sub ImportText()
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

FName = Application.GetOpenFilename
Sep = InputBox("Enter the delimiter, for tab delimited data type tab")
If Sep = "tab" Then
Sep = vbTab
End If

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.Row

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
ColNdx = SaveColNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = "'" & TempVal
Pos = NextPos + 1
ColNdx = ColNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
RowNdx = RowNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

Dan E

news:[email protected]...