troubleshooting Question

Postgresql: Import csv constant errors, how to fix?

Avatar of Anthony Mellor
Anthony MellorFlag for United Kingdom of Great Britain and Northern Ireland asked on
DatabasesPostgreSQLMongoDB
9 Comments2 Solutions1170 ViewsLast Modified:
I am using Navicat and I (finally) have Postgresql running.

I accept defaults as
Record delimiter LF
Text qualifier " (there aren't any - maybe there should be)

Error Message: ERROR:  invalid input syntax for type timestamp: "01:28:00"
LINE 1: ...','180480','','','1','2','1','1','1987-01-05','2','01:28:00'...
                                                             ^

[Msg] [Imp] Processed: 49, Added: 0, Updated: 0, Deleted: 0, Error: 1
[Msg] [Imp] Finished - Unsuccessfully

So I make both date and time field varchar and try again:

Error Message: ERROR:  malformed array literal: "5/1/1987"
LINE 1: ...1AD00002','529950','180480','','','1','2','1','1','5/1/1987'...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.

[Msg] [Imp] Processed: 49, Added: 0, Updated: 0, Deleted: 0, Error: 1
[Msg] [Imp] Finished - Unsuccessfully

and the query is I think: (not written by me I hasten to add, by Navicat)

[Msg] [Imp] Import start
[Msg] [Imp] Import type - CSV
[Msg] [Imp] Import from - /Users/ADM/Sync/6-Process01/accXtract.csv
[Msg] [Imp] Create table [accXtract]
[Msg] [Imp] Import table [accXtract]
[Err] [Imp] Cannot insert rows 1 - 49:
Query: insert into "public"."accXtract" ("Accident_Index","Location_Easting_OSGR","Location_Northing_OSGR","Longitude","Latitude","Police_Force","Accident_Severity","Number_of_Vehicles","Number_of_Casualties","Date","Day_of_Week","Time","Local_Authority_District","Local_Authority_Highway","1st_Road_Class","1st_Road_Number","Road_Type","Speed_limit","Junction_Detail","Junction_Control","2nd_Road_Class","2nd_Road_Number","Pedestrian_Crossing-Human_Control","Pedestrian_Crossing-Physical_Facilities","Light_Conditions","Weather_Conditions","Road_Surface_Conditions","Special_Conditions_at_Site","Carriageway_Hazards","Urban_or_Rural_Area","Did_Police_Officer_Attend_Scene_of_Accident","LSOA_of_Accident_Location","ACCREF","ACCMTH","A8M","ACCDAY","A8H","ACCYR","MONTH","Location_5Easting_OSGR","Location_5Northing_OSGR") values 
ASKER CERTIFIED SOLUTION
earth man2

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros