0

I am trying to load csv data file into snowflake. But it shows this error

"Numeric value '0xA1T0xA920xB4' is not recognized File '@TRY/ui1592405587937/New Microsoft Excel Worksheet.csv', line 2, character 1 Row 1, column "TRY"["DEC":1] "

My csv file contains this data

Dec            name

80.12345678    Kavi
80.67543213    Ravi

But when i try to load manually by insert statement insert into table values(80.12345678,'Kavi'); Then my data is getting inserted.

James Z
  • 12,209
  • 10
  • 24
  • 44
pavithra
  • 83
  • 12

3 Answers3

0

You have a value of '0xA1T0xA920xB4' that is being inserted into a Numeric value of your table. This is not a valid number. Your insert statement is working, because you are using values that are numeric in the first column. I recommend adding a CONTINUE to your ON_ERROR command and see how many records can be loaded, and which are not.

Mike Walton
  • 6,595
  • 2
  • 11
  • 22
  • Can you share your statement? – Mike Walton Jun 17 '20 at 15:24
  • You may need to look into file format options depending on your data. Is your numeric field surrounded by quotes and coming over as a string? What are your delimiters and are they specified? Are you skipping the header (column names) or not when loading this data? – Suzy Lockwood Jun 17 '20 at 15:39
0

The non-numeric value 0xA1T0xA920xB4 if decoded with unicode evaluates to ¡T©2´ which does not conform to your description of the 2-row data within the file.

If you've used Microsoft Excel's CSV export capabilities, ensure your written file is correct by printing its contents via type on a Windows CMD prompt or a cat on Linux/macOS Terminal, and double check that there are no extra characters surrounding the actual data fields.

Your file must appear as the following when viewed directly (assuming comma delimiters):

> type 'New Microsoft Excel Worksheet.csv'
Dec,name
80.12345678,Kavi
80.67543213,Ravi

If it appears different, try performing an export to CSV format again, selecting the right format option that uses UTF-8 style encoding.

0

You might want to clean your csv file. It seems there might be some special characters which snowflake is not able to parse/load.

Open your file in notepad++ and look for that special character and delete it. There is a button in notepad++, just below all menu bar ( File, Edit.. etc ), when you hover the mouse over that button, it says "Show All Characters", if you click that, it will show all the characters embedded in the file like newline character etc.

This hexcode: '0xA1T0xA920xB4' looks something like this: © ´ Since your first column is Decimal, so Snowflake is throwing this error because it sees a non numeric character.

I think this is the error.

Thanks

Somdeb Mukherjee
  • 158
  • 5