Redshift command - Copy adding a column with random numbers

Question

I am doing unload and copy from Redshift to S3 -

unload (select * from tbl)
to <S3 location>
credentials <creds>
addquotes escape

copy tbl2
from <S3 location>
credentials <creds>
removequotes escape

My table is like - int, text,text,text .

Copy command is adding random numbers in first int column and shifting further columns to right, removing last column.

Does anyone have any idea why this could happen?

Original table -

col1 col2 col3 col4
1 abc def ghi jkl
2 mno pqr stu vwx

Copy Table -

col1 col2 col3 col4
123 1 abc def ghi
456 2 mno pqr stu

Unloaded table is correct

Answer 1

At a guess, two things might be wrong. The first is that your to and from column order is different.

I would try

1. opening the file at 'S3 location' in S3
2. copy the header column (line 1)
3. edit the column text changing your delimiter to "," if not already

4. paste the edited column header into your copy command

   copy tbl2('column list from file') from credentials removequotes escape

   If your S3 file lacks a header than go back to your original export process and figure out what it is.

Less likely, you may be missing the

IGNOREHEADER 1

parameter on your copy. Let us know what you find.