Thank you all for the very interesting responses. Given my current programming skill level, I will rely on my memory to use Access to export to a CSV file. Thought I must say, I could write some mean Lotus macros back in the day!
Bottomline Software, Inc.
Original Message:
Sent: 06-06-2024 09:38
From: David Speck II
Subject: Importing quotes (") into the item code field
Excel will try to escape a double quote with another double quote when you save it as csv or tab delimited. VI does not parse escaped double quotes correctly so it sees it has two double quotes. You can solve this by using a calculation on the field you are trying to import and using the SUB function. If the field you are trying to import is part of a key, then you should use a temp field using the Replace operation with the proper assignment selection (Key, Header, Line) so VI knows when it changes so it can save the record and start the next record. The any of the following SUB function examples will work to replace two double quotes with a single double quote. You can use the CHR() function to specify a ASCII code or use the shorthand version Susan mentioned which is enclosing the ASCII code in $. When using IMP$[x], the x represents the column (1 - 99, it is an array that is not zero-based) in the source file you want to use in the calculation.
SUB(IMP$[x], QUO + QUO, QUO)SUB(IMP$[x], """""", """")SUB(Temp001$, CHR(34) + CHR(34), CHR(34))SUB(Temp001$, $34$ + $34$, $34$)
Also, as Kevin mentioned, it is best to avoid double quotes whenever possible, especially in key fields.
------------------------------
David Speck II
Blytheco LLC
------------------------------
Original Message:
Sent: 06-04-2024 11:14
From: Jeff Schwenk
Subject: Importing quotes (") into the item code field
Epilogue - I imported the Excel data into Access and then exported the table to a tab-delimited file. Access provided options (that Excel doesn't) to avoid the double quote issue. Imported all but five records. Only four had quotes in the item code field. Moving forward.
------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
Original Message:
Sent: 06-03-2024 11:23
From: Susan Pawlowic
Subject: Importing quotes (") into the item code field
I do the Replace with a symbol, then in the VI job do the following:
- Create a Temp field and import the value
- Create a formula:
SUB(Temp1$,$24$,$22$)
Where Temp1$ is the Temp field from Step 1, $24$ is the ASCII reference for the symbol in your file, and $22$ is the ASCII reference for quotes. Attached is the ASCII chart showing the references.
------------------------------
Susan Pawlowic
Sage 100 Solutions Architect
RKL eSolutions, LLC
spawlowic@rklesolutions.com
(717) 735-9109 Ext. 4352