Fixed Width File Editor

[djwatts]

Hi guys,

I'm getting loads of txt files in fixed width format, many of which need positions modifying slightly prior to loading.

Anyone know of a small app that can easily edit these files? Thought I would post in the VB forum as us developers always have handy little apps to help us with our work. + I Would be interested in any source code if there's anything like this out there written in VB

Thanks in advance

 

[BobRodes]

Either you don't like notepad, or I don't understand your question. In any case, you might want to check out TextPad.

HTH

Bob

 

[petermeachem]

I use pfe, works well

http://www.lancs.ac.uk/staff/steveb/cpaap/pfe/

 

[tsh73]

if you think that files could be modified automatically (in more or less uniform manner) you can look to sed or awk.

 

[djwatts]

Hi,

btw Notepad rules! However I think what i really need is an app that can easily move the posistion of fields in all rows in a fixed width file instead of having to do this individually to some 20,000 rows. Anyone know of a gui app that can do this, I don't really want to start using unix programs to do this???

Thanks

 

[petermeachem]

Pfe does macros, as in recorded series of keystrokes.
Why not write something in vb? If you can state teh rules for editing it should be easy enough.

 

[djwatts]

I think i'm going to have to find a way of working with PFE seems a pretty good editor. The way files need to be changed are different each time so a vb app would need lots of editing each time. Thanks for the help guys

 

[Swi]

You could try UltraEdit:

http://www.ultraedit.com/

Or EditPad:

http://www.editpadpro.com/

Swi

 

[dilettante]

What about a script and ADO plus Jet driven by a Jet Text Schema file?

Example text orig.txt

00100Horse     Y
02986Dog       N
00007Elephant  Y
01110Eel       Y
99173Cat       N

Example script SwapFixed.wsf

<job>
  <object id = objCmd progID = "ADODB.Command"/>
  <reference object = "ADODB.Command"/>
  <object id = objFSO progID = "Scripting.FileSystemObject"/>
  <reference object = "Scripting.FileSystemObject"/>
  <!-- Correct the schema below to reflect your files'
       actual fields and their arrangements in the
       original and new-format files.
  -->
  <resource id="resSchema">
    [$OrigFile$]
    ColNameHeader=False
    MaxScanRows=1
    Format=FixedLength
    Col1=Item   Text Width 5
    Col2=Animal Text Width 10
    Col3=Needed Text Width 1
    [$NewFile$]
    ColNameHeader=False
    MaxScanRows=1
    Format=FixedLength
    Col1=Needed Text Width 1
    Col2=Animal Text Width 10
    Col3=Item   Text Width 5
  </resource>
  <resource id = resConnString>
    Provider=Microsoft.Jet.OLEDB.4.0;
      Extended Properties="Text";
      Data Source="$DataPath$"
  </resource>
  <resource id = "resSQL">
    SELECT * INTO [$NewFile$]
             FROM [$OrigFile$]
  </resource>
  <script language = "VBScript">
    Option Explicit

    Const kstrMyName = "Rearrange fixed fields"
    Dim tsSchema
    Dim strSFN
    Dim strDataPath
    Dim strOrigFile
    Dim strNewFile
    Dim lngRecords

    Function Resource(ByVal ResName)
      'Fetch resource string, perform text substitutions,
      'return the result.
      Resource = _
        Replace(Replace(Replace(getResource(ResName), _
                                "$NewFile$", strNewFile), _
                        "$OrigFile$", strOrigFile), _
                "$DataPath$", strDataPath)
    End Function

    'Use script's path as our data path in this example.
    strSFN = WScript.ScriptFullName
    strDataPath = Left(strSFN, InStrRev(strSFN, "\") - 1)

    'Set original and new filenames.  Do NOT try to use
    'filenames like out, output, in, input, in.txt, etc.
    'these are illegal here.
    strOrigFile = "orig.txt"
    strNewFile = "new.txt"

    'Create schema file, overwriting any already present.
    Set tsSchema = _
      objFSO.CreateTextFile(strDataPath & "\schema.ini", _
                            True)
    tsSchema.Write Resource("resSchema")
    tsSchema.Close
    Set tsSchema = Nothing

    'Open connection and perform the SQL operation.
    objCmd.ActiveConnection = Resource("resConnString")
    objCmd.CommandText = Resource("resSQL")
    objCmd.Execute lngRecords, , adCmdText Or adExecuteNoRecords

    'Delete the schema file, we're done with it.
    objFSO.DeleteFile strDataPath & "\schema.ini", True

    'Signal completion.
    MsgBox "Done! File contains " & CStr(lngRecords) _
         & " records.", 0, kstrMyName
  </script>
</job>

The schema can be maintained outside the script or as a resource embedded in the script source as shown here.

Example result new.txt

YHorse     00100
NDog       02986
YElephant  00007
YEel       01110
NCat       99173

This allows for things like sorting, possible field transformations such as truncating or padding, or perhaps fancier operations using Jet VBA in the SQL statement.

 

[macleod1021]

It's difficult to help without more information. Just saying that the orig. files are fixed width doesn't give much information. If it's something that can be determined by information in the file, then you could use a streamreader/streamwriter to work with the data. You'd need to either put the information to look for in an ini file or hard code it so your app knows what to do.

However; if the edits can't be easily determined by the app then you'll need to do it with notepad.

 

[dilettante]

Well I was getting at either manually setting up the input and output layouts and schemas once for each "conversion," setting up some sort of database of such schemas and pulling from it by name/number/etc., or providing a GUI to build the schema on an ad hoc basis.

Basically take the blob of code I listed above, wrap it in a GUI of some sort, and compile it up into a tool. Whether you do this with Jet or hand-coded logic is irrelevant unless you need to offer transformational flexibility that Jet SQL already provides you. Then it may beat developing hand-coded logic and a transformation language, etc.

This is so much like Office Text Import/Export Wizards I'd be surprised if you can't do it using MS Access already. For that matter you'd think one or more tools for this sort of thing would be floating around already.

I suppose people use things like SQL Server DTS too, but that's overkill for just reformatting text files.