x Want to participate? Join the group to interact
Options
  • State Verified Answer
  • Replies 15 replies
  • Subscribers 82 subscribers
  • Views 2022 views
  • Users 0 members are here
Browse Forums
Announcements
293
topics
370
replies
Business Object Interface
1k
topics
7k
replies
Core Financial Modules
2k
topics
8k
replies
Customization and Productivity
3k
topics
12k
replies
Distribution, Manufacturing, and Applications
2k
topics
7k
replies
General Discussion
1k
topics
3k
replies
Partner Cloud
45
topics
91
replies
Payroll, Time Tracking, and Job Cost
777
topics
1k
replies
Reporting and Analytics
962
topics
3k
replies
Sage CRM for Sage 100
164
topics
357
replies
Technical and Installation
2k
topics
9k
replies
Year-end Forum
89
topics
237
replies
BG50

Remove line breaks in VI Export

SOLVED
Posted By

Hello all,

SAGE 100 Could Advanced 2020 (V6.20.1.0)

I have a VI (Visual  Integrator) export job that pulls fields from CI_Items out of SAGE and into a .CSV file. I use Task Manager to automate this export and then another software reads the .CSV files. It is great and I don't have to do anything for the data to be updated.

I do have a problem. though. When I export the "CI_ExtendedDescription" - ExtendedDescriptionText field some of the data has returns in it and these are handled as cell breaks in the resulting .CSV file. It is about 6,000 lines and may users enter data so I would rather do the clean up on the export than try to make sure all users enter data without carriage returns.

I have read about making the Operation Calculated in the Export Field Properties and using some of the Functions and Operators to write function (right term???) to remove the carriage breaks and line breaks. It does seem to be working for me. I have read up on ProvideX to try and see if I can figure out what I am doing wrong but I have not had any luck as of yet. When I use the formula below all it does is make the cells in the column (other than the header) blank. But every other field exports fine.

Any ideas? 

SUB(CI_ExtendedDescription.ExtendedDescriptionText$, $0D0A$, "TEST")

Thanks in advance!

  • FormerMember
    0 FormerMember

    Have you tried using ODBC to create your .CSV file?

  • FormerMember
    0 FormerMember

    I'm not sure if the PVX ODBC driver supports the scalar function REPLACE. That would be the function I would use.

    If not I would use ScriptBasic's REPLACE instead.

  • 0 in reply to FormerMember

    Thank you JohnS. I have used ODBC for extraction into Business Analytics tools (MS Power BI) but honestly I do not know how to automate ODBC to .CSV creation. I will research that a bit. If anyone has any tools they can recommend that would be awesome. Making this automated is the big piece for me as this needs to run stand alone to free up that button pushing time and have the data updates happening reliably.

  • 0 in reply to FormerMember

    Thank you again. I will read SAGE PVX ODBC driver info and see if I can figure out how to get it to export to a CSV. If there is anything you can recommend that might get me up to speed o nhow to do this that would be awesome! Thanks again.

  • +1
    verified answer

    You appear to be trying to replace the carriage return + line feed at the same time, it could be that you only have a line feed or carriage return in the data.  So try replacing them separately.  This will show you what was actually stored.

    SUB(SUB(CI_ExtendedDescription.ExtendedDescriptionText$, $0D$, "~CR~"), $0A$, "~LF~")

  • 0 in reply to David Speck

    Awesome. Good thinking on the nesting. Thank you for that David. I just plugged it in and got an "Error 20 in program VIWXOY at line 0085" message. I removed the tilde symbols and tried it again. It exported everything but the data below the Extended Description column again.

    SUB(SUB(CI_ExtendedDescription.ExtendedDescriptionText$, $0D$, "CR"), $0A$, "LF")

  • +1 in reply to BG50
    verified answer

    The temp field's operation should be set to Assign, not Calculated.

  • 0 in reply to David Speck

    David - Thank you again! That did the trick. I had to change Operation to Calculate, enter the expression and then change it back to Align.

    Running that expression did not seem to do the trick though. But I think that part of the challenge is solved.

    I will share it just in case anyone has seen this before. My data has hidden characters - latin small s - ASCII 00DF. You can see it here. At least this is what I think it is.

    I try to strip it out with an expression but it is still there. You don't see it in MS Word Pad but the main problem is that it shows up in Excel like this. It should all be on one row.

    I have an open case with SAGE and they too are stumped for the time being.

    Thanks for everyone's help!

  • 0 in reply to BG50

    Try adding a temp field with the calculation I gave above but make sure its operation is set to Assign, then remove the extended description text field.

    DFDM displays non-standard characters as the Beta symbol but that isn't actually the character stored in the database.

  • 0 in reply to David Speck

    Got it. Added a temp field then set the calculation made sure it was set to Assign but got that error again. 

*Community Hub is the new name for Sage City