CODEWITHSUNDEEP
azure-synapse
sthambi
sthambi

Reputation: 257

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 in Azure Synapse

I have a Spotify CSV file in my Azure Data Lake. I am trying to create external table you SQL serverless pool in Azure Synapse.

I am getting the below error message

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Track_popularity) in data file https://test.dfs.core.windows.net/data/folder/updated.csv.

I am using the below script


IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDelimitedTextFormat') 
    CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat] 
    WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
           FORMAT_OPTIONS (
             FIELD_TERMINATOR = ',',
             USE_TYPE_DEFAULT = FALSE
            ))
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'test.dfs.core.windows.net') 
    CREATE EXTERNAL DATA SOURCE [test.dfs.core.windows.net] 
    WITH (
        LOCATION = 'abfss://[email protected]' 
    )
GO

CREATE EXTERNAL TABLE updated (
    [Artist] nvarchar(4000),
    [Track] nvarchar(4000),
    [Track_id] nvarchar(4000),
    [Track_popularity] bigint,
    [Artist_id] nvarchar(4000),
    [Artist_Popularity] bigint,
    [Genres] nvarchar(4000),
    [Followers] bigint,
    [danceability] float,
    [energy] float,
    [key] bigint,
    [loudness] float,
    [mode] bigint,
    [speechiness] float,
    [acousticness] float,
    [instrumentalness] float,
    [liveness] float,
    [valence] float,
    [tempo] float,
    [duration_ms] bigint,
    [time_signature] bigint
    )
    WITH (
    LOCATION = 'data/updated.csv',
    DATA_SOURCE = [data_test_dfs_core_windows_net],
    FILE_FORMAT = [SynapseDelimitedTextFormat]
    )
GO


SELECT TOP 100 * FROM dbo.updated
GO

Below is the data sample

enter image description here

My CSV is utf-8 encoding. Not sure what is the issue. The error shows column (Track_popularity). Please advise

Upvotes: 0

Views: 1026

Answers (1)

GregGalloway
GregGalloway

Reputation: 11625

I’m guessing you may have a header row that should be skipped. Drop your external table and then drop and recreate the external file format as follows:


    CREATE EXTERNAL FILE FORMAT [SynapseDelimitedTextFormat] 
    WITH ( FORMAT_TYPE = DELIMITEDTEXT ,
           FORMAT_OPTIONS (
             FIELD_TERMINATOR = ',',
             USE_TYPE_DEFAULT = FALSE,
             FIRST_ROW = 2
            ))

Upvotes: 0

Related Questions