How to loop through a text file and add a paragraph mark

[ckevinjones]

I have a hugh data base file that I can not open. The header has been corrupted. I can see all the data in the file. I need to add paragraph marks at the end of each record (2412 characters). Once I can do this I can read my data. Please help. Thanks in advance.

Kevin Jones

 

[MiggyD]

And you are using what? Word, Excel, Access, or Other?

Is this a one-time fix you need? or do you have source code?

Are you sure there's "2412 characters" and not bytes?

--MiggyD

 

[ckevinjones]

I believe Word would be the appropriate application to run the code in. But if you have any other suggestions I would love to hear them.

The file is a Text file.

The file is approx 550 megabites. There are approx 150,000 records that are a fixed-with format.Each record is 2412 characters wide.

The problem is there is no record separator.

My solution is to add the record separator.

Therefor I need to loop through the file and after every 2412 characters I need to add a separator In this case a paragraph mark.

I have experience with VB app. for excell but none for word.

I just need a head start. thanks, Kevin Jones

 

[MiggyD]

I, myself, would use Excel 2000 (not sure if 97 has the capability though) and load the file that way then SAVE AS a CVS file.

**I'm on a NON-EXCEL'd computer so this is from memory**
------------------------
ex:
Rename the file's extension to something like .txt

then start excel

click File>Open

change to "All Text Files / *.txt"

click once on file

click on Open/Ok

excel should ask you if the file is delimited, click "NO" (or something similar) then OK

then slide the column with bar over to 2412 characters

click Ok/Accept

After it loads (which could be a while), "SAVE AS" a ".CVS" file with a comma, tab, whatever you want
------------------------


If you need to do this more than once, then in VB it would look something like this:

**this is NOT complete code; more akin to pseudo code, but since you already have some experience then you can fix it to your needs.**
------------------------
DatFile = "dbfile.txt"
OutFile = "Fixed.txt"
open DatFile for input as 11
open OutFile for output as 10
do
DBString = input(#11, 2412)
print #10, DBString + vbCR [or whatever character needed]
loop until eof(11)
close
msgbox "conversion done. Please review: " & vbcrlf & ucase(OutFile)
end
------------------------
The above is generic enough that it should work with in either Word's or Excel's VB IDE. If you have problems with your code, let us know.

--MiggyD

 

[MiggyD]

Any Luck?

--MiggyD

 

[sfvb]

Kevin,

What do you plan to do with this after you add a paragraph mark?

Personally, I would write a VBScript to do this. Word would take forever just to attempt to read this, let alone, accomplish the task you want to perform.

Steve

 

[MiggyD]

I'd like to amend my last post:

I would not use Excel until the file is delimited through the VB code.

for s&g I did a test simulation on a non-delimited file through excel...not too pretty. Do the 'spliting' code first then you can use excel (if you want to).

 

[MiggyD]

Sorry, ment to add this to the one above:

If you still need the code, I can provide it--since I had to create it for the test. It's similar to what I posted earlier.

But that's all that it is, it's just code. You'll have to create a status box so that you don't think it has stopped working (or run it when you leave work so it'll be done for you in the morning).

Let me know if you need it.


To sfvb:

As for speed, it should take about the same amount of time in both VBA as in VBS. You only need to have a BLANK SHEET, in either Word or Excel, to create a macro and shove in some code.

VB would be parsing the file, not Word; so Word would not be loading the file at all--therefore, resources should remain at maximum availability.

 

[sfvb]

Here's a VBScript that will create a new file with the paragraph mark after every 2412 characters. Just change the filenames appropriately, and save the file with a vbs extention. Ex: alterfile.vbs

Before you run it, make sure you have enough disk space for the new file. (It took just less than 3 minutes to complete on a 535M file.)

Dim fso, filR, filW
      
Set fso = CreateObject("Scripting.FileSystemObject")
Set filR = fso.OpenTextFile("c:\FileToRead.ext", 1, False)
Set filW = fso.OpenTextFile("c:\FileToCreate.ext", 2, True)
   
Do While Not filR.AtEndOfStream
   filW.Write filR.Read(2412) & vbCrLf
Loop

filW.Close
filR.Close
MsgBox "done"

Steve