Re: IBM i database to Excel spreadsheet

you can use ODBC to bring AS400 tables into excel spreadsheets. you need
client access installed on the PC. then setup ODBC data source on the PC.
That configs a DSN that connects to the IBM i thru the client access odbc
driver.

Once the DSN is setup you can open excel, go to the data tab on the ribbon,
click get external data, from other sources, from data connection wizard.
Select ODBC DSN from the drop down list, next, your IBM i DSN should be in
the next drop down list. Select that DSN, click Next and continue on to
select the IBM i table or view to import.


On Wed, Nov 30, 2016 at 12:12 PM, Elden Fenison <
Elden.Fenison@xxxxxxxxxxxxxxxxx> wrote:

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.