7 Replies Latest reply: May 15, 2014 10:02 AM by JTR1104 _ RSS

    Bad First Record in CSV File

    JTR1104 _

      Hi,  I hope someone can help with a seemingly simple problem.

       

      I am developing a batch process to have Monarch read in a CSV file as a database, do some processing and output a report.   All works well except for one small road bump.

       

      The problem is that the CSV file is provided by the state and there is always a comma in the first record.   All by itself.  The rest of the file is fine.  Remove th first record and all is well.

       

      However, what happens is when I use Monarch to open the file (with the comma in place) as a database, Monarch becomes unresponsive and its process needs to be ended.

       

      Before doing some programming or making removal of the first record a manual process, I was hoping to find another solution.   PREP can remove the header record but I don't find the same capability in the Monarch Utility.  I also do not know how to set up a PREP command line nor have I been able to find info on PREP command line options.

       

      Seems simple but I am stumped. 

       

      Any advise would be much appreciated ... as always.

       

       

        • Bad First Record in CSV File
          Data Kruncher

          Hi JTR,

           

          There's an easy to overlook option in the dialog box when opening databases. When you see the checkbox to indicate that the "First row contains column names", just to the right and down one row there's an option to offset the loading start point. Set the "Lines to skip at start" option to 1, and you'll be on your way.

           

          No programming or use of the Utility program needed, so no fun for me this time.

            • Bad First Record in CSV File
              JTR1104 _

              Thanx Kruncher!!    

               

              However, the dialog does not go that far.   When I provide the source file name and select NEXT, that's all she wrote.   After waiting a while and then check the Task Manager, Monarch has stopped responding.   Monarch really does not like a lonely comma in the first record and I never get to the dialog screen with the first line check box.

               

              That's why I thought I needed to use something like PREP to get rid of the first line before Monarch tries to open the file.   

               

              BTW, I would have included this in the first post but to be honest, I never thought of the check box and was zoned into a batch based solution.

               

              Fun Fun Fun  

                • Bad First Record in CSV File
                  Data Kruncher

                  Funny, that's not what happened with my little test file. No lock ups with:

                   

                  ,

                  1,2

                  2,3

                  3,4[/code]

                   

                   

                  Nonetheless...

                   

                  I came up with a one line batch file you can try:

                   

                  "C:\Program Files\Monarch\Program\MonarchU.exe" "C:\test\test.csv" "C:\test\outtest.csv" /cr "/044/013" "" /chset:ascii[/code]

                   

                  No promises, though, because your symptoms seem to indicate that something else may /Bbe at work here.

                   

                  Try it and see how it goes...

                    • Bad First Record in CSV File
                      Nick Osdale-Popa

                      OK. I've verified that, yes, Monarch does lockup with a comma as the first line. My test file had 1200 lines and it did not like the comma.

                       

                      Data Kruncher's routine effectively removes that comma, but leaves it as a blank line. Monarch did not like this file either.

                       

                      This line of batch will effectively remove the first line of the file:

                      for /f "skip=1" %%A in (doctest.csv) do ( @echo %%A >> outtest.csv )[/code]

                       

                      Just replace doctest.csv[/B] with your report file and outtest.csv[/B] with the name of the new file.

                       

                      EDIT:

                      If for some reason the above routine does not work, just add the tokens option:

                      for /f "skip=1 tokens=*" %%A in (doctest.csv) do ( @echo %%A >> outtest.csv )[/code]

                        • Bad First Record in CSV File
                          Data Kruncher

                          I bow to your batch greatness.

                           

                          Even after twenty ... OK, along time ... I still don't know my way around batch programming "tricks" near as well as you do. Once again, well done.

                            • Bad First Record in CSV File
                              JTR1104 _

                              Thank you Nick and Kruncher for your efforts and feedback,

                               

                              The Monarch Utility suggestion did not work.  The batch did.   However, with 190,000 records in the file, it takes a few minutes.   But it works.

                               

                              With a small sample of the file (eg. first 50 records) Monarch opens it but  displays no data nor fields on the subsequent screens.

                               

                              Thanx again!!!!   Great forum!!