7 Replies Latest reply: May 15, 2014 10:04 AM by Terry007 _ RSS

    batch extract of fields in comma delimited file to spreadsheet or access

    Terry007 _

      I have each month a different comma delimited file for various customers that I need to extract data (set up a mask for each) and then output to either excel or access.  I need to do this in an automated way (batch like).  Will Monarch allow me to do this or do I need something else.  This will be on an XP system for now and eventually Vista (or it's replacement) and Excel 2007/Access 2007.

       

      Thanks for the feedback.

       

      Terry

        • batch extract of fields in comma delimited file to spreadsheet or access
          Data Kruncher

          Hi Terry,

           

          [URL="http://datawatch.com/_products/monarch_pro.php"]Monarch Pro V10[/URL] will fit the bill nicely for your requirements.

           

          It runs on both XP and Vista, and with Datawatch's status with Microsoft is bound to support later OS versions as well.

           

          Extensive [URL="http://********************/tips/monarch-batch-file-generator"]command line parameters[/URL] offer basic automation, and a COM interface permits more [URL="http://********************/tips/monarch-programming-class"]advanced control[/URL] over automation.

           

          Throw in [URL="http://********************/tips/new-year-new-features"]support for Office 2007 file formats[/URL] and you're good to go.

           

          I've been using Monarch for seven years now, and I'm sure that once you discover and take advantage of Monarch's many benefits, you'll wonder how you got along without it.

           

          HTH,

          Kruncher

            • batch extract of fields in comma delimited file to spreadsheet or access
              Terry007 _

              Thanks for your input, I was not sure if I needed the data pump.  Frankly I'm not sure when I would use the data pump.

               

              T

                • batch extract of fields in comma delimited file to spreadsheet or access
                  Olly Bond

                  Hi Terry,

                   

                  Monarch is almost completely automatable via command line or COM interfaces, as Kruncher says. There is a hefty difference in price between Monarch and DataPump (about 1:10 as far as I recall) and it might be helpful to bear in mind the differences.

                   

                  1. Licence

                   

                  Monarch is licenced for installation on one machine, for use by one user. If you need multiple users to control Monarch, then either a network licence of Monarch or a single licence of DataPump (which offers a remote admin interface as well as a web interface) is the solution.

                   

                  2. Audit

                   

                  A batch file running Monarch leaves no traces. If you need to keep a record to prove that the job ran on the 1st of the month, then you'll need DataPump.

                   

                  3. Wildcard inputs

                   

                  If your input file is called 20090601.csv this month, and 20090701.csv next month, your Monarch projects won't handle it. You can get around this with some scripting to rename the input files, but if you want an easy and robust way of managing it, use DataPump.

                   

                  4. Scheduling

                   

                  DataPump can lurk and wait for an input file to arrive in a specific location, by setting a process to be run as "monitored". You can also schedule the process using the Windows Scheduler. Very useful if you aren't in the office on the day the process needs to run!

                   

                  5. Exports

                   

                  In DataPump, exports can be called "Summary-Sales-20090701.xls" - you can also datestamp them, or give them a counter ID. Exports can also be distributed by FTP or email.

                   

                  6. Scripting

                   

                  There's a complete .NET scripting interface for DataPump. The Pro version also integrates with SQL Server Development Tools so that you can include DataPump operations when developing routines in Visual Studio.

                   

                  7. Verification

                   

                  If your report layout changes, a Monarch model would deliver wrong or empty data, and in a batch file there's no way of knowing that an error has occured. DataPump lets you stop a process, and report an error, if the report layout changes by including a verification step that requires n rows in the filtered table to be present, so you can build a filter which tests for expected behaviour. It's not foolproof, but you can configure the error handling to email you an alert.

                   

                  I'm sure others will add to this...

                   

                  Best wishes,

                   

                  Olly

                    • batch extract of fields in comma delimited file to spreadsheet or access
                      Terry007 _

                      The one thing that concerns me is your comment:

                       

                      If your input file is called 20090601.csv this month, and 20090701.csv next month, your Monarch projects won't handle it....

                       

                      What is it about the file that Monarch can't handle....I think this is almost exactly what will happen.  How have you gotten around this issue?

                       

                      Terry

                        • batch extract of fields in comma delimited file to spreadsheet or access
                          Olly Bond

                          Hello Terry,

                           

                          A Monarch project defines the input file(s) explicitly (e.g. c:\input\salesreport.prn, not as c:\input\*.prn).

                           

                          There are hacks - you could have a batch job that perhaps reads a directory listing (using dir > listing.prn, for example) and if a filename matches your criteria, outputs a fixed width text summary in Monarch containing a calculated field of the form:

                           

                          "C:\Program Files\Monarch\Program\Monarch.exe" ...

                           

                          followed by the name of the relevant model, file and export desired.

                           

                          It's not for the faint-hearted, but it's workable.

                           

                          Best wishes,

                           

                          Olly

                            • batch extract of fields in comma delimited file to spreadsheet or access
                              Data Kruncher

                              A Monarch project defines the input file(s) explicitly[/quote]

                               

                              That's not entirely[/I] accurate Olly. By supplying an additional /datasource parameter, you can override the default data source defined in the project file.

                               

                              This line will use the default csv data file as was setup in the project file:

                               

                              "C:\program files\monarch\program\monarch" /prj:"C:\Desktop\csvproject.xprj" /exp:"C:\Desktop\olddata.xls" /expfileopt:overwrite[/code]

                               

                              But this line will replace the default data source with C:\new_csv_data.csv

                               

                              "C:\program files\monarch\program\monarch" /prj:"C:\Desktop\old_csv_project.xprj" /exp:"C:\Desktop\newdata.xls" /datasource:"C:\new_csv_data.csv" /expfileopt:overwrite

                              /code

                               

                              In my testing I used olddata.xls and newdata.xls to ensure that it was operating as expected.

                               

                              You'll still need the name of the file, but a %i (or is it %%i ... I never remember that exactly... Hey Nick, Mr. Batch File!) substitution should help with that.

                               

                              HTH,

                              Kruncher