Using the pipe character ( | ) as a trap

[Nick Osdale-Popa]

Okay, I got a tough one here:
I have a datafile that is deliminated with the pipe character ( | ). How do I use that as a trap if that's also the character used for the Numeric Or trap?

[tcorley]

Good Question...

I often run into "|" delimited files it would be great to use "|" for a floating trap.

Datawatch please change the "or" trap character.

Thanks

[Grant Perkins]

My preference (being a Pro version user) is to read the files as a database deliminated by the |.

Another option would be to change the | to something else (MSRP? or an editor - sounds like it could be a Unix originated file? so maybe vi it to change it to something else?) then work with the new character for the trap.

Grant


Originally posted by Nick Osdale-Popa:
Okay, I got a tough one here:
I have a datafile that is deliminated with the pipe character ( | ). How do I use that as a trap if that's also the character used for the Numeric Or trap?

[ August 13, 2003, 05:38 PM: Message edited by: Grant Perkins ]

[Grant Perkins]

TC,

I used to deal with a lot of | delimited files (much less ambiguous that csv). Can't think of many where I could have successfully used the | as a floating trap. The variable field sizes would cause problems surely?

Grant


Originally posted by tcorley:
Good Question...

I often run into "|" delimited files it would be great to use "|" for a floating trap.

Datawatch please change the "or" trap character.

Thanks

[tcorley]

You are correct,

Loading as a CSV or using search and replace are things that I have done.

I have never come across the characters; Ã,Ñ,ß,Ø (the other trap chars) in parts of files that contained information that "I needed". For me these characters have always been obscure enough to be usable. However the "|" I see all the time as a great trap character.

example (floating trap on the pipe would work great here):
Pipe delimited file 1 record per line
tcorley|112 SOME STREET||NY|NEW YORK|90210|...
tcorley2|1102 SOME AVE|APT2|NY|NEWYORK|90210|...

also
1 Field per line (Same number of lines per Record)
* note ... line extends to more records
* items on line 4
* Coresponding amounts for items on line 5
tcorley
112 Some Street|
NY|NEW YORK|90210
item1|item2|item2|item4|...
10.00|0.20|3.00|4.00|...
tcorley2
1102 SOME AVE|APT2
NY|NEW YORK|90210
item1|item2|item2|item4|...
10.00|0.20|3.00|4.00|...

[Grant Perkins]

TC,

I agree that it seems to be a bit of anomaly that other trap characters can be remapped to something else (due to language issues) via the registry entries but | cannot. There may be a good reason. Mike, Gareth - any observations to help us out here?

However I still have problems with ANY trap character used as a floating trap on a non fixed field length file. Unless the record is in 2 parts and only the first part is of variable length and the rest is treated as a fixed (or maximum) length field.

Or in your example split into multiple lines, where each line is treated as a separate entity with its own template AND the first field of each line is of variable length but where the rest are fixed and in fixed positions relative to each other.

Floating traps can be very very powerful for specific requirements but, quite reasonably, have some clear limitations.

My preference would be to let the software do the work for you and read the file as a database. (If using Monarch Pro.)Unless there was something in the Voodoo Monarch session that blows my rationale out of the water?

Other than that the only thing I can think of is doing what you have done before - substitute the | for another character that is easier to spot than TAB and less likely to be in the text than ",".

Have I missed a trick somewhere?

Grant



Originally posted by tcorley:
You are correct,

Loading as a CSV or using search and replace are things that I have done.

I have never come across the characters; Ã,Ñ,ß,Ø (the other trap chars) in parts of files that contained information that "I needed". For me these characters have always been obscure enough to be usable. However the "|" I see all the time as a great trap character.

example (floating trap on the pipe would work great here):
Pipe delimited file 1 record per line
tcorley|112 SOME STREET||NY|NEW YORK|90210|...
tcorley2|1102 SOME AVE|APT2|NY|NEWYORK|90210|...

also
1 Field per line (Same number of lines per Record)
* note ... line extends to more records
* items on line 4
* Coresponding amounts for items on line 5
tcorley
112 Some Street|
NY|NEW YORK|90210
item1|item2|item2|item4|...
10.00|0.20|3.00|4.00|...
tcorley2
1102 SOME AVE|APT2
NY|NEW YORK|90210
item1|item2|item2|item4|...
10.00|0.20|3.00|4.00|...

[Nick Osdale-Popa]

Okay, tried pulling in the file as a database, the file looks as such:

Job|Phase|CostCode|HrsToComp|HrsAtComp|DlrsAtComp| comments|note
01.161|55|CLNP|12.0|47.5|1212.99|-: Need to add more hours |Call before posting
01.161|55|DUST|13.0|30.0|942.76||Call before posting
01.161|55|MATL|100.0|154.0|3315.51|-: Overstatement of hours|Call before postingThe Job is text, but Monarch is reading it as numeric. The model is not allowing me to change it.

Any other ideas?

[Grant Perkins]

Good point Nick.

Somehow I remember this problem occuring from time to time. Don't think I ever worked a full resolution that I was entirely happy with.

Ideally it should be possible to simply redefine the field to a character field and save the model accordingly. One for the future?

The workaround is to create a calculated field to convert the data to character and use that.

V7 I reckon I would use STR(JOB;6;3;"0")which works based on the sample lines.

As observed above, I would be much happier if the field could simply be redefined in the model after the first importation.

Does this help?

Grant



Originally posted by Nick Osdale-Popa:
Okay, tried pulling in the file as a database, the file looks as such:

Job|Phase|CostCode|HrsToComp|HrsAtComp|DlrsAtComp| comments|note
01.161|55|CLNP|12.0|47.5|1212.99|-: Need to add more hours |Call before posting
01.161|55|DUST|13.0|30.0|942.76||Call before posting
01.161|55|MATL|100.0|154.0|3315.51|-: Overstatement of hours|Call before postingThe Job is text, but Monarch is reading it as numeric. The model is not allowing me to change it.

Any other ideas?

[Tom Whiteside]

Nick, Todd, Grant,

I work with HUGE pipe-delimited data files just like Nick's. The only consistent way I have found to deal with them is to :eek: (Gasp!) temporarily put Monarch aside, and make use of a little known feature of the MS Access file import wizard (or, the link text wizard). Excel even has a parallel feature, though it is not as efficient.

From within your "home" Access database, go to File - Get External Data - Import (activating the file import wizard) - - or, start fresh and go to File - Open (choosing your text file will activate the link text wizard). I prefer the file import option simply from familiarity.

After you reach the first wizard screen, choose "Delimited." Then - - and here's the secret ;) - - click on "Advanced" in the lower left corner of the screen. The screen that opens up should look familiar to the open database function within Monarch. Once again, choose "Delimited" and then overtype the Field Delimiter box with the pipe symbol. Check that the Date, Times, and Numbers options are set correctly. Here's the important part smile.gif !!! Before you hit OK - - which would continue the import process - - hit Save As and save your Access "import model." Now, whenever you have to import the same type of data file, simply come to the first wizard screen, hit Advanced, and then hit Specs. The Specs screen lists all the Access "import models" you have created, and lets you select the one for your file. Hit OK and then finish the import wizard. In answer to Nick's problem with the Monarch model reading the text Job field as numeric, the Access import wizard will let you choose format for each field as you go - - or change format afterwards.

I have used MS Access for years, but this feature was unknown to me until last year. In this particular case, and a few similar ones, Access has the capability to mimic Monarch well enough to avoid this delimiter problem. Again, even Excel has a similar "Advanced" file import option.

Nick, you owe me a stake in Las Vegas for this one - - maybe a small portion of the millions you left the Vegas Conference with?

Let me know if any problems or questions.

[Grant Perkins]

Hi All,

Tom's suggestion is very reasonable especially if you are dealing with a VERY large file since it is my impression (in V7 at least) that linking to an Access database offers a faster process option than linking to a ascii file where a significant number of calculated fields and other external links are used. (Excel has the functions but not necessarily the record capacity if we are talking about large files). Please note that this is only an impression as I have not had the time (it takes a while with the large files I have available!!!) to work out and run a full back-to-back test for Access vs ascii file imports when using very large numbers of calulated fields and lookups.

On the other hand, if such extended features are not going to be used, direct reading into Monarch (from V6 Pro, Pro versions only) is an option. Simply specify the separator as "Other" and enter the | in the box provided at the appropriate point in the process. This route still suffers the limitation discussed previously about the way any fields with embedded periods ( . )are assumed to be numeric.

If this functional area in Monarch could be brought into line with existing Access/Excel funtionality, even if it had to be via a saved model file rather than at import time, there would be no need for the calculated field fudge.

Tom has highlighted a couple of interesting features from Access that are good to have in the armoury. It is also worth considering how often any particular file format will be re-used. If it is more than a one off it may well be worth creating an Access database anyway and simply clearing (or not) the old one before each import of a new file.


Originally posted by Tom Whiteside:
Nick, Todd, Grant,

I work with HUGE pipe-delimited data files just like Nick's. The only consistent way I have found to deal with them is to :eek: (Gasp!) temporarily put Monarch aside, and make use of a little known feature of the MS Access file import wizard (or, the link text wizard). Excel even has a parallel feature, though it is not as efficient.



[ August 19, 2003, 04:20 AM: Message edited by: Grant Perkins ]

[Nick Osdale-Popa]

Hehehehe.... not so fast Tom! :D

Yes, I'm well aware of Access' stored Import Specifications, I've used it since V2.0. Since I eventually want to be emailing the imported information in an Excel spreadsheet, I wanted to stick with that App. You've seen what I can do with Excel (the Datawatch guys likened it to a desktop version of Datapump ;) )

I *found a bit of code that does the trick, using ActiveX Data Objects and the Windows Scripting Runtime Engine:

(there are some bits of code in here referencing global objects and functions, but the meat of the information is given.)

Option Explicit
'Set References to:
' - Microsoft Scripting Runtime
' - Microsoft ActiveX Data Objects 2.6+ Library

Private wbMain As Workbook
Private wsMain As Worksheet
Private rngMain As Range

Sub OpenDataFile()
Dim intIdx As Integer
Dim intCnt As Integer
Dim strFilename As String
CurrProc = "OpenDataFile"

SetDirectories
Set colFTPFiles = New Collection
Set wbMain = Workbooks.Add
intCnt = wbMain.Worksheets.Count
If intCnt > 1 Then
AlertsOff
For intIdx = 2 To intCnt
wbMain.Worksheets(2).Delete
Next intIdx
AlertsOn
End If
Set wsMain = wbMain.Worksheets(1)
Set rngMain = wsMain.Range("A1")

On Error GoTo OpenDataFile_Error
'[
strFilename = Dir(SOURCEDIR & "" & "WeeklyLabor*.txt")
Do While Len(Trim(strFilename)) <> 0
colFTPFiles.Add Item:=strFilename
strFilename = Dir()
Loop
intCnt = colFTPFiles.Count
If intCnt = 0 Then
Err.Raise vbObjectError + 1, , "No uploaded files found in:" & SOURCEDIR
End If
']
For intIdx = 1 To intCnt
strFilename = colFTPFiles(intIdx)
If ImportDSV(strFilename) = False Then
Err.Raise vbObjectError + 1, , "Couldn't Open " & strFilename
End If
Next intIdx
wsMain.Cells.EntireColumn.AutoFit
wsMain.Range("A1").Select

AlertsOff
wbMain.SaveAs DESTINATIONDIR & "" & EXPORTFILE
AlertsOn

OpenDataFile_Exit:
With wbMain
.Saved = True
.Close
End With
Set rngMain = Nothing
Set wsMain = Nothing
Set wbMain = Nothing
Exit Sub

OpenDataFile_Error:
WriteToLog CurrProc, Err.Description
Set colFTPFiles = Nothing
Resume OpenDataFile_Exit
End Sub

Private Function ImportDSV(strFilename As String) As Boolean
Dim rsRecordSet As New ADODB.Recordset
Dim fldDataField As ADODB.Field
Dim FSO As New Scripting.FileSystemObject
Dim strmInput As Scripting.TextStream
Dim aryValues As Variant
Dim strLine As String
Dim strData As String
Dim lFld As Long
Dim recCount As Long
Dim lRow As Long
Dim iCol As Integer
Dim fldCount As Integer
Dim PM As String

PM = Extract(strFilename, "_")
'Define the fields in the recordset
With rsRecordSet.Fields
.Append "PM", adVarChar, 30
.Append "Job#", adVarChar, 10
.Append "Phase", adVarChar, 4
.Append "Cost Code", adVarChar, 10
.Append "Hours To Complete", adDouble
.Append "Hours At Complete", adDouble
.Append "Dollars At Complete", adDouble
.Append "Comments", adVarChar, 100
.Append "Note", adVarChar, 100
End With
rsRecordSet.Open

'Open the file and read it into the recordset
On Error GoTo ImportError
Set strmInput = FSO.OpenTextFile(SOURCEDIR & "" & strFilename, ForReading)

While Not strmInput.AtEndOfStream
recCount = recCount + 1
strLine = strmInput.ReadLine

'Make sure the line is not blank
If strLine <> "" Then
aryValues = SplitIt(strLine, "|")
If recCount > 1 Then
rsRecordSet.AddNew
rsRecordSet.Fields(0).Value = PM
For lFld = 1 To rsRecordSet.Fields.Count - 1
rsRecordSet.Fields(lFld).Value = aryValues(lFld - 1)
Next
End If
End If
Wend
strmInput.Close
Set strmInput = Nothing
Set FSO = Nothing

If rngMain.Row = 1 Then
For Each fldDataField In rsRecordSet.Fields
'Put the field names in the first row of the sheet
rngMain = fldDataField.Name
'format the column based on the data type
With wsMain.Columns(rngMain.Column)
Select Case fldDataField.Type
Case adDate
.NumberFormat = "mm/dd/yyyy"
Case adDouble
.NumberFormat = "#,##0.00"
.HorizontalAlignment = xlRight
Case adInteger
.NumberFormat = "#,##0"
.HorizontalAlignment = xlRight
Case Else
End Select
End With
'move over one cell
Set rngMain = rngMain.Cells(1, 2)
Next fldDataField
Set fldDataField = Nothing
End If


'Dump the recordset
rsRecordSet.MoveFirst

If Val(Mid(Application.Version, 1, InStr(1, Application.Version, ".") - 1)) > 8 Then
'EXCEL 2000 or 2002: Use CopyFromRecordset

' Copy the recordset to the worksheet, starting in cell A2
rngMain.CopyFromRecordset rsRecordSet
'Note: CopyFromRecordset will fail if the recordset
'contains an OLE object field or array data such
'as hierarchical recordsets

Else
'EXCEL 97 or earlier: Use a LOOP to transfer the fields
Do While Not rsRecordSet.EOF
lRow = rngMain.Row
'move to next row
Set rngMain = wsMain.Cells(lRow + 1, 1)
For Each fldDataField In rsRecordSet.Fields
If Len(fldDataField) > 0 Then rngMain = fldDataField
Set rngMain = rngMain.Cells(1, 2)
Next fldDataField
rsRecordSet.MoveNext
Loop
rsRecordSet.MoveFirst
End If

rsRecordSet.Close
ImportDSV = True

'a little touch up work
ImportExit:
Set rsRecordSet = Nothing
Exit Function
ImportError:
ImportDSV = False
Resume ImportExit
End Function

Private Function SplitIt(InString As String, strChar As String) As Variant
Dim arrayReturn() As Variant
Dim intLen As Integer
Dim intPos As Integer
Dim intCount As Integer
Dim strTemp As String
Dim strElement As String

strTemp = InString
Do
intPos = InStr(strTemp, strChar)
If intPos <> 0 Then
ReDim Preserve arrayReturn(intCount)
strElement = Mid(strTemp, 1, intPos - 1)
If Left(strElement, 1) = Chr$(34) Then
strElement = Mid(strElement, 2, Len(strElement) - 2)
End If
arrayReturn(intCount) = Trim(strElement)
Else
ReDim Preserve arrayReturn(intCount)
strElement = strTemp
If Left(strElement, 1) = Chr$(34) Then
strElement = Mid(strElement, 2, Len(strElement) - 2)
End If
arrayReturn(intCount) = Trim(strElement)
End If
strTemp = Mid(strTemp, intPos + Len(strChar))
intCount = intCount + 1
Loop While intPos > 0
SplitIt = arrayReturn
End Function

Private Function Extract(InString As String, StartChar As String, Optional EndChar As String = "")
Dim intStart As Integer
Dim intEnd As Integer
If EndChar = "" Then EndChar = StartChar
intStart = InStr(InString, StartChar)
If intStart = 0 Then
Extract = InString
Exit Function
End If
intEnd = InStr(intStart + 1, InString, EndChar)
If intEnd = 0 Then
Extract = Mid(InString, intStart + 1)
Exit Function
End If
intEnd = (intEnd - 1) - intStart
Extract = Mid(InString, intStart + 1, intEnd)
End FunctionIs it more involved than a simple Access Import spec? Yes. But it keeps everything nice and tidy in one application. This could also have been done by automating Access, but that just get ugly too.

I'd run into the same bit of problem with the Job # if I exported the data from Access into Excel, at least not without automating Excel to accomodate the export.

*I'm not the original author of the ImportDSV routine (originally called ImportCSV). It was originally written for Excel2000+, using it's new Split Function and CopyFromRecordset (from and Access Data Object). I had to create my own code to write the SplitIt() function and Extract() function.

PS: I so wanted to accomplish this in Monarch, but in this situation, I had to ignore it. :(

[Gareth Horton]

Nick

Unfortunately, we are not likely to allow the pipe character as a trap character in the future.

Opening the file as a database is the way to go.

The problem with some of the behavior of the database import routine is that we are using Microsofts Jet text import engine to do this, and it has some limitations.

The import engine makes assumptions based on its own inscrutable logic and the only way to override these is essentially before the import takes place.

This is the problem you are seeing in that you cannot tell the engine what data type is desired.

After an import, you may notice a file called schema.ini gets created in the same folder as the database you are importing.

In order to forcefeed the import engine with the metadata, you can create your own custom schema.ini with the fields and data types pre-specified. This must reside in the same folder as the import file.

here (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp) is a link to the MS article on how these are structured.

We are very likely to replace the engine for importing text with our own custom engine in the future, to avoid these niggles.

Let me know if you have problems getting this working.

Gareth



Originally posted by Nick Osdale-Popa:
Okay, tried pulling in the file as a database, the file looks as such:

Job|Phase|CostCode|HrsToComp|HrsAtComp|DlrsAtComp| comments|note
01.161|55|CLNP|12.0|47.5|1212.99|-: Need to add more hours |Call before posting
01.161|55|DUST|13.0|30.0|942.76||Call before posting
01.161|55|MATL|100.0|154.0|3315.51|-: Overstatement of hours|Call before postingThe Job is text, but Monarch is reading it as numeric. The model is not allowing me to change it.

Any other ideas?

[ August 19, 2003, 10:59 AM: Message edited by: Gareth Horton ]

[Mike Urbonas]

Gareth is absolutely right, using Monarch Pro and opening a pipe-delimited file as a database is the way to go.

See also this similar Q&A:
http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000133#000000

which lead to this Monarch Tip in the Feb. 2003 issue of The Monarch Report (see Page 6):
http://www.datawatch.com/pdf/products/monarch/Feb03_Report_Final.pdf

[ February 09, 2006, 09:33 AM: Message edited by: Mary Lou Sullivan ]

[Arne]

Hi,
I am thinking of following Mike's advice and re-do some of my models to read the file as a database deliminated by the |. This would help immensely, as it takes care (I hope) of possible layout changes, e.g. a column becoming longer, but still separated by |).

Now my question: my existing model makes intensive use of filters, look-up tables, calculated fields, hiding and shifting columns etc. I tried to use the original model when opening the database, but it failed.

Is there a way to copy or cut and paste from the old to the new model? I don't want re-invent the wheel.
Tschuess Arne

[Grant Perkins]

Arne,

I fully recommend the change to using the file as a database for the very reason you mention BUT I do not think you will have much success trying to cut and paste things from one model file to the next for various reasons. Out of interest I did try that in the past but, whilst it sort of almost worked in some ways, at the end it was a waste of time. However, if anyone knows a way around that OR knows that it might work for some things and not others it would be interesting to hear about it.

What you should be able to do is cut and paste the definitions of each formula, filter, look-up and so on from the original model to the new one.

I have done that very successfully in similar circumstances by running 2 sessions side by side. When reproducing the field in the new model simply cut and past on a field by field basis as you create the new entry.

For some field it may be quicker to simply re-type the entry, but for complex formulae of large lookup tables the C and P route is surely the way to go.

Moves and hides presumably need to be set up manually.

On the other hand Gareth, Mike or Steve may have some other suggestions ... ?

Grant



Originally posted by Arne:
Hi,
I am thinking of following Mike's advice and re-do some of my models to read the file as a database deliminated by the |. This would help immensely, as it takes care (I hope) of possible layout changes, e.g. a column becoming longer, but still separated by |).

Now my question: my existing model makes intensive use of filters, look-up tables, calculated fields, hiding and shifting columns etc. I tried to use the original model when opening the database, but it failed.

Is there a way to copy or cut and paste from the old to the new model? I don't want re-invent the wheel.
Tschuess Arne

[Nick Osdale-Popa]

Voodoo Monarch to the rescue!!!


Name Type Data Length Display Length Decimals Alignment Formula
TrapLine C 100 67 L
Job C 20 20 L LSplit(TrapLine,8,"|",1)
Phase C 20 5 L LSplit(TrapLine,8,"|",2)
CostCode C 20 5 L LSplit(TrapLine,8,"|",3)
HrsToComp N 18 10 1 R Val(LSplit(TrapLine,8,"|",4))
HrsAtComp N 18 9 1 R Val(LSplit(TrapLine,8,"|",5))
DlrsAtComp N 18 10 2 R Val(LSplit(TrapLine,8,"|",6))
Comments C 20 18 L LSplit(TrapLine,8,"|",7)
Notes C 20 16 L LSplit(TrapLine,8,"|",8) The only minor adjustments that would need to be made are the Data Lengths for TrapLine, Comments, and Notes.

[Grant Perkins]

So that is what Voodoo Monarch is! :cool:

Have used that before (before V5 Pro I think therefore before the ability to read a delimited file as a database IIRC. Proabably back in V4 days) but not since I had access to the the Pro features. So had forgotten it! Used with space and | delimited files as I recall but I can't find the examples at this moment. However I think the files had relatively few fields we wanted to pick whereas the later stuff would run to 40 or 50 fields and became a bit of a 'mare to think about unless using the database facility.

Now this route, when compared to the database rooute, means you have to create 8 calculated fields rather than 1 (or is it 2? Perhaps the first 2 calculations would otherwise be automatically set to numeric?). So what now interests me is what additional factors in your overall process leads you to prefer the Voodoo solution to the database option? (Other than the fact that it would work for Standard users as well as far as I can see).

Come on Nick, spill the beans! I'm intrigued. smile.gif

Grant



Originally posted by Nick Osdale-Popa:
Voodoo Monarch to the rescue!!!

<snip - code sadly removed due to excessive width when part of a quote section >

The only minor adjustments that would need to be made are the Data Lengths for TrapLine, Comments, and Notes.

[ September 09, 2003, 11:52 AM: Message edited by: Grant Perkins ]

[Nick Osdale-Popa]

As I stated above, the database option read in the Job number as numeric, thus requiring a calculated field to to append the leading zeros and converting it to a string. The same would have to be done with the Phase and Cost Code too.
Gareth pointed out that a schema.ini file is created which can be modified to account for this, but then that's another file you have to worry about updating.

Besides, this gave me a reason to try some of the more daring techniques in creating the table, and as you pointed out, this does help those with Standard versions. :D

[Tom Whiteside]

Nick,

This may lead you back to a "D'Oh!" but how does your Monarch solution differ - - really - - from using the Excel Advanced Import Option? You mentioned before that you wished to keep the end result in an Excel format for e-mail. We already hashed through the good and bad points of the Access Advanced Import, but your listing of your display widths, field lengths, and data types looks (to me) almost indistinguishable from the same setup in an Excel Import template.

Am I missing something? Is there a reason to bring in a middle step between report and your desired Excel file output?

After all - - this is the way we do it in The O.C., Nick! (Sorry - - couldn't resist.)

- - Lurking in Lake Forest (The O.C.)

[ September 10, 2003, 10:22 PM: Message edited by: Tom Whiteside ]

[Nick Osdale-Popa]

No, not much different, and my Excel solution above does take care of it. I was just looking for the Monarch Solution, for those who may have the same problem, but don't need/want to have the results in Excel.