# Snowflake Numeric value is not recognized

**URL:** <https://connective.celigo.com/t/snowflake-numeric-value-is-not-recognized/611>\
**Category:** Integrator.io\
**Tags:** snowflake, error-handling, export, import, amazon-seller-central\
**Created:** [November 9, 2022, 7:48pm UTC](https://connective.celigo.com/t/snowflake-numeric-value-is-not-recognized/611 "2022-11-09T19:48:08Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![alexbaeza](https://avatars.discourse-cdn.com/v4/letter/a/ecd19e/32.png) [@alexbaeza](https://connective.celigo.com/u/alexbaeza)\
**Post date:** [November 9, 2022, 7:48pm UTC](https://connective.celigo.com/t/snowflake-numeric-value-is-not-recognized/611/1 "2022-11-09T19:48:08Z")

</div>

## **Situation**

I am exporting order data from Amazon SP-API and Importing it to Snowflake. When I try to import the data into Snowflake I get an error stating that the numeric value 'Order' is not recognized. I don't understand why Snowflake thinks this value should be numeric, In Celigo I have tried mapping this key with and without manually setting the datatype to String.

Any help would be greatly appreciated.

### Basic Configuration Details

I am using the "Hybrid" connection to Amazon

I am using "SP-API" api type

My export type is "Test - export only 1 record"

My parsed output looks like this (the orders list contains 100 order objects):

```
{ "page_of_records": [{ "record": { "payload": { "Orders": [ {...}, ...]}}}]}
```

### Error

When I try to import the records into snowflake I get this error:

```
 Message: Numeric value 'Other' is not recognized Code: 100038 Source: Snowflake Timestamp: 2022-11-09T18:42:25.197Z Error ID: 484384545 Classification : Value
```

My retry data shows that the only key that has a value of "Other" is "PaymentMethod". I have confirmed that If I remove payment method from the import mapping, this error goes away.

```
{ "payload": { "Orders": [{ ..., "PaymentMethod": "Other", ... }], "NextToken": "zzz", "CreatedBefore": "2022-11-09T18:40:08Z" }}
```

If I edit any of the retry data (not the PaymentMethod), and retry then the error also goes away for this one record.

### Data Investigation

Since this is a test export, only one page of data is returned, and the "Orders" array only contains 100 orders.

All 100 orders have the same key value pair of:

```
"PaymentMethod": "Other"
```

So, I don't think this error is caused by having one of the records incorrectly sending an integer value.

### One **to Many Configuration**

In my import I break out the nested orders using the one to many option:

 ![5301043caa20740200000001-d8dcdec54d59408d972da6599b07f631.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/6/6dac0df36b70783de3dca2b6746e416035ca5fc8.png)

### **Import Type**

 ![5301043caa20740200000001-fe324bd4ff5d402abd8cb48bf4122025.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/0/00c2a4697f0b3fc0b296766548ed383792d97985.png)

 

### **Import Mapping**

 ![5301043caa20740200000001-545e9ebf3c9b405ca1fd6c4fe6b6c884.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/f/f642e318fa3c78d792610680e7877e4bdc6b613e.png)

### **PaymentMethod field Configuration**

I have tried this integration with and without the Data type being explicitly set to String.

 ![5301043caa20740200000001-fa17b6bc9af94115a5d7315e29d2adb3.png](https://us1.discourse-cdn.com/flex001/uploads/celigo/original/2X/2/21c825db86896c95c4b1fdbc2770b47424ee0a13.png)

### **Snowflake**

In snowflake the definition for the "paymentmethod" column is:

```
CREATE TABLE ...( ..., paymentmethod VARCHAR, ...);
```

### **SP-API reference**

[PaymentMethod](https://developer-docs.amazon.com/sp-api/docs/orders-api-v0-reference#paymentmethod)

---

<div class="post-metadata">

**Author:** ![scotthenderson](https://sea1.discourse-cdn.com/flex001/user_avatar/connective.celigo.com/scotthenderson/32/6780_2.png) [@scotthenderson](https://connective.celigo.com/u/scotthenderson)\
**Post date:** [November 9, 2022, 11:22pm UTC](https://connective.celigo.com/t/snowflake-numeric-value-is-not-recognized/611/2 "2022-11-09T23:22:46Z")

</div>

The error being returned by Snowflake "_Numeric value 'Other' is not recognized_" makes me think that the column in Snowflake is not a VARCHAR (i.e. it is likely setup as an INTEGER or equivalent). Can you double check this? If you are positive it is setup correctly in Snowflake, then I def recommend contacting support.

---

<div class="post-metadata">

**Author:** ![alexbaeza](https://avatars.discourse-cdn.com/v4/letter/a/ecd19e/32.png) [@alexbaeza](https://connective.celigo.com/u/alexbaeza)\
**Post date:** [November 10, 2022, 12:14am UTC](https://connective.celigo.com/t/snowflake-numeric-value-is-not-recognized/611/3 "2022-11-10T00:14:40Z")

</div>

No need to contact support, I am now importing the entire record as a variant.

From there I should be able to manipulate the data in snowflake from there. I think the data type issues were caused by shifting columns in snowflake due to missing key value pairs in some records in the JSON response.

Thanks for all your help.

---

<div class="post-metadata">

**Author:** ![scotthenderson](https://sea1.discourse-cdn.com/flex001/user_avatar/connective.celigo.com/scotthenderson/32/6780_2.png) [@scotthenderson](https://connective.celigo.com/u/scotthenderson)\
**Post date:** [November 10, 2022, 12:36am UTC](https://connective.celigo.com/t/snowflake-numeric-value-is-not-recognized/611/4 "2022-11-10T00:36:32Z")

</div>

My pleasure!
