Hello everyone,
I have had a long term problem loading tab separated data from a text file that has prevented me from migrating my enterprise application from MySQL to PostgreSQL. With version 2 of this application I am making the move to PostgreSQL but am still running into the problem even with PostgreSQL 8 beta.
Sample Data is available at http://www.idahoimageworks.com/sampledata.txt
I'm using the command: COPY residential FROM 'file' WITH DELIMITER AS '\t';
I'm receiving the error: ERROR: missing data for column "builder"
When opened with excel as a tab delimited file all the fields are separated correctly, I know this e-mail is pretty long but I'm stumped.
Thanks,
Here is the information from the table I'm loading into:
Table "mls.residential"
Column | Type | Modifiers
--------------------------+------------------------+-----------
id | character varying(10) |
type | character varying(30) |
area | character varying(30) |
list_price | character varying(10) |
address | character varying(30) |
city | character varying(30) |
county | character varying(30) |
state | character varying(3) |
zip | character varying(5) |
status | character varying(30) |
number_beds | character varying(10) |
number_baths | character varying(10) |
approximate_sqft | character varying(10) |
land_size | character varying(30) |
age | character varying(30) |
level | character varying(30) |
garage_capacity | character varying(10) |
list_agent | character varying(30) |
list_office | character varying(50) |
list_agent_2nd_phone | character varying(25) |
co_agent | character varying(30) |
list_date | character varying(10) |
directions | character varying(100) |
approximate_acres | character varying(10) |
subdivision | character varying(30) |
completion_date | character varying(10) |
year_built | character varying(10) |
lot_length | character varying(10) |
lot_width | character varying(10) |
irrigation_district | character varying(30) |
irrigation_district_name | character varying(25) |
water_shares_avail | character varying(30) |
water_deliverable | character varying(30) |
school_district | character varying(30) |
grade_school | character varying(30) |
jr_high | character varying(30) |
sr_high | character varying(30) |
above_grade_fin | character varying(5) |
below_grade_fin | character varying(5) |
fin_sqft | character varying(5) |
above_grade_unfin | character varying(5) |
below_grade_unfin | character varying(5) |
unfin_sqft | character varying(5) |
price_per_sqft | character varying(10) |
master_bedroom_size | character varying(5) |
bedroom2_size | character varying(5) |
bedroom3_size | character varying(5) |
bedroom4_size | character varying(5) |
bedroom5_size | character varying(5) |
bonus_room_size | character varying(5) |
den_study_size | character varying(5) |
eating_space_size | character varying(5) |
entry_size | character varying(5) |
family_room_size | character varying(5) |
formal_dining_size | character varying(5) |
great_room_size | character varying(5) |
kitchen_size | character varying(5) |
living_room_size | character varying(5) |
office_size | character varying(5) |
other_room_size | character varying(5) |
recreation_room_size | character varying(5) |
utility_room_size | character varying(5) |
shop_dimensions | character varying(10) |
garage_dimensions | character varying(10) |
remarks | character varying(512) |
central_air | text |
brick | text |
one | text |
attached | text |
baseboard | text |
breakfast | text |
single | text |
auto | text |
abandoned_septic | text |
above_ground | text |
composition_shingle | text |
holding_tank | text |
alarm | text |
artesian_well | text |
home_owner_exempt | character varying(30) |
legal_description | character varying(255) |
parcel | character varying(50) |
flood_insurance_required | character varying(30) |
do_not_display | text |
virtual_tour | character varying(255) |
builder | character varying(25) |
area_main | character varying(30) |
Create SQL:
CREATE TABLE residential ( id character varying(10), "type" character varying(30), area character varying(30), list_price character varying(10), address character varying(30), city character varying(30), county character varying(30), state character varying(3), zip character varying(5), status character varying(30), number_beds character varying(10), number_baths character varying(10), approximate_sqft character varying(10), land_size character varying(30), age character varying(30), "level" character varying(30), garage_capacity character varying(10), list_agent character varying(30), list_office character varying(50), list_agent_2nd_phone character varying(25), co_agent character varying(30), list_date character varying(10), directions character varying(100), approximate_acres character varying(10), subdivision character varying(30), completion_date character varying(10), year_built character varying(10), lot_length character varying(10), lot_width character varying(10), irrigation_district character varying(30), irrigation_district_name character varying(25), water_shares_avail character varying(30), water_deliverable character varying(30), school_district character varying(30), grade_school character varying(30), jr_high character varying(30), sr_high character varying(30), above_grade_fin character varying(5), below_grade_fin character varying(5), fin_sqft character varying(5), above_grade_unfin character varying(5), below_grade_unfin character varying(5), unfin_sqft character varying(5), price_per_sqft character varying(10), master_bedroom_size character varying(5), bedroom2_size character varying(5), bedroom3_size character varying(5), bedroom4_size character varying(5), bedroom5_size character varying(5), bonus_room_size character varying(5), den_study_size character varying(5), eating_space_size character varying(5), entry_size character varying(5), family_room_size character varying(5), formal_dining_size character varying(5), great_room_size character varying(5), kitchen_size character varying(5), living_room_size character varying(5), office_size character varying(5), other_room_size character varying(5), recreation_room_size character varying(5), utility_room_size character varying(5), shop_dimensions character varying(10), garage_dimensions character varying(10), remarks character varying(512), central_air text, brick text, one text, attached text, baseboard text, breakfast text, single text, auto text, abandoned_septic text, above_ground text, composition_shingle text, holding_tank text, alarm text, artesian_well text, home_owner_exempt character varying(30), legal_description character varying(255), parcel character varying(50), flood_insurance_required character varying(30), do_not_display text, virtual_tour character varying(255), builder character varying(25), area_main character varying(30) );
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
