6 Replies Latest reply: May 15, 2014 10:06 AM by elginreigner _ RSS

    Incorrect Output

    elginreigner _

      Does anyone have a clue why a character field with the value '29250...172891' would be exported as '290250.172891' into a CSV?

       

       

       

       

       

       

       

      Version 10.5 Pro

        • Incorrect Output
          Data Kruncher

          I don't know how you're getting that result.

           

          So long as I define the field as Character, every test gets the correct result of 29250...172891 in the resultant csv export. Even opening the csv with Excel gives me 29250...172891 in a cell.

           

          Have a look at your text file export options for any clues.

           

          It's actually showing up as 290250.172891 in your csv file if you open it with Notepad? Weird. :confused:

            • Incorrect Output
              elginreigner _

              Well, I solved my problem, sort of. The issue is the data itself. The file came from a client, who I suspect is using some Mac version or Open source version of Excel to create the spreadsheet. The '...' is not actually 3 dots, but a unicode character. Excel has trouble using unicode characters correctly (have tried 2003, 2007 and 2010 Excel), especially exporting the data. I have reviewed the raw data of the Excel file, and it still has the unicode character amongs the ascii data. Which is why Monarch is exporting it this way.

               

              Very weird but workable.

               

              Thanks for the look Data Kruncher.

                • Incorrect Output
                  Data Kruncher

                  It is always the same unicode character? You could always get its ASCII value and Strip() it or Replace() it with something else if necessary.

                    • Incorrect Output
                      elginreigner _

                      Thank you Data Kruncher. Using your suggetion i created the following:

                       

                      REPLACE(DBR_CLI_REF_NO,chr(133),'...')

                       

                      The chr(133) is the ascii numeric representation of the unicode value '...'

                        • Incorrect Output
                          Grant Perkins

                          Just out of curiosity here .... did you experiment with 'endian' versions available in the input options? (I'm not quite sure from what you have written as to whether the start point is an Excel file or a text file created by an Excel export or maybe something else I have failed to read correctly.)

                           

                          Grant

                            • Incorrect Output
                              elginreigner _

                              The start file is a XLS spreadsheet. I typically will change it to CSV as if someone screws up the worksheet name, Monarch doesnt read it (i work mostly from autmated command batches). In this case, Excel was exporting the '...' even worse than Monarch was reading it, Excel would export it as a '?', as it does for all unicode values it doesn't recogize. If I would import that CSV, for somereason Monarch wouldnt even see the '?' and just give me all numeric data. The resolve is more manaul setup for the file for automated processing, but the replace() of the chr() worked great.

                               

                              I have tried the other input options, to no avail.