19 Replies Latest reply: May 15, 2014 9:59 AM by Nick Osdale-Popa RSS

    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?

        • Using the pipe character ( | ) as a 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

          • Using the pipe character ( | ) as a trap
            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? /b[/quote]

             

            [size="1"][ August 13, 2003, 05:38 PM: Message edited by: Grant Perkins ][/size]

            • Using the pipe character ( | ) as a trap
              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 /b[/quote]

              • Using the pipe character ( | ) as a trap
                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|...

                • Using the pipe character ( | ) as a trap
                  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|... /b[/quote]

                  • Using the pipe character ( | ) as a trap
                    Nick Osdale-Popa

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

                    [font="courier"]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 posting[/font][/quote]The Job is text, but Monarch is reading it as numeric.  The model is not allowing me to change it.

                     

                    Any other ideas?

                    • Using the pipe character ( | ) as a trap
                      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:

                      [font="courier"]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 posting[/font][/quote]The Job is text, but Monarch is reading it as numeric.  The model is not allowing me to change it.

                       

                      Any other ideas? /b[/quote]

                      • Using the pipe character ( | ) as a trap
                        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[/img]    !!!  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.

                        • Using the pipe character ( | ) as a trap
                          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.

                           

                          /b[/quote]

                           

                          [size="1"][ August 19, 2003, 04:20 AM: Message edited by: Grant Perkins ][/size]

                          • Using the pipe character ( | ) as a trap
                            Nick Osdale-Popa

                            Hehehehe.... not so fast Tom!    

                             

                            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.)

                            [font="courier"]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 Function[/font][/quote]Is 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. 

                            • Using the pipe character ( | ) as a trap
                              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.

                               

                              [url="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetsdk_98.asp"]here[/url]  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:

                              [font="courier"]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 posting[/font][/quote]The Job is text, but Monarch is reading it as numeric.  The model is not allowing me to change it.

                               

                              Any other ideas? /b[/quote]

                               

                              [size="1"][ August 19, 2003, 10:59 AM: Message edited by: Gareth Horton ][/size]

                              • Using the pipe character ( | ) as a trap
                                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:

                                  [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000133#000000"]http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000133#000000[/url] 

                                 

                                which lead to this Monarch Tip in the Feb. 2003 issue of The Monarch Report (see Page 6):

                                  [url="http://www.datawatch.com/pdf/products/monarch/Feb03_Report_Final.pdf"]http://www.datawatch.com/pdf/products/monarch/Feb03_Report_Final.pdf[/url]

                                 

                                [size="1"][ February 09, 2006, 09:33 AM: Message edited by: Mary Lou Sullivan ][/size]

                                • Using the pipe character ( | ) as a trap
                                  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

                                  • Using the pipe character ( | ) as a trap
                                    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 /b[/quote]

                                    • Using the pipe character ( | ) as a trap
                                      Nick Osdale-Popa

                                      [b]Voodoo Monarch[/b][/i] to the rescue!!!

                                       

                                      [font="courier"]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) /font[/quote]The only minor adjustments that would need to be made are the Data Lengths for TrapLine, Comments, and Notes.

                                      • Using the pipe character ( | ) as a trap
                                        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[/img]  

                                         

                                        Grant

                                         

                                         

                                          Originally posted by Nick Osdale-Popa:

                                        [b]Voodoo Monarch[/b][/i] 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. /b[/quote]

                                         

                                        [size="1"][ September 09, 2003, 11:52 AM: Message edited by: Grant Perkins ][/size]

                                        • Using the pipe character ( | ) as a trap
                                          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.  

                                          • Using the pipe character ( | ) as a trap
                                            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.)

                                             

                                            [size="1"][ September 10, 2003, 10:22 PM: Message edited by: Tom Whiteside ][/size]

                                            • Using the pipe character ( | ) as a trap
                                              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.