Importing Text data using fixed width and delimited

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

sheltton

New Member
Joined
Apr 2, 2008
Messages
21
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
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Apr 2, 2008
Messages
21
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

New Member
Joined
Apr 2, 2008
Messages
21
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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:
Code:
#!/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

New Member
Joined
Apr 2, 2008
Messages
21
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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.
 
Last edited:

Forum statistics

Threads
1,170,960
Messages
5,872,997
Members
432,955
Latest member
minhnhat2504

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top