CODEWITHSUNDEEP
sqlpostgresqlpsql
Knut Eriksen
Knut Eriksen

Reputation: 27

Cannot copy data from text file into table in psql

I just started learning PostgreSQL and I'm having a lot of trouble inserting the data from a text file into my tables. This is a task for school, so the sql code and text files were written by my teacher. This way I know there isn't actually anything wrong with the files.

The sql code:

drop view if exists Timeantall;
drop view if exists Varighet;
drop table if exists Timelistelinje;
drop table if exists Timeliste;

create table Timeliste (
 timelistenr int primary key,
 status text not null,
 levert date,
 utbetalt date,
 beskrivelse text not null,
 check (status = 'aktiv' or status = 'levert' or status = 'utbetalt')
 );

create table Timelistelinje (
 timelistenr int references Timeliste(timelistenr),
 linjenr int,
 startdato date not null,
 starttid time not null,
 sluttid time,
 pause int,
 beskrivelse text not null,
 primary key (timelistenr, linjenr)
 );

 create view Varighet AS
   select timelistenr,
     linjenr, 
     (sluttid - starttid - pause) as varighet
   from (select timelistenr,
           linjenr,
           cast(extract(hour from starttid) as integer)*60 +
                cast(extract(minute from starttid) as integer) as starttid,
           cast(extract(hour from sluttid) as integer)*60 +
                cast(extract(minute from sluttid) as integer) +
                     case when sluttid < starttid then 60*24
                          else 0
                     end as sluttid,
           case when pause is null then 0
                else pause
           end as pause
    from Timelistelinje
    where sluttid is not null) as c;

 create view Timeantall AS
   select timelistenr, ceil(cast(minuttantall as real)/60) as timeantall
  from (select timelistenr, sum(varighet) as minuttantall
    from Varighet
    group by timelistenr) as m;

  \copy Timeliste from 'timeliste.txt' with delimiter '|' null ''

  \copy Timelistelinje from 'timelistelinje.txt' with delimiter '|' null ''

The problem occurs with the \copy command, where this is the error message: 

psql:timelistedb.sql:51: ERROR:  invalid byte sequence for encoding "UTF8": 
0xf8
CONTEXT:  COPY timeliste, line 2
psql:timelistedb.sql:53: ERROR:  invalid byte sequence for encoding "UTF8": 
0xf8
CONTEXT:  COPY timelistelinje, line 3

Here are my text files:

Timeliste.txt

1|utbetalt|2016-07-04|2016-07-13|HMS-kurs
2|utbetalt|2016-07-08|2016-07-13|Innføring
3|utbetalt|2016-07-19|2016-07-27|Test av database
4|levert|2016-07-20||Innlegging av virksomhetsdokumenter
5|utbetalt|2016-07-20|2016-07-27|Oppsporing av manglende underlagsinformasjon
6|aktiv|||Identifisering av manglende funksjonalitet
7|utbetalt|2016-08-01|2016-08-10|Opprettelse av testdatabase

Timelistelinje.txt

1|1|2016-07-01|09:00|12:00||HMS del 1
1|2|2016-07-04|09:00|12:00||HMS del 2
2|1|2016-07-01|13:00|15:00|15|Innføring
3|1|2016-07-01|15:00|16:00||Test 1
3|2|2016-07-04|13:15|17:00|40|Test 2
3|3|2016-07-04|22:00|01:00|30|Test 3
3|4|2016-07-05|14:00|18:00||Test 4
3|5|2016-07-06|10:00|16:50|55|Test 5
3|6|2016-07-07|10:00|12:00||Test 6
3|7|2016-07-07|15:00|18:00|20|Test 7
3|8|2016-07-08|13:00|13:50||Test 8
3|9|2016-07-09|22:00|03:00|25|Retesting
4|1|2016-07-05|13:00|14:00||innlegging
4|2|2016-07-08|11:00|12:00||innlegging
4|3|2016-07-11|14:20|16:55|45|innlegging
4|4|2016-07-15|15:00|17:00||innlegging
4|5|2016-07-20|10:00|11:45||innlegging
4|6|2016-07-20|12:00|13:45||Enhetstesting
5|1|2016-07-13|09:15|12:00||Leting i arkivet
5|2|2016-07-18|14:30|16:00||Leting i arkivet
5|3|2016-07-19|15:45|17:20|20|Søk i databasene
5|4|2016-07-21|13:00|14:00||Leting i arkivet
6|1|2016-08-01|13:15|14:00||Diskusjoner
6|2|2016-08-02|11:00|12:10||Diskusjoner
6|3|2016-08-05|14:00|17:00|45|Skriving av notat
7|1|2016-07-13|14:05|16:10||Innlegging av data
7|2|2016-07-14|09:20|13:00|45|Vasking av data
7|3|2016-07-15|10:00|12:00||Testing
7|4|2016-07-18|18:00|00:15|50|Testing
7|5|2016-07-19|18:00|20:15||Innlegging av data
7|6|2016-07-21|17:15|22:00|35|Testing
7|7|2016-07-21|23:15|01:10||Feilsøking
7|8|2016-07-26|09:00|11:35||Testing
7|9|2016-08-01|10:30|12:40||Stresstesting

I have absolutely no idea how to solve this problem, I've tried a lot of googling but none of the solutions have helped at all. The deadline for the assignment is approaching as well, and I haven't even gotten to start yet because I can't seem to get past this.

Any help is greatly appreciated!

Upvotes: 0

Views: 127

Answers (1)

Hambone
Hambone

Reputation: 16397

Verify the encoding of the file is UTF-8. make sure to save it as UTF-8, or if it's not, specify the right encoding when you run the copy command.

For example, if the file originated from an MS Office file, it's likely the endoding is WIN 1250. Here is an example of how you would change the encoding upon calling copy:

copy Timeliste from 'timeliste.txt' with delimiter '|' null '' encoding 'WIN1250'

Upvotes: 1

Related Questions