0

I'm trying to import data from a fixed width text file in to a Postgres table using COPY command. The said file contains approximately million records with a width of 1450 characters most of which are empty spaces with useful data scattered at predetermined places in every row.

On using the COPY command, PSQL is throwing the following error. 

upi=# COPY issuer (stage)
upi-# FROM 'D:/UPI_Inward.txt';
ERROR:  literal newline found in data
HINT:  Use "\n" to represent newline.
CONTEXT:  COPY issuer, line 227040

Please explain what error it is throwing and suggest a way to import the rows from the text file in to Postgres table.

PS: The table "issuer' mentioned in the code contains two columns ID bigint and stage character varying(1460) and I'm expecting PSQL to import each row as a single record with an ID number assigned to it.

M D S R Kumar
  • 1
  • 1
  • It seems you have records that span more than one line in the input file. –  Apr 01 '20 at 16:01
  • By fixed-width, do you mean that the data does not have delimiters or separators, you just identify the records and fields by counting bytes? If so, COPY does not support fixed width data. You will have to use some other tool to reformat the data into a format COPY accepts. – jjanes Apr 01 '20 at 16:40
  • @jjanes That's right. The data does not have delimiters or separators and necessary records have to be extracted by counting bytes. If we manage to import each row as a record in to "issuer" table extracting data from these rows could be very easy. – M D S R Kumar Apr 01 '20 at 18:05
  • @a_horse_with_no_name Each row is exactly 1452 bytes long and then a new line starts. As I mentioned earlier, every row is filled with lot of white spaces and useful data is scattered at fixed positions. – M D S R Kumar Apr 01 '20 at 18:13
  • The COPY command can not deal with fixed width columns, it requires a delimiter. The only thing you could do, is to import everything into a staging table that has a single `text` column. Then use SQL and Postgres string processing functions (e.g `substr()`) to split up the value and insert into the real table. –  Apr 01 '20 at 18:15

1 Answers1

0

I believe the error message you report (for TEXT-formatted input) is only possible when your file has inconsistent line terminators (use of \r, or \n, or \r\n). The first thing it saw was an \r, so now it insists on using that throughout the rest of the file.

Either you switched to then use \n as the line terminator, or you have tried to embed a literal newline character in the middle of a line. In the first case, you have to not do that. In the second case, you have to replace the literal newline with the two-character escape sequence \n. (You could just insert a backslash in front of the literal newline character, but that is not recommended)

jjanes
  • 37,812
  • 5
  • 27
  • 34