timestamp error while importing datat from csv to postgresql

Question

import uuid
import pandas as pd
from sqlalchemy import create_engine
engine = create_engine('postgresql+psycopg2://postgres:xxx@localhost:5432/xxxxa')

df=pd.read_csv("uplauds_corp_financial.csv")
li=[]
for _ in range(len(df)):
    ud = uuid.uuid1()
    print(ud)
    li.append(ud)
df['uuid_']=li
df.to_sql(name="uplauds_corp_financial", con=engine, if_exists='append', index=False)

ERROR=sqlalchemy.exc.DataError: (psycopg2.errors.InvalidDatetimeFormat) invalid input syntax for type timestamp: "23:37.2" LINE 1: ...75, 140.89, 75.92, 0.0, 0.0, 0.0, 0.0, 0.0, NULL, '23:37.2',...

maybe it is taking it as string maybe thats why it is showing error
is there any option that instead of changing it in my csv i can change it before importing into postgresql

enter image description here

It is trying to interpret the time as timestamp but is missing either seconds or hours. The format should be HH:MM:SS.ms — gtomer
– gtomer, Commented Sep 22, 2020 at 13:01
@gtomer any way i can edit it ?other than manually doing in my csv — King
– King, Commented Sep 22, 2020 at 13:47
@gtomer can i post only this part bcoz main csv is of 196 columns and only 2 rows — King
– King, Commented Sep 22, 2020 at 13:54

Adrian Klaver · Accepted Answer · 2020-09-22 14:39:04Z

1

The error is telling you the problem:

select '23:37.2'::timestamp;
ERROR:  invalid input syntax for type timestamp: "23:37.2"
LINE 1: select '23:37.2'::timestamp;

#Reformatting won't help

select '23:37:00.2'::timestamp;
ERROR:  invalid input syntax for type timestamp: "23:37:00.2"
LINE 1: select '23:37:00.2'::timestamp;


#Use time type

select '23:37.2'::time;
    time    
------------
 00:23:37.2

Your data is not providing enough information for use as a timestamp. To be a little clearer you need to change the type for the table field if you are only going to provide a time. Or if you leave it as timestamp then you need to create a complete timestamp(a date and time).

edited Sep 22, 2020 at 14:39

answered Sep 22, 2020 at 14:09

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

timestamp error while importing datat from csv to postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related