CODEWITHSUNDEEP
pythonamazon-web-servicesamazon-s3amazon-redshift
mjoy
mjoy

Reputation: 700

Python s3 to Redshift error: Invalid digit, Value '"', Pos 0, Type:

I am trying to upload my csv file from S3 to Redshift but I keep getting the error:

InternalError: (psycopg2.errors.InternalError_) Load into table 'scheme.table' failed.  Check 'stl_load_errors' system table for details.

The first error showed:

 colname     type      raw_filed_value     err_reason
  col1      float8     [{'ORG':'nba'}      Invalid digit, Value '"', Pos 0, Type: Double

I saw a post that said to converted all columns. So I converted columns with strings to str and numeric columns to int. Then I saw the following error:

 colname     type      raw_filed_value     err_reason
  col1       int4      [{'ORG':'nba'}      Invalid digit, Value '"', Pos 0, Type: Integer

Then I saw a post that said to convert ints to DECIMAL(10,0). However, this now produced the following error:

colname     type      raw_filed_value     err_reason
  col1     numeric    [{'ORG':'nba'}      Invalid digit, Value '"', Pos 0, Type: Decimal

I have check col1 and made sure there are no string values. All there is numeric vals 1,2,3,4, and 5. The column was before, but int now after converting them to int. I can't figure out how to solve this.

Upvotes: 0

Views: 1073

Answers (1)

Bill Weiner
Bill Weiner

Reputation: 11092

The Redshift COPY command (I assuming as it wasn't stated) is seeing the text [{'ORG':'nba'}

This value is in your S3 files somewhere.

Can you post your COPY command and the full line of data being parsed and file name as reported by stl_load_errors?

Some likely causes:

  1. You are reading a file from S3 you don't expect
  2. You have embedded quotes or commas that aren't properly escaped

More possibilities and more likely causes will become apparent with more data

Upvotes: 0

Related Questions