Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data import, carriage return
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data import, carriage return
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 "Sam" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Carriage return | Excel Discussion (Misc queries) | |||
Separating carriage return data into separate cells | Excel Discussion (Misc queries) | |||
Carriage Return | Excel Worksheet Functions | |||
Importing data to excel (newline/carriage return issue) | Excel Discussion (Misc queries) | |||
Excel import txt file carriage return as delimiters | Excel Discussion (Misc queries) |