Sage 100

Importing IM descriptions - what is the ""safest"" format to import inventory descriptions that have commas and quotes (inches/feet)? My default format is tab delimited but when saving, Excel will add beginning and ending quotes to the field if it contains a comma. For example, **""COLLAR, 2 PC SHAFT, 1-1/2""""** is what Excel creates for the import file. VI will import as **COLLAR, 2 PC SHAFT, 1-1/2""""**. The leading quote disappears but the two ending quotes remain. If I reduce the two ending quotes to one, VI still imports two quotes. Then I have some other descriptions that are identical to this but the quotes are correct. I have @Clean and @ Trim the fields in Excel. But then again, this is all via the test function. Perhaps I should go full on and see where the dust settles.

if you are using tab delimited then try find and replace (with blank) on all of the quotes. You don't need quotes as a delimiter if you specify the delimiter is a tab.

That is the slight problem. I do want some quotes. If I search and destroy for the bad quote, I risk losing some good quotes.

I usually search for double quotes """" and replace with single

A quote is typically defined with three quotes like

Why do they even have a test button for imports? Spent the morning chasing this false positive. If you eliminate the triple quote, the LIVE import functions correctly. Time to start drinking...

I read through this. I am under the impression you have a solution other than mine. Could you clarify that solution. My solution is to find "" and replace with IN :)

Excel should escape the double quote when exported, just like it escapes the comma by wrapping the cell contents in double quotes. A properly escaped double quote in a csv or tab delimted file would be doubled. Look at all 3 attached files exported by Excel 2016. Tab Delimited ``` ItemCodeItemCodeDesc COLLAR-2PC-SHAFT-112""COLLAR, 2 PC SHAFT, 1-1/2"""""" ``` Comma Delimited ``` ItemCode,ItemCodeDesc COLLAR-2PC-SHAFT-112,""COLLAR, 2 PC SHAFT, 1-1/2"""""" ``` Another option is as Diana said, replace the one double quote with two single quotes and in order to maintain data consistency, make sure the end users are instructed to not enter double quotes, instead enter two single quotes where needed.

No, find replace was part of the solution. When I saved as a tab delimited, the single quote at the end of the line exported to the txt file with THREE quotes. See David's first example. So I opened the file and performed a find three quote and replace with two quotes (per a KB, Excel issue). I then did a test import of 100 records. The TWO end quotes were visible. This is a RED herring. After two hours of futility, I did the actual importing thinking that it could be a red herring and it was! The second quote at the end of the description did not pass through, only the first one (COLLAR, 2 PC SHAFT, 1-1/2"") which is what I wanted all along. Thanks everyone.

Did you try without converting to a delimited file?

Actually, no. Burned too many times 18 years ago so this never occurred to me. Might try the XLSX flavor for kicks next time. Thanks for pointing out that option.

Download Libre Office (free) and use Calc for creating the .csv files as tab delimited. It actually let's you choose your delimiter and whether or not to put quotes around text fields