I'm having a hard time importing this txt file: http://www.ars.usda.gov/SP2UserFiles/Place/12354500/Data/SR27/asc/FOOD_DES.txt
into Postgresql. What should the delimter be?
I'm using navicat to do it but can use the psql console with this syntax:
copy <table name> from '<txt file path>' delimiter '????' txt;
What should my delimiter be?
Looking at the csv files, it looks like the delimiter is '^', it also introduced a quote character '~'.
More reference here
Here's my testing code :
test=# create table food (c1 varchar(10), c2 varchar(10), c3 varchar(255), c4 varchar(255), c5 varchar(100), c6 varchar(100), c7 varchar(100), c8 varchar(100), c9 int, c10 varchar(100), c11 float, c12 float, c13 float, c14 float);
test=# COPY food FROM '/tmp/food.csv' (DELIMITER '^', quote '~', format csv);
test=# select * from food;
This results in :
c1|c2|c3|c4|c5|c6|c7|c8|c9|c10|c11|c12|c13|c14
01001|0100|Butter, salted|BUTTER,WITH SALT|||Y||0||6.38|4.27|8.79|3.87
01002|0100|Butter, whipped, with salt|BUTTER,WHIPPED,WITH SALT|||Y||0||6.38|4.27|8.79|3.87
01003|0100|Butter oil, anhydrous|BUTTER OIL,ANHYDROUS|||Y||0||6.38|4.27|8.79|3.87
01004|0100|Cheese, blue|CHEESE,BLUE|||Y||0||6.38|4.27|8.79|3.87
01005|0100|Cheese, brick|CHEESE,BRICK|||Y||0||6.38|4.27|8.79|3.87
01006|0100|Cheese, brie|CHEESE,BRIE|||Y||0||6.38|4.27|8.79|3.87
01007|0100|Cheese, camembert|CHEESE,CAMEMBERT|||Y||0||6.38|4.27|8.79|3.87
01008|0100|Cheese, caraway|CHEESE,CARAWAY|||||0||6.38|4.27|8.79|3.87
I use Postgres 9.1
