Importing Text data using fixed width and delimited

[sheltton]

Hi,

I'm trying to import data from a .txt file

http://www.4shared.com/photo/2fda9qRj/TXT1.html

The condition is it needs fixed width + delimited with "." but I end up having it in this format.

http://www.4shared.com/photo/FUoi3tn4/Excel1.html


What I want is to keep it completely in Column A. after every "." it needs to be on a different row. but in column A.

Thanks in Advance

Please Help

 

[sheltton]

Sorry for the confusion, My Job is to Format the txt files and import them into an excel sheet The shell script you gave me is great, however it isnt serving my purpose , since I need to combine 2 things. 1 Formating the text files(agreed that I can do it in linux but then again I have to 2 Importing them into Excel <<<(Client are Dumb they aren't Linux-Savvy. ha haha h). The VB.Script I posted earlier works like this Stage1. The first vb.script Format's the string withen my selected *.txt files, vbscript - Sub Main() (it works fine) Stage 2. then I Import those txt files into Column A, xlDelimited format at 255 charecters, I end up having string scattered like this. Eg ------------------------------------------------- My name is Jack. I hav e a dog named shadow. He is smart. But not too Bright. ------------------------------------------------- ^This is where the problem starts when I import the data to the excel column^ This is what I want it to do when It Imports. ------------------------------- My name is Jack. I have a dog named Shadow. He is smart. But not too Bright. ---------------------------------- I searched alot of posts and ended up with the script name " Sub Lines2Rows()" This one does the Job However the "." character dissapears and I end up with a something like this. ------------------------------- A1 - My name is Jack A2 - I have a dog named i A3 - e A4 - Shadow A5 - He is smart A6 - But not too Bright. ---------------------------------- Now imagine 10-30 k of lines all messed up I want to combine 3 things Format,Import, as per delimiter "." all in 1 ColumnA Simple. Yet Complicated.

 

[xenou]

I don't think I can be of much more help I'm afraid. As far as I can tell, we've (perhaps) succeeding in placing newlines after each period - if you don't mind doing it in *nix. The rest is clear as mud to me. It's very mysterious what you are up to with this project.

However the "." character dissapears and I end up with a something like this.

Note: Maybe with this last script you can put the "." back in at the end of each cell (after the code runs).

 

[sheltton]

Thax for trying though.

 

[xenou]

No problem - I did enjoy reading your Tesla blurb from post #1. Wish I'd come up with more for you - the bash script seemed more promising at first. We *can* convert that to VBA but it seems like there's more that's at issue here than just the newlines.

 

[sheltton]

Finally I had to break the macro's up into parts, and joined them under Sub Add.

Down fall of it is, that these scripts work under Ms.Word then save the file as TXT and import it from excel later.

hope this will help others.

Note to seniors, Please help trim this a little and if possible make it work for Ms.Excel compatable

----------------------

Sub Add()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
.AllowMultiSelect = True
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
myName = Dir(vrtSelectedItem)
While myName <> ""
With Selection
.InsertFile FileName:=myName, ConfirmConversions:=False
.InsertParagraphAfter
.InsertBreak Type:=wdSectionBreakNextPage
.Collapse Direction:=wdCollapseEnd
Call Format
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
Call Rem_DoubleSpaces
End With
myName = Dir()
Wend
Next vrtSelectedItem
Else
End If
End With
Set fd = Nothing
End Sub
Sub Format()
Call Rem_LineBreak
Call Rem_DoubleSpaces
Call Replace_PeriodSpace_Period
Call Replace_Period_PeriodLineBreak
End Sub
----------------------------------------------------------
Sub Rem_LineBreak()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
----------------------------------------------------------
Sub Rem_DoubleSpaces()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " "
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
----------------------------------------------------------
Sub Replace_PeriodSpace_Period()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = ". "
.Replacement.Text = "."
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
----------------------------------------------------------
Sub Replace_Period_PeriodLineBreak()
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "."
.Replacement.Text = ".^p^p"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub
----------------------------------------------------------