22 Replies Latest reply: May 15, 2014 10:06 AM by adonis _ RSS

    Batch File sending output data into Access MDB

    adonis _

      Hi Not sure if anyone can help me.

       

      There are 2 spreadsheets I generate that get placed in an output file.

       

      I want to copy this data to an access mdb.

       

      I will investigate on my end, but if anyone can point me to that quicker, please let me know

        • Batch File sending output data into Access MDB
          Data Kruncher

          Hey there NYC,

           

          Am willing to help, but you'll need to fill in some details. Fair enough?

           

          What is the "output file" of the two spreadsheet files? A third spreadsheet? Something else?

           

          Do you have Access installed? Would it be easier to, say it isn't so , avoid Monarch altogether and just import that output file directly into the Access table? You could build an Access macro to do that. RalphB, this is where you come in!

           

          But of course you can use Monarch to set up a process (batch file) which would read the two spreadsheets individually (two Monarch passes) and create the output file and finally in a third Monarch pass add that output data to your Access db.

            • Batch File sending output data into Access MDB
              adonis _

              Data,

               

              glad to see you are alive!

              I have not been using monarch much these days but from time to time I do get some adjustmetns or create simple processes.

               

              Originally I take 2 Text Files, and adjust them in Monarch to synch up the fields etc.  I use the batch process which generates 2 files.

              The user takes the 2 files and adds them to a 3rd file.

              I have a simple alias (vlookup) created in this template sheet.  The user though does not want to look thru to find the #N/A items that don't match between both spreadsheet.  The data field names are the same and match up side by side.

               

              Unless you know of another way please advise.

                • Batch File sending output data into Access MDB
                  Data Kruncher

                  OK, now I'm really quite confused. :confused:

                   

                  No more spreadsheets. Text files. OK, check.

                   

                  The two get Monarch'd into a single file of some format. OK.

                   

                  Now we're troubleshooting an Excel vlookup instead of trying to export to Access. No problem. First, make sure that you're using the right syntax for the vlookup:

                   

                  =VLOOKUP(Cell,Range,OffsetNumberOfColumns,FALSE)[/CODE]

                   

                  eg:

                   

                  =VLOOKUP(A2,'OtherSheet'!A1:Z100,7,FALSE)[/CODE]

                   

                  If this returns #N/A, then Excel couldn't find your lookup value in the other list. This will only ever be the case if the values aren't [B]exactly[/B][/I] the same.

                   

                  Watch for trailing spaces (that's impossible to see at first look), and numbers that Excel thinks are text, such as cell values of '300. The leading apostrophe will be invisible when you look at the cell, but '300 will not be equal to 300, so Excel will return #N/A because it can't find the equivalent value in the lookup list.

                   

                  Now if it's correct that the value doesn't exist in the other list, but you want something other than #N/A, then you need something like

                   

                  =If(ISNA(Vlookup(A2,'OtherSheet'!A1:Z100,7,FALSE)),"Not Found Value",Vlookup(A2,'OtherSheet'!A1:Z100,7,FALSE))[/CODE]

                   

                  A word of warning. Nesting vlookups like this, though, really isn't a good idea when working with large spreadsheets as it'll grind your recalculations to a near halt. But for small sheets, I wouldn't worry about it.

                   

                  All for now.

                    • Batch File sending output data into Access MDB
                      adonis _

                      you are the master Data, appreciate your patience, and help, as I want to see what can be done to show the exceptions seperately.

                       

                      My problem is i did 2 simple lookups (left join, and right join) looking at what one side has and the other doesn't.

                       

                      The user is a bit lazy, rather then auto filtering, they just looking at both lookups, they want to see the items that were N/A in both sheets ie: what doesn't match on one file against the other and vice versa. 

                       

                      Since I formatted the data and created a caclculated field or two, the data looks the same, however the output varies where there is a master sheet which always has more securities then the other, and sometimes the other differences is that the price may vary between both sheets.

                       

                      If you put them side by side, the N/As dont align, which is due to a missing security on the non-master file spreadsheet.

                       

                      One easy way to see the N/A's only would be for me to be able to throw it in Access after the batch ran and have a query which shows the items that don't match on both sides.

                       

                      Other then that, I am not a VB guru and wouldn't know how to take both spreadsheets and spit out the N/As without doing it somewhat manually.

                        • Batch File sending output data into Access MDB
                          RalphB _

                          Adonis,

                           

                          why don't you export your data directly into Access and skip the spreadsheet part?  You can do whatever calculations you need in Access  and then run a couple of Unmatched queries to find what doesn't match up and export out that to a spreadsheet or whatever format you need.

                           

                          There are a couple of other ways of extracting the data you want in Access that I can think of off hand but this one will do for now.

                           

                          Ralph

                            • Batch File sending output data into Access MDB
                              adonis _

                              Ralph/Data

                               

                              That is why I was asking initially for help to create the output into an Access MDB.

                               

                              normally from my experience, I only create output and place them in Text or Excel Spreadsheets.

                               

                              This is what my Batch Script looks like:

                              I would like to create output in addition to creating both spreadsheets already in place.  I wanted to take the data and dump into an access mdb and run a simple query that shows both results side by side.

                              let me know what you think.

                               

                               

                              ECHO "Generating NSCC Contract..."

                              SET MTH=%DATE:~4,2%

                              SET DAY=%DATE:~7,2%

                              SET YR=%DATE:~10,4%

                               

                              SET MYDATE=%YR%%MTH%%DAY%

                               

                              ECHO "Generating NSCC Contract..."

                              "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                              Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\NSCC Contract.txt" /mod:"
                              Monarch Download\NSCC Contract & P&S Blotter Summary\Models\NSCC_Contract.xmod"  /exp:"
                              Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\NSCC Contract_%MYDATE%.xls" /S

                               

                              ECHO "Generating P & S Blotter Summary..."

                              "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                              Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\P&S Blotter Summary.txt" /mod:"
                              Monarch Download\NSCC Contract & P&S Blotter Summary\Models\P&S_Blotter_Summary.xmod"  /exp:"
                              Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\P&S Blotter Summary_%MYDATE%.xls" /S

                                • Batch File sending output data into Access MDB
                                  RalphB _

                                  Adonis,

                                   

                                  You are almost there, all you have to do is change the .xls extension to .mdb and it will export out to Access instead of Excel.

                                   

                                  If you want to add both files to the same database, check out Chapter 9 in the Online learning guide in Monarch.

                                   

                                  Ralph

                                    • Batch File sending output data into Access MDB
                                      adonis _

                                      Thanks Ralph

                                       

                                      I will take a look and see if that helps any.

                                      have a good day, any other comments would be appreciated.

                                        • Batch File sending output data into Access MDB
                                          adonis _

                                          think i can adjust names in db will try and see what happens

                                            • Batch File sending output data into Access MDB
                                              adonis _

                                              I was able to figure out how to add them to same database as follows and create the 2 spreadsheets, the problem is the data fields are not the same

                                              instead of showing up as Symbol, Quantity,Actual Price, Amount,Avg Price

                                              the fields showed up as: Symbol, Quantity, Actual Price_1, Amount, Amount_99.

                                               

                                              why did this happen?  below is the 2 new batch file processes, not sure why my database tables have different field names, anyone please help.

                                              thanks

                                               

                                               

                                               

                                               

                                              ECHO "Generating NSCC Contract..."

                                              SET MTH=%DATE:~4,2%

                                              SET DAY=%DATE:~7,2%

                                              SET YR=%DATE:~10,4%

                                               

                                              SET MYDATE=%YR%%MTH%%DAY%

                                               

                                              ECHO "Generating NSCC Contract..."

                                              "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\NSCC Contract.txt" /mod:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Models\NSCC_Contract.xmod"  /exp:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\NSCC Contract_%MYDATE%.xls" /S

                                               

                                              ECHO "Generating P & S Blotter Summary..."

                                              "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\P&S Blotter Summary.txt" /mod:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Models\P&S_Blotter_Summary.xmod"  /exp:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\P&S Blotter Summary_%MYDATE%.xls" /S

                                               

                                               

                                              ECHO "Generating NSCC Contract MDB..."

                                              "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\NSCC Contract.txt" /mod:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Models\NSCC_Contract.xmod"  /exp:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\Validation.mdb" /expfileopt:append /exptableopt:overwrite /exptable:"NSCC Contract" /T

                                               

                                              ECHO "Generating P & S Blotter Summary MDB..."

                                              "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\P&S Blotter Summary.txt" /mod:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Models\P&S_Blotter_Summary.xmod" /exp:"
                                              ntdisk01\operations\Operations\Purchase and Sales - Equity\Equity_Product\Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\Validation.mdb" /expfileopt:append /exptableopt:overwrite /exptable:"P&S Blotter Summary" /T

                                                • Batch File sending output data into Access MDB
                                                  RalphB _

                                                  adonis,

                                                   

                                                  Have you predefined the fields in the tables you are exporting to in Access? 

                                                   

                                                  If so, it appears the field names you have set up in Monarch differ slightly compared to the fields in Access. The field names must match exactly otherwise you get duplicate field names.

                                                   

                                                  That is my guess as to your problem.

                                                   

                                                  What I normally do is let Monarch create the tables first and then add fields if necessary.

                                                    • Batch File sending output data into Access MDB
                                                      adonis _

                                                      Ralph

                                                       

                                                      I can put the data in the Access Mdb, with simple expression changing, i can rename it to something else, so far i found how to put it in access and then i can create a couple of queries, the thing is i need to figure out how to get the Union done so i can see the output in one query vs. clicking multiple times.

                                                       

                                                      keep you posted if i come across any issues,

                                                      wondering why I have issues with join

                                                       

                                                       

                                                      SELECT  , , , , , , , , ,

                                                      FROM NSCC_Validation_Qry

                                                       

                                                       

                                                      UNION

                                                      SELECT  , , , , , , , , ,

                                                      FROM P&S_Validation_Qry;

                                                       

                                                      it says syntax error in from clause

                                                    • Batch File sending output data into Access MDB
                                                      adonis _

                                                      figured out how to fix the query and get the data into an Access MDB.

                                                       

                                                      I am putting the data in Access as follows.

                                                      However i want to execute 3 queries after the data is in the Tables and Copy the data into Excel via the batch file.

                                                      Is there any way to do that, as I do not know VB Script etc.

                                                      Please let me know, as I love the Batch File process if the process can just be appended to the end of the batch file to copy the data from the queries and then place the data into 3 seperate spreadsheets, that would be appreciated.

                                                       

                                                      ECHO "Generating Contract MDB..."

                                                      "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                                                      Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\Contract.txt" /mod:"
                                                      Monarch Download\NSCC Contract & P&S Blotter Summary\Models\Contract.xmod" /exp:"
                                                      Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\Validation.mdb" /expfileopt:append /exptableopt:overwrite /exptable:"Contract" /S

                                                       

                                                      ECHO "Generating P & S MDB..."

                                                      "C:\Program Files\Monarch\Program\monarch.exe" /rpt:"
                                                      Monarch Download\NSCC Contract & P&S Blotter Summary\Input Files\P&S Blotter Summary.txt" /mod:"
                                                      Monarch Download\NSCC Contract & P&S Blotter Summary\Models\P&S_Blotter_Summary.xmod" /exp:"
                                                      Monarch Download\NSCC Contract & P&S Blotter Summary\Output Files\Validation.mdb" /expfileopt:append /exptableopt:overwrite /exptable:"P&S Blotter Summary" /S

                                                        • Batch File sending output data into Access MDB
                                                          RalphB _

                                                          Yes you can.  First off you have to set up an auto execute macro in your Access database that will call all your queries.

                                                           

                                                          Then in a batch file set up something like:"C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "Your database" /x macImportData

                                                           

                                                          where your database includes the path and database name and the last part is your macro name.

                                                           

                                                          If you need more help with Access check out the UtterAccess website.  It is loaded with good information.

                                                      • Batch File sending output data into Access MDB
                                                        adonis _

                                                        Ralph

                                                         

                                                        I can do that, but trying to have the user only click on one button

                                                        they are very detailed about everything, and clicking 3 things would probably annoy them and then it would turn into them coming at me to resolve this issue.

                                                         

                                                        I want to create 2 dormant queries which I would hide, and then have the 1 that is used to show the output populate the exceptions.