Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

Create a loop on a Notepad file

I have a Notepad file that at column 161 starts a new record.  What I need is to insert a return at column 160 for each row so the record is in the correct position.  Basically, every row really has two rows of data on it.  Of course, I need the code to then go to the next row and insert a return again at 160.  However, can VBA be used against a Notepad file?  What language would be used and can this even be done?
Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This depends on the file contents. Is it ASCII (versus being UTF16 / "Unicode") indeed?
In general, you open the text file, read a line, and write the first 160 chars as a line to a new file, then write the next 160 chars.

In PowerShell (which is most simple here) this would look this:
$file = C:\Temp\OriginalFile.txt
(get-content $file) | % {
  Write-Output $_.SubString(0, 160)
  Write-Output $_.Substring(160)
} | Out-File -Encoding ASCII $file

Open in new window

It is very similar in VBA.
Avatar of Sandra Smith


It is a text file. Will try both suggestions and see what happens
I have all of MS Office and want this data to end up in an ACCESS 2010 database.
Do you want to have each (new) line as an own row in Access? In that case I would combine all processes required, and use VBA code similar to above.
With a slight modification, this works perfectly, thank you.