Data import, carriage return

[Sam]

Hi,

After I export addresses from our database to a .txt file.

When I open it in Excel
the address fields start a new row like below:
Name
Address1
Address2
Address3

Not the format I wanted
Name Address1 Address2 Address3

How to import the .txt to the correct columns?

If I open it in Notepad
When the Format - Word Wrap is selected, the appearance is the same as
Excel
When the Format - Word Wrap is unselecte, the Address1, Address2 and
Address3 are separated by a small square (I suspect it's a carriage
return)

Is there a way in Notepad to replace the small squares with a "!"?
Then hopefully Excel would import them with the right column, however,
the title of the column might not correctly lined up.

If I open it in Word, the appearance is similar as Excel.
If I Find "^p", I could find that at the end of every whole record
If I Find "^013", I could not only find that at the end of every whole
record, but also at the end of Address1, Address2...
Maybe there is a ASCII code for the small squares only?

At the moment, I could
In Word, replace the "^p" to "End***End", then replace the "^013"
to !, then replace "End***End" to "^p", save it as .txt.
In Excel open the .txt. copy of the column field alone to a new Excel,
save into tab deliminated, then open withe deliminator with Other "!",
then copy the columns back to the original file.
It's quite a work aroud.

I am sure there are better ways. Hopefully you could help.

Regards
Sam

 

[Joel]

It sound like your file has only Linefeed instead of carriagge-returns then
line feeds. Try this macro to fix the file. It will make sure your file has
both carriagge returns and line feeds. It will open your original file in
text mode in read mode and create a new text file in write mode.


Sub fixcrlf()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "C:\temp\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Dim CR As String
Dim LF As String

CR = Chr(13)
LF = Chr(10)


Set fsread = CreateObject("Scripting.FileSystemObject")
Set fswrite = CreateObject("Scripting.FileSystemObject")

ReadFileName = "intext.txt"
WriteFileName = "outtext.txt"


'open files
ReadPathName = MyPath + ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

OutputLine = ""
FoundCR = False
Do While tsread.atendofstream = False

MyChar = tsread.Read(1)

Select Case MyChar

Case LF
If FoundCR = False Then
tswrite.write CR
Else
FoundCR = False
End If
Case CR
If FoundCR = True Then
tswrite.write LF
Else
FoundCR = True
End If
Case Else
If FoundCR = True Then
tswrite.write LF
FoundCR = False
End If
End Select
tswrite.write MyChar
Loop

tswrite.Close
tsread.Close

End Sub