Importing from AS400 to Excel

[philwes83170]

I'm trying to import data from an AS400 to Excel. I can get the data to come in fine, except for dates. I just get a number that will not convert to a date. I couldn't find anything in the options that would preserve the date format during the import.

I have a feeling I'm missing something pretty simple. Thanks in advance for any help you can offer.

 

[dougcranston]

philwes83170,

Do not work with AS400, but dates can be a bit of a pain.

What is your date format in the text file that you are trying to import into Excel as a date?

Some sample functions you might be able to use to import the data..

http://www.ozgrid.com/Excel/TextFormulas.htm#DATE

An optional approach would be to import from the text file using ADO Recordset, see:

loading a recordset from textfile
thread329-509150 refs of interest include:

Pulling data from Access into another MS App (via DAO)

http://www.tek-tips.com/faqs.cfm?spid=707&sfid=2281

VBScript & Excel

http://www.greggriffiths.org/webdev/both/excel/activex.html

Rendering the results of an Access Database in Excel

http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=8

generating excel sheets from access
thread181-472093 DAO to run a query in Access and return results to excel
thread707-510949 to Excel then format the spreadsheet using VBA
thread705-511927 this helps.
DougCranston

 

[PaulTEG]

philwes81370,

Its time to read up on the date formats for the AS400. Once you know what format they're using, it should be fairly straightforward to code up a conversion routine.

Can you give us a sample of what a date comes in as, and what it should be?

Paul

 

[ETID]

if your date looks like this...


20030328

then you can can add a col to convert it


in new col...assuming your as400 date is in col. A
then in col B ...format the column as date
then paste this formula.


=DATEVALUE(MID(A1,5,2)&"/"&RIGHT(A1,2)&"/"&LEFT(A1,4))

fill down