Sage 100

Trying to import a multi line text from one UDF to another UDF. Standard V2013 PU5. When I export it, I see all the data in Crystal & then the Excel & csv file, but when I import, it only brings in the first line. I have tried exporting using VI export & didn't help. Turned off wrap text in Excel. I viewed in Notepad & I see all the text in one line, all within the same quotations, but the import still brings only the first line, then thinks the next line of text is a new record. Stumped.

How many characters does the new udf have in the vi job?

Once in Excel try using the CLEAN function. There may be some special characters preventing the data from being read correctly.

CLEAN didn't help. There are 2 UDFs, one with max of 5566 & the other with 2700. Only a couple get close to the max.

Can you try using a txt file, instead of csv? Am wondering if the csv format may be causing the issue.

Make sure you don't have a Carriage Return after that first line. In Excel go to one of these lines and press F2. See if it is 2 lines. You might need to Backspace or press delete to remove the Carriage Return (Enter key)

The carriage returns are the problem. But, I have 1800 lines of data; I am trying to remove those carriage returns without manually editing all the fields. I thought turning off wrap text would help, but didn't I will try txt file

They don't show up in a txt file but they are there and will cause a problem when importing. I have not figured out a way to remove them without manually editing. Maybe someone else knows.

Find replace?? Use an ASCI code for the carriage return?? Not tested, but it might work....

The problem with find replace is you need the real ones at the end of each record.

If you find replace in excel prior to saving as a CSV and highlight the column, you should be ok. If ongoing save the find replace as a Macro for them to highlight and run.

Just curious, what do you put in the Find and what do you Replace with?

Go to Find Replace in Excel and in the Find what box, hold down the ALT key, and then click numbers 010 from the numeric keyboard. Leave the Replace with box blank or enter a space bar.

There is a program called ASAP UTILITIES that I use to help with just this sort of thing. Works like a charm! http://www.asap-utilities.com/

@LarryBradford this is what I just found as well & it worked!

Thanks Larry

I have only done this once before (actually in the last month). I had to do a little ETG... Education Through Google. :) Matter of fact, I had to do it again prior to posting because I knew the client would not remember so I setup a Macro Script button in there excel to clean the file of carriage returns and then save the file to CSV and back to Excel. I love when have my presentation of Sage 100 ERP is Microsoft functionality.

Hello! I kinda have the same thing happening & I can't get it resolved.

This time I have a client trying to import to an 8000 character UDF & wants carriage returns so its easily read on the screen.
I can't get it to work.
The only thing that does work is bringing the field in as a temp field & subbing some character (i.e, "^") for "chr(13)+char(10)", but since the temp field is capped at 60 characters, it truncates the import. Is there a way to run this logic without a temp field?

I manually added this format to the UDF, then exported it, but the blank characters are not there like they were back in 2015. I did a replace function with ALT 010 & it doesn't find any instances. I also exported a multi-line Ext Description & that doesn't find anything with ALT 010.

If I look at my exports in Excel, I see only the first line, but if I click on the field & hit F2, it shows the lines below. I can't figure out the formatting. I can't figure out how to translate that back as carriage returns in a long text. 

If I do the CLEAN() function, it just puts one line after another without any characters in between.

They are on v2019 Premium, if that matters. Is there a way to get this in via SQL?
They would prefer a VI job so they can do it themselves, as needed.
And, they want to come from a raw Excel file, so the export part is not needed, but was just using to troubleshoot.

Thx!

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

I have an idea for the temp fields... not sure it will work but this is a note I have for something similar.

For calculations / conditions, without a temp field, use IMP$[##] to reference column ## in the source data.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 06-29-2021 14:42
From: Dana Young
Subject: Trying to import a multi line text from one UDF to

Hello! I kinda have the same thing happening & I can't get it resolved.

This time I have a client trying to import to an 8000 character UDF & wants carriage returns so its easily read on the screen.
I can't get it to work.
The only thing that does work is bringing the field in as a temp field & subbing some character (i.e, "^") for "chr(13)+char(10)", but since the temp field is capped at 60 characters, it truncates the import. Is there a way to run this logic without a temp field?

I manually added this format to the UDF, then exported it, but the blank characters are not there like they were back in 2015. I did a replace function with ALT 010 & it doesn't find any instances. I also exported a multi-line Ext Description & that doesn't find anything with ALT 010.

If I look at my exports in Excel, I see only the first line, but if I click on the field & hit F2, it shows the lines below. I can't figure out the formatting. I can't figure out how to translate that back as carriage returns in a long text.

If I do the CLEAN() function, it just puts one line after another without any characters in between.

They are on v2019 Premium, if that matters. Is there a way to get this in via SQL?
They would prefer a VI job so they can do it themselves, as needed.
And, they want to come from a raw Excel file, so the export part is not needed, but was just using to troubleshoot.

Thx!

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

@Kevin Moyes YES!!!! That worked! Thank you very, very much! 

My calculation line for the data in column 2 (for anyone else that could use this):
sub(IMP$[02],"^",chr(13)+chr(10))​

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI
------------------------------

Original Message:
Sent: 06-29-2021 15:05
From: Kevin Moyes
Subject: Trying to import a multi line text from one UDF to

I have an idea for the temp fields... not sure it will work but this is a note I have for something similar.

For calculations / conditions, without a temp field, use IMP$[##] to reference column ## in the source data.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 06-29-2021 14:42
From: Dana Young
Subject: Trying to import a multi line text from one UDF to

Hello! I kinda have the same thing happening & I can't get it resolved.

This time I have a client trying to import to an 8000 character UDF & wants carriage returns so its easily read on the screen.
I can't get it to work.
The only thing that does work is bringing the field in as a temp field & subbing some character (i.e, "^") for "chr(13)+char(10)", but since the temp field is capped at 60 characters, it truncates the import. Is there a way to run this logic without a temp field?

I manually added this format to the UDF, then exported it, but the blank characters are not there like they were back in 2015. I did a replace function with ALT 010 & it doesn't find any instances. I also exported a multi-line Ext Description & that doesn't find anything with ALT 010.

If I look at my exports in Excel, I see only the first line, but if I click on the field & hit F2, it shows the lines below. I can't figure out the formatting. I can't figure out how to translate that back as carriage returns in a long text.

If I do the CLEAN() function, it just puts one line after another without any characters in between.

They are on v2019 Premium, if that matters. Is there a way to get this in via SQL?
They would prefer a VI job so they can do it themselves, as needed.
And, they want to come from a raw Excel file, so the export part is not needed, but was just using to troubleshoot.

Thx!

------------------------------
Dana Young
Lehman Wesley & Associates
Lansing MI