× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. August 2022

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

With the spreadsheet on our desktop, we go to ACS.

Then, in the ACS Emulator screen, select Action, Data Transfer to IBM i.
From the pop-up, browse for the spreadsheet on your desktop. Now browse for
the Library/file (member) you want the spreadsheet transferred to. Then
select Details and point to an xxxx.fdfx which contains:

[Data Transfer File Description]
FieldCount=3
FileType=23
ClientFileEncoding=UTF-8
DataTransferVersion=1.0
[Options]
DateFormat=1
DateSep=1
DcmlSep=1
TimeFormat=1
TimeSep=1
[F0001]
Length=7
Name=SCAN
Type=2
[F0002]
Length=6
Name=BAD
Type=2
[F0003]
Length=6
Name=GOOD
Type=2

Our IBM i file only has three fields in this example.

Now press Start Transfer

If you get this Inquiry Message:
Multiple sheets exist in the spreadsheet file. Do you want to send data
from sheets other than the first sheet?
Answer No

You should get a Inquiry Message such as

Transfer request is complete.
Transfer statistics: hh:mm:sc
Rows transferred: nnn
OK

On Wed, Aug 17, 2022 at 9: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




As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.