That can be done directly in the SQL (with the COPY INTO function) or by using dataframes (classic way).

As you started out with SQL:

-- The example below loads CSV files without headers on ADLS Gen2 using COPY INTO.
-- By casting the data and renaming the columns, you can put the data in the schema you want
COPY INTO delta.`abfss://container@storageAccount.dfs.core.windows.net/deltaTables/target`
FROM (
  SELECT _c0::bigint key, _c1::int index, _c2 textData
  FROM 'abfss://container@storageAccount.dfs.core.windows.net/base/path'
)
FILEFORMAT = CSV
PATTERN = 'folder1/file_[a-g].csv'

(Snippet from the Azure Docs)

There is also an 'inferSchema' option which will try to determine the schema itself (by reading the data twice). But the quality of the result varies, it might not give you the result you expected (doubleType instead of decimalType etc) and it is slower (because you read 2 times).

Using dataframes is similar, but you read the csv using pyspark/scala with a manually defined schema or inferSchema

(https://sparkbyexamples.com/spark/spark-read-csv-file-into-dataframe/)