Обсуждение: delimeters psql /CSV
Hi, I have a problem with psql and CSV.
C:\Users\an\bin>C:\Tmp\psql -h 10.211.43.22 -p 5432 -U postgres -d aatest -w -c "\copy public.""Bayern"" FROM 'C:\Users\an\test\Bayern.csv' WITH DELIMITER ';' CSV"
Error: extra data after expected last column
CONTEXT: COPY Bayern, row 1: "48.455555555555556;11.800833333333333;Anglberg/Amper;Industrial plant;722;220;220;2133;0;0;Undefined..."
I have a table (Bayern) with all columns in the right format. There is a column-value which has a ‘/’ in the string (Anglberg/Amper). I think that ‘/’ is taken for a delimeter, since when I replace / with ‘white space‘ it works.
I use ‘;’ as delimeter. I have UTF-8 encoding in the db. Is there a way around this or do I have to go through all csv files and change ‘/’ to white space?
Kind regards,
Paul
Hi, I have a problem with psql and CSV.
C:\Users\an\bin>C:\Tmp\psql -h 10.211.43.22 -p 5432 -U postgres -d aatest -w -c "\copy public.""Bayern"" FROM 'C:\Users\an\test\Bayern.csv' WITH DELIMITER ';' CSV"
Error: extra data after expected last column
CONTEXT: COPY Bayern, row 1: "48.455555555555556;11.800833333333333;Anglberg/Amper;Industrial plant;722;220;220;2133;0;0;Undefined..."
I have a table (Bayern) with all columns in the right format. There is a column-value which has a ‘/’ in the string (Anglberg/Amper). I think that ‘/’ is taken for a delimeter, since when I replace / with ‘white space‘ it works.
I use ‘;’ as delimeter. I have UTF-8 encoding in the db. Is there a way around this or do I have to go through all csv files and change ‘/’ to white space?
What if you escape the slashes with backslashes?
Angular momentum makes the world go 'round.
Paul Malm wrote: > C:\Users\an\bin>C:\Tmp\psql -h 10.211.43.22 -p 5432 -U postgres -d aatest -w > -c "\copy public.""Bayern"" FROM 'C:\Users\an\test\Bayern.csv' WITH > DELIMITER ';' CSV" > Error: extra data after expected last column > CONTEXT: COPY Bayern, row 1: > "48.455555555555556;11.800833333333333;Anglberg/Amper;Industrial > plant;722;220;220;2133;0;0;Undefined..." > > I have a table (Bayern) with all columns in the right format. There is a > column-value which has a '/' in the string (Anglberg/Amper). I think that > '/' is taken for a delimeter, since when I replace / with 'white space' it > works. There's no reason for '/' to be taken as a delimiter. Can you can share the table definition and the offending line as an attachment, so that someone can try to reproduce this with the exact same data? Also mention your PostgreSQL version and the shell the command is invoked from (cmd.exe, powershell, something else?) just in case it matters. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite
Sorry, I recognized that it was not "/" who caused the problem. I'm doing this in a java program and if I pause the programbefore running psql.exe and just open the csv file in windows with Excel and close it (without doing anything else)and then let the program run again. Then it works! Could it have something to do with encoding. I have no idea. Kind regards, Paul -----Ursprungligt meddelande----- Från: Daniel Verite [mailto:daniel@manitou-mail.org] Skickat: den 7 juni 2019 14:03 Till: Malm, Paul (Operations AIM) Kopia: pgsql-general@lists.postgresql.org Ämne: Re: delimeters psql /CSV Paul Malm wrote: > C:\Users\an\bin>C:\Tmp\psql -h 10.211.43.22 -p 5432 -U postgres -d aatest -w > -c "\copy public.""Bayern"" FROM 'C:\Users\an\test\Bayern.csv' WITH > DELIMITER ';' CSV" > Error: extra data after expected last column > CONTEXT: COPY Bayern, row 1: > "48.455555555555556;11.800833333333333;Anglberg/Amper;Industrial > plant;722;220;220;2133;0;0;Undefined..." > > I have a table (Bayern) with all columns in the right format. There is a > column-value which has a '/' in the string (Anglberg/Amper). I think that > '/' is taken for a delimeter, since when I replace / with 'white space' it > works. There's no reason for '/' to be taken as a delimiter. Can you can share the table definition and the offending line as an attachment, so that someone can try to reproduce this with the exact same data? Also mention your PostgreSQL version and the shell the command is invoked from (cmd.exe, powershell, something else?) just in case it matters. Best regards, -- Daniel Vérité PostgreSQL-powered mailer: http://www.manitou-mail.org Twitter: @DanielVerite