Re: [EXTERNAL] Importing data from a spreadsheet to a PF -- MIDRANGE-L

The solution, IMO, is to find a utility that takes an XLSX spreadsheet and
converts it into a CSV.

Trying to figure out a technique that handles infinitely-variable formats
is a fool's errand. In my application, I designed the spreadsheet; users
filled it in and dropped the CSV copy into a shared folder. The
import/edit program was a big chunk of code but the formatting simplified
the process, which eliminated thousands of hours every year spent on
boring, slow, repetitive, error-prone numeric data entry. A side benefit:
adjusting rates and amounts over timer was easy: bring up the original
document, apply a formula, tweak, and save under a new name.

On Wed, Aug 17, 2022 at 6:29 AM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

In my world, the whole idea is to create a process that "imports" the data
w/out ANY user touching the file.

Example:

FTP Script to get the CSV
RPG pgm read the IFS directory for new file(s)
RPG pgm uses CPYFRMIMPF to place CSV "rows" into Native DB file/table
RPG pgm does something with the new rows (typically orders)

That's where I'm coming from.

I'm just saying, in my experience using Excel as a "medium" to get data
from a streamfile (CSV or otherwise) to a DB table is not the best way to
accomplish that. Unless you're very careful (like your example), you can
encounter all kinds of issues.

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
George Smith
Sent: Tuesday, August 16, 2022 4:02 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] Importing data from a spreadsheet to a PF

It has been my experience when opening a CSV in Excel, I open a blank
spreadsheet go to Data and import from the CSV. This allows me to keep
leading zeros on columns that are text, and I don't want the column to be
General and convert it to a numeric column.

George.....

From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Brad Stone
Sent: Tuesday, August 16, 2022 2:57 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: [EXTERNAL] Importing data from a spreadsheet to a PF

On Tue, Aug 16, 2022 at 2:52 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
wrote: > 1. If it is CSV, DO NOT ever open it with Excel - it trashes the
data > Hmm... I've not seen this before. I do
External (bvstone@xxxxxxxxx<mailto:bvstone@xxxxxxxxx>)
Report This Email<
https://protection.inkyphishfence.com/report?id=c2tvdXQtZnJ1aXQtb2YtdGhlLWVhcnRoL2dzbWl0aEBmb3RlLmNvbS9kYjM1MGYwYWViOGY5ODI3NTljMmZhNjUyZmU4MTI4Ny8xNjYwNjc5ODc1LjYy#key=a1cead70cf98f8c650d6b645674369bc>
FAQ<https://getskout.com/emailprotectionfaq/> Skout Email Protection<
https://getskout.com/emailprotection/>

On Tue, Aug 16, 2022 at 2:52 PM Greg Wilburn <

gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
wrote:

1. If it is CSV, DO NOT ever open it with Excel - it trashes the data

Hmm... I've not seen this before. I don't use CSVs a lot, but when I

double click them Excel (from Office 365) seems to open them just fine.

--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list

To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:
MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

To subscribe, unsubscribe, or change list options,

visit: https://lists.midrange.com/mailman/listinfo/midrange-l<
https://shared.outlook.inky.com/link?domain=lists.midrange.com&t=h.eJw1zksOwiAUheGtGMZSWgyUdtSt0JZbSHkYuDgx7l3ROP5y_pwnqdmT-UIs4r3MjHlXsHTB7VnHw3RbCixo54OOX3IREvsr9eR6IWeblzNVpJCrQ5qAojXU6IyWHSU4tAsk_MX29SZ66LVZFUyKj2LaOGgpOBg1cDWyQcpejpMaRSd565vWXx8FUzTL0c60UJP9I7F6_3oDEu8_2g.MEUCIG3NYZqzhoeRvgoDhb_TwpoTkPRI4c4TBpPshuoiGbrWAiEAtBqhFbWxX7yneiVHKJkCzt3WlnUrATJHxR8c0-ET458

or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:
MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

Before posting, please take a moment to review the archives

at https://archive.midrange.com/midrange-l.<
https://shared.outlook.inky.com/link?domain=archive.midrange.com&t=h.eJw1jEsOgyAUAK_SsC6gNHx05VVQH0JEaeDhpundW9J0OZnMvEjNkYw34hGfZeTc5sWHC9gR1mzPDdiSDv4HGhm538jegrKnitTlGpAmR9EDBZvR860cAf3kEv7idX7IznUWZuMGI7QcFuGsksKB6YXRvFeqU3owWjIl2h_af74KphOm7bAhtlEz69ecNcb3B-R3OhY.MEUCIGrWck8Z6ProheZCOBZimRyeHg7EEPJm5ODPmc8iGmgjAiEAumvkE5TD5eigo6_v2UYQ2uRKDWQQFpnpd-M-Qek4muk

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com<
https://shared.outlook.inky.com/link?domain=amazon.midrange.com&t=h.eJw1jcsOwiAUBX_FsJbSYni0q_7KbQuFlIeBiwuN_641cX1m5rxIK4FMF-IQ73ViDCI8c-qi3wqk3XRrjuR6IcfJ1CM3pLY0jzRbis5QAwUd22v06Gab8SewbbmJ3vZgFm1HzZUYV25BCm6NHrhWbJCyl2rUSnSSn31z9pdHxZzMvEfw4f-8fZfUQnh_ALD0NX0.MEYCIQCyUoBH2PzT0J8TMQOTj6Bnwkfo_fhAwETVcnItQEw0TAIhAI7EoisQ8868WoWrnBeXh2-5vzkeqKqtM0uwOye9mB0Z

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com