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]

I'm stuck again, I'm trying to combine my 2 new macros
-----------------------
Sub Main()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant
With fd
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
Dim strBuffer As String
Dim ff As Integer
strBuffer = Space(FileLen(vrtSelectedItem))
ff = FreeFile
Open vrtSelectedItem For Binary Access Read As #ff
Get #ff, , strBuffer
Close #ff
strBuffer = Replace(strBuffer, ".", "." & vbCrLf)
strBuffer = Replace(strBuffer, " ", " ")
strBuffer = Replace(strBuffer, " ", " ")
strBuffer = Replace(strBuffer, " ", " ")
strBuffer = Replace(strBuffer, " ", " ")
Kill vrtSelectedItem
Open vrtSelectedItem For Binary Access Write As #ff
Put #ff, , strBuffer
Close #ff
Next vrtSelectedItem
Else
End If
End With
Set fd = Nothing
End Sub
-------------------------
&
-------------------------
Sub Lines2Rows()
Dim rList As Range, rCell As Range
Dim lRow As Long
Dim vText As Variant
Set rList = Range("A1", Range("A" & Rows.Count).End(xlUp)) ' list
lRow = 1
For Each rCell In rList
If rCell <> "" Then
vText = Split(rCell, "-")
Range("B" & lRow).Resize(1 + UBound(vText)).Value = Application.Transpose(vText)
lRow = lRow + UBound(vText) + 1
End If
Next rCell
End Sub
----------------------

What I'm trying to do is format them withen .txt as

example:
am,nsbkdjhd askjl an .
ajksdajk jnmad.
afjhwijh fkjsbnm,dsvc.
kjdsckjdb cks.

and write it to A:A as 1sentence = 1 cell

Also help me make it a Lite Macro.
Cheers

 

[xenou]

How big is this file? By the way, "fixed width" and "delimited' are mutually exclusive. If a text file is delimited, it's not fixed width, and vice versa.

I don't frequently download files from unknown sources so I have not seen your sample.

 

[sheltton]

The Data are Txt files sort of e-Books would be a good example, but they are unsorted and have variable delimited.

ex : ajbhskha hafvdfv,jdshc skhds. jknhfkjwbn , jcnwjcnbnj ,(ckjdcbc), uh wjwn.

I can have 3 "." in a line
but I want them to be split up with a new line eachtime there is a "." and the problem even arises when i have "e.g." & "Mr." situations if you understand. what I mean

 

[xenou]

ex : ajbhskha hafvdfv,jdshc skhds. jknhfkjwbn , jcnwjcnbnj ,(ckjdcbc), uh wjwn.

I can have 3 "." in a line
but I want them to be split up with a new line eachtime there is a "." and the problem even arises when i have "e.g." & "Mr." situations if you understand. what I mean

That looks like a show-stopper to me. We can break on every full stop/period but can't distinguish between full stop/periods that aren't "really" the end of sentences. Maybe, possibly, if there's a period + one|two spaces plus a capital letter ... that *might work* ... does that describe your data? Is this in fact just sentences of written material? How big is the file?

 

[sheltton]

Kind of kinky, but i'll manage with the period's. aha ha ha ah

The file size's are variable since I can Ctrl + select as many TXT files I need so it really doesnt matter.

All I need at the moment is to Merge both my VBA's and Trim it a little. So that it works Really Fast. Since I am planing on buying office 2010 I wont complain on windows at all. since I colate most of my data from Linux. even most of my text processing is done from linux, but i think eventually I'm not a script writer for Linux I'm more comfortable with VBA and excel though I still consider myself a noob. )

Plez help if you can.
Need to start collating data By tomorrow. Have a huge proj waiting.

 

[xenou]

Well, I should like to know about the file size as it makes a difference if its a few hundred lines or a few hundred thousand lines - in terms of how much we can dump into memory or whether its feasible to loop through the file one character at a time. Things like that - not that I really have any idea what I'd do if you said it's files with 750,000 lines in it. If you are doing data processing on the other side where the files are generated why not fiddle with it so the output is better (is it really your files on linux here that you are creating - Linux is generally very good with this kind of thing - probably grep and a redirect is all that you would need?) Surely, there's better tools for processing text files than Excel but I guess its a hammer/nail thing, right? Note that if you have lots and lots of data I don't think you really have room to be indifferent to the Mr. or e.g. problem - you're going to end of with loads of text files that still are not fully reliable and will have many errors in them. It would be better to back up and rethink what is happening here. Also, why in the world would you want all this textual data in excel cells? Surely, it's at best an awkward tool for such work.

 

[xenou]

Okay, if you can really get to the linux machine, then you can do the following:

1) create a folder to put all the .txt files in
2) create a text file called Do_Changes.sh and put it in the folder
the file will contain this script:

#!/bin/bash
     for fl in *.txt; do
     mv $fl $fl.old
     sed 's/\./\.\n/g' $fl.old > $fl
     rm -f $fl.old
     done

3) save the file and set the executable flag
4) have all the texts files you want to process in this folder (maybe back them up first)
5) run the script

adapted from:
http://www.brunolinux.com/02-The_Terminal/Find_and Replace_with_Sed.html

This worked for me with a test of two files. The script will run on all files in the folder that it is in, if they have a .txt extension.

 

[sheltton]

Nope 750,000 is way too much probably 10 -50 k lines is max.
Clients don't know Linux, we have to get the data sorted into excel and send the files to them.

Will give the script a shot, but vba helps me with the import for the collation of the data from macro2. would help a lot if it was possible.

excel doesn't collate things properly when I import the files to the column and the data in some cell's in A:A get formatted like this when I import the text file with delimited at 255.

eg:
kjsdjkbs ,sdkjbs bsdc. kjsdbk jds
cbsdkcbsksjc. kjjkcbnsdcjnsmdcn.

 

[xenou]

Will give the script a shot, but vba helps me with the import for the collation of the data from macro2. would help a lot if it was possible.

Are you saying your macros work or that they don't work? If the latter, what is not working? I don't know what "collating from Macro2" means, or how this is causing problems for you.

As far as the bash script goes, you obviously don't send your client that ... just the data after the newline characters have been inserted ... if your text files still don't work then the problem is bigger than just inserting the newlines.