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)
);
