Forum Stats

  • 3,839,099 Users
  • 2,262,455 Discussions
  • 7,900,863 Comments

Discussions

Categories

Insert script for excel with different time format

Uday_N
Uday_N Member Posts: 320 Bronze Badge
in SQL & PL/SQL

Hi All,

I have a table called Product . I need to generate insert script which have different time format from a csv file .

Table : Product

create table product (prod_id number , prod_date date , prod_exp_date date , prod_sold_date date);


Input File:

PROD_ID PROD_DATE PROD_EXP_DATE PROD_SOLD_DATE

1   1/1/1998  1/1/4000   2002-03-14-17.17.47.402000

2   1/1/1998  1/1/4000   2001-03-14-17.17.47.420000


Query used in excel:

="INSERT INTO PRODUCT VALUES('"&A1&"','"&TEXT(B1,"MM-DD-YYYY")&"','"&TEXT(C1,"MM-DD-YYYY")&"','"&TEXT(D1,"DD-MM-YYYY HH:MM")&"');"

Insert script got :

INSERT INTO PRODUCT VALUES('1',' 1/1/1998 ','01-01-4000','2002-03-14-17.17.47.402000');


INSERT INTO PRODUCT VALUES('2',' 1/1/1998 ','01-01-4000','2001-03-14-17.17.47.420000');

Expected script output :

('1',to_date(' 1/1/1998','MM-DD-YYYY'), TO_DATE('01-01-4000','MM-DD-YYYY'),TO_TIMESTAMP(

'2002-03-14-17.17.47.402000','YYYY-MM-DD HH24:MI:SS:FF3');


('2',to_date(' 1/1/1998','MM-DD-YYYY'), TO_DATE('01-01-4000','MM-DD-YYYY'),TO_TIMESTAMP(

'2001-03-14-17.17.47.402000','YYYY-MM-DD HH24:MI:SS:FF2');


Since there are more entries i have to do more manual work . I also tried to import into a temp table and then insert into product table but when i inserted i got entries for date like '01-01-98' and '14-03-01' not the exact year . Could you please help me to get correct date format.

Regards,

Uday

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond

    Hi, @Uday_N

    I'm not sure I understand the question. Are you saying that you're exporting data in an Excel spreadsheet and you're having trouble formatting it the way you need it? That sounds like an Excel question, not an Oracle issue. There are other spaces for asking Excel questions, perhaps

    Microsoft Community

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,198 Silver Crown

    Why are you using Excel to create the INSERT statements? What are you going to do with the resulting file?

    What is your long term goal? Is this a one-time thing?

    SQL*Loader is the preferred method to ingesting CSV data.

    I believe SQL*Developer can ingest Excel files (in addition to CSV)

    If the data is already in a table, SQL*Developer can generate the INSERT statements for your.

    There is always an APEX Data Load Wizard. (IIRC - current versions take in Excel and CSV files).

    And..if you are just migrating data from one Oracle database to another, there is always Data Pump.

    Uday_N
    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond

    Hi,

    If the problem is just that Excel sometimes outputs only the last two digits of the year, then maybe you could get around the proiblem by using 'RRRR' format instead of 'YYYY'. Currently in Oracle, TO_DATE (str, 'MM/DD/RRRR') returns the same value whether str is '02/18/2022' or '02/28/22' . Two-digit years in the range 50-99 are assumed to be between 1950 and 1999.

    Uday_N
    · Share on TwitterShare on Facebook
  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi Mike,

    Thanks for your reply . We have to use excel to load data and also we need dml scripts to load data not through sql loader or other methods like external table . Due to this constraint only I’m going for Excel .


    Hi Frank ,

    Thanks for your reply . I will try this method now . Thanks once again


    Regards,

    uday

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown

    Why the artificial constraints against doing the job properly, with the tools that oracle provides for the very purpose of this task? I'd be pushing back.

    · Share on TwitterShare on Facebook
  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi Frank and Mike ,

    I tried but I didn’t get any solution . It still needs manual work . I also posted the Question in Microsoft forum as well . I also tried in Sql developer tool with import but it still have the issue of converting to_timestamp

    eg : '2001-03-14-17.17.47.420000'

    this I am not able to convert into to_timestamp (‘2001-03-14 17:17:47:4200000 ,’YYYY-MM-DD HH24:MI:SS:FF6’) . Since I’m not able to change this alone insert script generated throws Date format error . Could you plz advise .


    Regards,

    Uday


    Stevens - I cannot use any tools which is the major constraint for me . I can either generate insert script or any sql loafer script not any tools . Plz apologise for it .


    Regards,

    Uday

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond
    edited Feb 20, 2022 6:26PM

    Hi, @Uday_N

    this I am not able to convert into to_timestamp (‘2001-03-14 17:17:47:4200000 ,’YYYY-MM-DD HH24:MI:SS:FF6’) 

    You have unbalanced quotes; it looks like you forgot a single-quote right before the comma. The quotes you did post are rounded single-quotes, not straight single-quotes. (It's possible that you correctly typed straight single-quotes, but your editor automatically changed them.) Also, you said yout specified 'FF6' format, but you have 7 digits for the fractional second. If you fix all those mistakes, like this:

    to_timestamp ('2001-03-14 17:17:47:4200000' ,'YYYY-MM-DD HH24:MI:SS:FF9')

    then it will work. Notice that passing 7 digits to 'FF9' format does not raise an error; only the reverse causes the "ORA-01830: date format picture ends before converting entire input string" error.

    · Share on TwitterShare on Facebook
  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi Frank ,

    Thanks for your reply . It’s my mistake that I didn’t insert a single Quote and put seven digits Not a single digit . Is there any way to convert the data into to_timestamp format as specified by you automatically in excel as insert script or in some other way like Sql loader . I’m able to do it manually and able to insert records but could not do it through some excel formula or through any script .


    I’m really thankful for replying to me even on Sunday. Im Also sorry if my message not able to convey my question


    Regards,

    Uday

    · Share on TwitterShare on Facebook
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,246 Red Diamond

    Hi, @Uday_N

    Sorry, I don't know much about Excel, and this really sounds like an Excel issue. (That is, the simplest, fastest, most reliable way to solve this is in Excel.) If you really must try to solve it on the Oracle side, then post a little sample input and the desired results. The input may be a CSV file generated by Excel, and the results may be how the Oracle table looks after the data is imported. (Include a CREATE TABLE statement.) If the problem is that you can't figure out how to get Excel to export the TIMESTAMP column in a consistent format, then put examples of the different formats that it may generate in the sample data. Always post you full Oracle version number (e.g. 18.4.0.0.0).

    · Share on TwitterShare on Facebook
  • Uday_N
    Uday_N Member Posts: 320 Bronze Badge

    Hi Frank ,

    Thanks for your reply . I will do as per your suggestion. Really Thanks for your advice . Your advice always enlighten me . Thanks once again .

    · Share on TwitterShare on Facebook