0 Replies Latest reply: Dec 15, 2008 8:53 AM by Ziggy _ RSS

    Batch converting files to CSV

    Ziggy _

      Hi,

       

      I have dozens of files I want to convert to CSV.  Each has a different file name but are identical in structure ( so Only one Model template).  Is there a way I can run a batch process, anything that is quicker than what I am doing.

       

      this is what I do now ( I work with 2 screens), with Monarch/Model open on one and the folder open on the other...I setup a project Export to CSV "Add Records to File"... I drag the file onto Monarch window..CHECK off "Retain Model" then Run Export...I do this for every file and it adds them together.

       

      this is all Manual and if I screw it up I might add a file twice.

       

      I would also accept a batch convert of multiple files into Multiple CSV ( as opposed to one large file), doesn't matter to me.

        • Batch converting files to CSV
          Data Kruncher

          Hi Ziggy, and welcome to the forum.

           

          You need [URL="http://www.monarchforums.com/showthread.php?t=2663"]Nick's solution for processing multiple files in a folder[/URL].

           

          Let us know if this suits your needs.

           

          HTH,

          Kruncher

            • Batch converting files to CSV
              Ziggy _

              Thanks Kruncher,

               

              I'm going to work on it to see if it works for us.  It has to tie in with another process which will grab the converted files so this sounds promising.

               

               

              ___________________

               

              Ziggy

                • Batch converting files to CSV
                  Ziggy _

                  I'm just not sure about the part that tells the script which folder to look for the files?

                   

                  I figure it is this part, but what to I change to look in the the C:\ drive

                   

                  For %%i in (*.prn) Do Call :DoMonarch "%%i"[/CODE]

                   

                  Edit:

                   

                  I guess it might be: For %%i in (C:\*.prn) Do Call :DoMonarch "%%i"[/CODE]

                   

                  although I am not entirely sure how to run a script, I saved it in a text file as a .scr extension and tried to run at RUN window... cscript C:\MonarchScr.scr  I changed all the other path references.

                   

                   

                  Edit again

                   

                  Ok went to cmd  cscript C:\MonarchScr.[B]vbs[/B]  getting Invalid character...I'll have to check the sytax

                    • Batch converting files to CSV
                      Nick Osdale-Popa

                      Yes, just adding your drive\path to the *.prn line will work.

                       

                      This isn't a script. It's a batch file. Rename with either the .BAT or .CMD extension (if XP or above, I suggest .CMD -- the command processor actually handles it differently.). Then just click on it to run.

                        • Batch converting files to CSV
                          Ziggy _

                          thanks, I fixed that part and double clicked on it...the cmd window popped up for a few seconds ( blank) and then closed.  I don't see the file.

                           

                          this is my script modified from yours ( the paths)

                           

                          @echo off

                          For %%i in (C:\Documents and Settings\ziggy\My Documents\1FTP\REPORTS\*.txt) Do Call :DoMonarch "%%i"

                          GOTO :EOF

                          :DoMonarch

                          ("C:\Program Files\Monarch\Program\Monarch.exe" /rpt:"%1" /mod:"C:\Documents and Settings\ziggy\My Documents\MonarchTemplates\Models\ReceiptORD-MultiLine.xmod" /exp:"C:\Documents and Settings\ziggy\My Documents\MonarchTemplates\Export\DataExport.csv" /T /exptable:"%1" /expfileopt:add)

                          GOTO :EOF[/CODE]

                            • Batch converting files to CSV
                              Data Kruncher

                              Remove the

                               

                              /exptable:"%~1"

                               

                              part of the command. You cannot specify a table name when exporting CSV files.

                               

                              Monarch command lines will not execute if Monarch finds that the instructions provided aren't possible.

                                • Batch converting files to CSV
                                  Ziggy _

                                  thanks,  I also noticed that I forgot to wrap quotes around the input directory C:\Documents and Settings\ziggy\My Documents\1FTP\REPORTS\*.txt

                                   

                                  I have it working now, this is awesome, thanks Nick/Kruncher

                                    • Batch converting files to CSV
                                      Data Kruncher

                                      Great! Thanks for the update.

                                       

                                      Since we''re talking batch files, you might be interested in my [URL is no longer valid]

                                        • Batch converting files to CSV
                                          Ziggy _

                                          thanks again Kruncher, I will definetly look at that.  I need to think about which format/process will work best with operations to automate this.

                                            • Batch converting files to CSV
                                              Ziggy _

                                              Hi again,

                                               

                                              I was hoping to figure it out on my own,, I have done a ton of searching but now just getting frustrated...what do i add to make the current file in the loop rename from .txt to .bak  (after export)

                                               

                                              I tried ren %%i *.bak[/CODE]  but I must be missing something, I can get the [B]ren /B[/I]to work with a hardcoded value but not the variable.

                                                • Batch converting files to CSV
                                                  Nick Osdale-Popa

                                                  Post what your batch file looks like so that we can see where it would need to be renamed.

                                                    • Batch converting files to CSV
                                                      Ziggy _

                                                      thanks Nick,

                                                       

                                                      It is essentially the same as I posted (#6), I'm at home now so I don't have the copy I am working with.  I thought %%i[/B] was the variable /Iholding the full filename ( with Path), I placed the REN /Bstatement just before the second [B]GOTO :EOF[/B][/I]  ( in my work copy)

                                                       

                                                       

                                                      @echo off

                                                      For %%i in ("C:\Documents and Settings\ziggy\My Documents\1FTP\REPORTS\*.txt") Do Call :DoMonarch "%%i"

                                                      GOTO :EOF

                                                      :DoMonarch

                                                      ("C:\Program Files\Monarch\Program\Monarch.exe" /rpt:"%1" /mod:"C:\Documents and Settings\ziggy\My Documents\MonarchTemplates\Models\ReceiptORD-MultiLine.xmod" /exp:"C:\Documents and Settings\ziggy\My Documents\MonarchTemplates\Export\DataExport.csv" /T /exptable:"%1" /expfileopt:add)

                                                      GOTO :EOF[/CODE]

                                                        • Batch converting files to CSV
                                                          Nick Osdale-Popa

                                                          OK. change

                                                          ren %%i *.bak[/B]

                                                          to

                                                          ren %1 *.bak[/B]

                                                           

                                                          %%i is being passed to the second part of the batch file, where it gets used as %1.

                                                            • Batch converting files to CSV
                                                              Ziggy _

                                                              Excellent, that works!  good to know I was on the right track

                                                               

                                                              Here is my finished copy for reference to anyone needing it...

                                                               

                                                               

                                                              @echo off

                                                              rem save file as .cmd extension

                                                               

                                                              rem loops through folder looking for files with RPT1 as part of name

                                                              For %%i in ("C:\Documents and Settings\ziggy\My Documents\MonarchTemplates\Export\RPT1\*RPT1*.txt")  Do Call :DoMonarch "%%i"

                                                               

                                                              GOTO :EOF

                                                              :DoMonarch

                                                               

                                                              rem refer to Monarch command file HELP for explanation

                                                              rem this example exports and appends all files to an Access database "RPT1b.mdb"  table "tblRPT1"

                                                               

                                                              ("C:\Program Files\Monarch\Program\Monarch.exe" /rpt:"%~1" /mod:"C:\Documents and Settings\ziggy \My Documents\MonarchTemplates\Models\RPT1-ReceiptORD-MultiLine.xmod" /exp:"C:\Documents and Settings\ziggy \My Documents\MonarchTemplates\Export\RPT1b.mdb" /expfileopt:add /exptable:"tblRPT1" /exptableopt:append /T )

                                                                 

                                                              rem renames as backup 

                                                              ren %1 *.bak

                                                               

                                                              GOTO :EOF[/CODE]

                                                               

                                                               

                                                              Thanks Nick

                                    • Re: Batch converting files to CSV
                                      Ziggy _

                                      Hi not sure if anyone is still watching this post, but I haven't worked with this script for a few years now.  I gathered up my files and tried to reproduce this and I can't get it to work.

                                       

                                      differences today:

                                       

                                      Monarch Pro, Version 11.6.11.0

                                      Windows 7

                                      Office 2007

                                       

                                      the script and files are the same, but locations are different.  I noticed that the Program path is different and all other Paths are correct?  Anything I should be aware of that might be different?

                                       

                                       

                                       

                                      @echo off
                                      For %%i in ("C:\Users\ziggy\Documents\Development\Monarch\IP93\*IP93*.txt") Do Call :DoMonarch "%%i"
                                      
                                      
                                      GOTO :EOF
                                      :DoMonarch
                                      
                                      
                                      ("C:\Program Files(x86)\Monarch\Program\Monarch.exe" /rpt:"%~1" /mod:"C:\Users\ziggy\Documents\Development\Monarch\IP93\IP93Script\IP93-ReceiptORD-MultiLine-REV.xmod" /exp:"C:\Users\ziggy\Documents\Development\Monarch\IP93\Output\%~n1.xlsx" /expfileopt:append /T )
                                      
                                      
                                      rem renames as backup 
                                      ren  %1 *.bak
                                      del %1
                                      
                                      
                                      GOTO :EOF
                                      

                                       

                                       

                                       

                                      thanks

                                       

                                      Ziggy