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?

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

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.

Sandra Smith

ASKER

It is a text file. Will try both suggestions and see what happens

Sandra Smith

ASKER

I have all of MS Office and want this data to end up in an ACCESS 2010 database.

Qlemo

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.

Sandra Smith

ASKER

With a slight modification, this works perfectly, thank you.