Thank you to you Diane and all the others who replied to my question. I think the answer you describe here (and also suggested by a few others) is the answer I will pursue. I will attempt to use the built-in facilities of Client Access and Excel. We don't need fancy things like color or font stuff. Just having Excel properly understand the correct data types would suffice. Thank you to all for the great ideas.
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of dmmueller@xxxxxxx
Sent: Wednesday, November 30, 2016 9:41 AM
To: Midrange Systems Technical Discussion
Subject: Re: IBM i database to Excel spreadsheet
I have created an excel spreadsheet, formatted the columns as needed, saved it as a "template" spreadsheet.
Within the template, created a connection document to our iSeries, also includes the SQL statement within the connection properties.
Works great, each morning, I open the excel spreadsheet, it retrieves the daily data from the SQL statement, and since it is built from a template, all columns are formatted correctly.
Next - added a macro that is saving the file based on the date timestamp.
All tools from Excel and iSeries and within seconds, I have a formatted spreadsheet with data updated each morning.
Diane Mueller
From: Elden Fenison <Elden.Fenison@xxxxxxxxxxxxxxxxx>
To: "MIDRANGE-L@xxxxxxxxxxxx" <MIDRANGE-L@xxxxxxxxxxxx>
Date: 11/30/2016 11:17 AM
Subject: IBM i database to Excel spreadsheet
I'd like to figure out a better way to get data from an IBM i database file into an Excel spreadsheet.
We are currently using an open source tool apparently written by Hideyuki Yahagi. It will take an IBM i database and create a CSV file. We land that on a QNTC file system (on a Windows server) where the user goes and grabs it. The user then imports the CSV file into an XLS file via Excel.
The problem we face is that the CSV format doesn't retain any real information with regards to column formatting. So when we try to import the CSV to an XLS, Excel tries to guess about the column formatting to where fields that appear that they could be valid dates (but are not) are brought in as a date data type. The only way we've found around this is to tell the import process what the format should be for each column, every time we need to import from CSV to Excel. We think that exporting directly from the IBM i database to an XLS format might be allow us to retain the column formatting so that we don't have to go through all those hoops.
So I am seeking a way to go directly from the IBM i database to an Excel spreadsheet. If there are no free solutions, we would definitely consider a paid solution.
Ideas?
Elden Fenison
Programmer/Analyst
Murphy Company
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
