Sage 100

Client is on 4.50, trying to import into sales order/quotes. They use "" & ' (inches & feet) in their item numbers. When saving as .csv we all know it adds add'l quotes. So I found I could export as 'formatted text (space delimited) which then didn't add the quotes. However, I can't get VI to work. It reads the lines, but indicates missing/invalid header. Suspecting I'll have more problems once the header works but can't get past the header record. I have included the VI job. If anyone is able to help, it would be really appreciated. If Formatted Text won't work, is my only choice to do a find/replace on the quotes? Customer won't appreciate that. Thanks

Try tab delimited. use AscII and 009.

Tab Delimited adds the add'l quote marks too.

upload a sample with a couple of records of your space delimited file.

I save as Tab Delimiter with ASCII 009 like Doug days. Open the text file in Notepad and do a file/replace of the Double quotes with a single quote. That should work fine.

.xls file and .prn (formatted text (space delimited) attached. Madeline, trying to avoid the file/replace issue unless absolutely necessary as customer employees will be doing these on a daily basis.

the way the data is formatted is going to make it tough, particularly is you have a lot of records and the import is going to be used frequently. If it is going to be used frequently, the data should be cleaned up with a pre-import program. However, if there are just few records and the import is used infrequently then you can get it to work. With space delimited, every space is counted... It is a text file. Unlike tab delimited, you need to count the spaces beteween fields... You can doenload notetab+ for free. It will save a lot of time counting spaces in text files. See attached examples.

Thanks Doug, I kind of wondered if that was going to play into it. So depending on the length of the item code & subsequent columns, the quantity field for each row could be at a different location - is that a true statement?

note tab lite http://www.trialpay.com/checkout/?c=55bec37&tid=9ahJQJ9

Yes that is possible. That is a good reason to use tab delimited. If you are still getting quotes on the export you might also try | (pipe) delimited. The best method, if available, is fixed field width text. Using fixed field width text, each field is always exactly in the same position... It is difficult to read incorrect data.

How would I get a pipe delimited or fixed field width flle from Excel without the add'l quote marks?

Here's a link on how to export csv with pipe delimiters. http://www.howtogeek.com/howto/21456/export-or-save-excel-files-with-pipe-or-other-delimiters-instead-of-commas/

There are a couple of ways I know, neither of them elegant. Prior to the Excel export, find and replace all inch markers with another character. For example, find "" and replace with *. Now export to tab delimited. Quotes should be gone. Now find * and replace with "". Ready for VI import. You could also use a utility program to read and look for characters, or use an Excel macro to look for and remove quotes.

If I read your post correctly, your customer uses double quotes and single quotes in the actual item code. I have a customer that does the same. Makes it impossible to strip the characters from the import data. The pipe delimited technique has worked well for me in this situation, but the downside is that you have to go through extra steps if you ever need to read the import data into Excel, (usually for debug purposes). I actually found this to be a benefit because it keeps the customer from messing with the data in Excel.

I prefer using a SUB function in the VI job and still use a regular csv file. This is in our 90minds evernote: Problem / Description: When saving an excel spreadsheet with a cell which contains quotation marks- excel doubles the quotes. Resolution: Note: There are multiple ways to approach this including a manual search/replace. This is one potential solution. Bring the data into a temp field in the job then used the following formula: sub([field to search],[string to find],[string to replace with],[#ofinstances]) or sub(Temp001$,$22$+$22$,$22$,0) $22$ being the ASCII for a quotation mark.0=all instances. The PVX Plus Manual is really helpful in figuring this VI job out. Date Created: 10/2/12 Created By: WES Source: LinkedIn - Nicole Ronchetti Updated: 10/2/12 Other Information (Links/Sage KB/Additional Tips):

What complicates Nancy's VI job is that she doesn't want to remove all quotation marks. She wants the quotation marks that denote inches in a description to remain.

Yup. I use the sub function for that. Just bring the description field into the temp field in the VI job. The SUB function example above replaces the double quotes with single quotes.

Yes. I see you are using $22$+$22$ to handle. Great solution!

Nicole, Looking at sub(Temp001$,$22$+$22$,$22$,0). Doesn't this look for all instances of """" and change the """" to ""? There are also instances of single quotes that need to be removed.

This does. If there are single quotes as well you would need to do an additional calculation on the temp field like sub(Temp002$,$27$+$27$,$27$,0) or sub(Temp002$,$27$,"""",0) or something like that depending on the scenario.

Tricky because once you run the first function then all that is left is single quotes.

Thanks everyone, customer is willing to renumber the items with "" or ' in them. It was the lessor of all evils for them! Appreciate all the responses though, will be tucking some of these away for use.

Those dang commas and quote marks are why I have switched to tab delimited imports. They seem to work better. BTW, does anyone know if they fixed the inability to search text containing a double quote for item descriptions.