Data Loads from Excel

Hi All,

My users are wanting to do mass loads into a couple of the tables (F06017, F08901) using SQL

Normally I would create a table conversion UBE, unfortunately we don't have the time to do this :-(


I had started creating the process for this
1) create temp tables to load data in from EXCEL
2) Import data (using Toad) from excel into my new table
3) query data from import table to JDE table

My load of F06017 was good - didn't see any issues.

With loading data in F08901, I do have an issue. My DOB field is obviously formatted incorrectly and as such, it will not import into the field.

How do I convert a date (04/22/1986) to a julian date in Excel so that the import will work correctly

Is there a better method for this that won't take too long to develop?

Any thoughts/suggestions would be appreciated

Thanks
Pam

Sample query to load into my temp table
Insert into F08901_UPLOAD (
HUAN8, HUALPH, HUSSN, HUDOB, HUDTDH, HUSEX, HUHSG, HUFTS, HUSCA, HUEMP,
HUPU01, HUPU02, HUPU03)
Values (
622325, 'Cooper, Sheldon', null,4/22/1986, null, 'F', null, 'N', null, null,
'N', 'N', 'N');

I know that 4/22/1986 is incorrect, but I am not sure how to add logic in my insert statement to change this to a Julian date

Pam

If you have some development skills, I believe the form you need is a headerless detail over the table. I did this for a positive pay bank form which loads cheque details from the bank re: cleared cheques.

It will cover the data conversion you are looking for and the data can be straight copy-pasted from excel.

Malcolm

Unfortunately we don't have time to develop this into a JDE form/import routine.

I am being provided several spreadsheets that I am to use to load the data into the tables

Our SQL tool has an import routine, but the dates are an issue.

Pam

Use excel formulas to convert the dates. You can convert the YYYY year into the julian year using: YYYY - 1900.

To get the day of the year, check if excel has any built-in functions. If not, google something along the lines of: "get day of year in excel". You will find other people who have requested the same thing.

concatenate the two results for your julian date.

I still like the headerless detail approach for importing from excel. Even if they need the data in the tables sooner than you can get a package build, you can still use app on the machine you're developing on to get the data into the DB.

Here is an Excel formula I have used :

Given - cell A1 contains the date you want to convert (04/22/1986), in cell B1 copy this formula:
=(YEAR(A1)-2000+100)*1000+A1-DATE(YEAR(A1),"01","01")+1
This will convert A1 to 86112 (086112) in cell B1.

You can copy the formula down to convert multiple rows.

Cheers !

Hello Pam,

Use this formula in your excel data sheet column.
'=(YEAR(B3)-2000+100)*1000+B3-DATE(YEAR(B3),"01","01")+1

In above formula "B3" is locate to actual date format column (m/d/yyyy) that you mentioned (04/22/1986) and it convert to (86112)Julian date.

Regards,
Iqbal Khan