Synapse Analytics - Unable to truncate column from csv in view creation

Kalin Ramsumer 6

I'm attempting to create a view in Synapse reading a csv file in blob storage.
When reading the file, a single column has a length that exceeds 8000.
I'm okay with this column being truncated.

When setting ANSI WARNINGS OFF, this allows me to view the truncated column without warning and subsequently create the view, but would require ANSI WARNINGS to be explicitly set to OFF when querying the view again.

Is there a way to SUBSTRING/accept column truncation on view creation so that when the view is queried, there isnt a need to set ANSI WARNINGS OFF?
Alternatively, what is the best approach to handle these columns that exceed 8000 characters?

The message returned when ANSI WARNINGS is on is as follows:
"String or binary data would be truncated while reading column of type 'VARCHAR'. Check ANSI_WARNINGS option."

I would appreciate any insight!

ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2022-10-10T07:44:17.02+00:00
Hi @Kalin Ramsumer ,

Thank you for posting query in Microsoft Q&A Platform.

Kindly help with below details and understand ask better and help with resolution.

How you are creating view? Are you reading csv file and creating view on top if it using OPENROWSET() function? Kindly share your code so what I can try same at my end help better.

If OPENROWSET() function is getting used, then at the time of VIEW creation itself you can omit that lengthy column to be included right?

Kindly shar the SQL code which is getting used here. Thank you.

Kalin Ramsumer 6

Hi @ShaikMaheer-MSFT ,

Thank you for the response.
Correct, I am reading a csv using OPENROWSET()

The column should however not be excluded.
I would like to include the column but dont mind if its truncated.

Sample code below:

SELECT   
    [Description]  
FROM    
    OPENROWSET(  
        BULK 'source/entity/year=*/month=*/day=*/*.csv',   
        DATA_SOURCE = 'trusted',   
        FORMAT = 'CSV',   
        HEADER_ROW = TRUE,   
        PARSER_VERSION = '2.0',   
        FIELDTERMINATOR = '|',   
        FIELDQUOTE = '"'  
  )   
  WITH (  
    [Description] VARCHAR(8000)  
) AS [result]

----------------------------------------------------------------

Ive also tried:

SELECT   
    SUBSTRING([Description],1,8000) AS [Description]  
FROM    
    OPENROWSET(  
        BULK 'source/entity/year=*/month=*/day=*/*.csv',   
        DATA_SOURCE = 'trusted',   
        FORMAT = 'CSV',   
        HEADER_ROW = TRUE,   
        PARSER_VERSION = '2.0',   
        FIELDTERMINATOR = '|',   
        FIELDQUOTE = '"'  
  )   
  WITH (  
    [Description] VARCHAR(8000)  
) AS [result]

----------------------------------------------------------------

Kindly advise what my options are if there is an alternative?

Thank you

ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2022-10-12T15:58:24.677+00:00

Hi @Kalin Ramsumer ,

Kindly try using VARCHAR(MAX) as column datatype and see if that helps.

Please let me know how it goes.
ShaikMaheer-MSFT 38,401 Reputation points Microsoft Employee

2022-10-14T06:17:35.21+00:00

Hi @Kalin Ramsumer , Just checking if you got chance to share updates any?
Kalin Ramsumer 6 Reputation points

2022-10-17T06:30:10.573+00:00

Good Morning @ShaikMaheer-MSFT

I have tried that but unfortunately the message returned is that VARHCAR(MAX) is not supported.

Share via

Synapse Analytics - Unable to truncate column from csv in view creation