topic Re: COPY INTO skipRows FORMAT_OPTIONS does not work in Data Engineering

COPY INTO skipRows FORMAT_OPTIONS does not work

yubin-apollo — Wed, 30 Nov 2022 18:26:14 GMT

Based on the COPY INTO documentation, it seems I can use `skipRows` to skip the first `n` rows. I am trying to load a CSV file where I need to skip a few first rows in the file. I have tried various combinations, e.g. setting header parameter on or off, mergeSchema on or off. I think I tried most cases I can think of, and any of these does not skip the first `n` rows. Has anybody experience this issue?

COPY INTO Documentation: https://docs.databricks.com/sql/language-manual/delta-copy-into.html

Re: COPY INTO skipRows FORMAT_OPTIONS does not work

UmaMahesh1 — Wed, 30 Nov 2022 18:31:48 GMT

Hi @Yubin Park

Can you write down the statement you are using to copy the data.

Also, after copying, did you check the record count between source and target and find that records are not skipped ?

Re: COPY INTO skipRows FORMAT_OPTIONS does not work

yubin-apollo — Sun, 04 Dec 2022 21:48:42 GMT

Thanks for the reply, @UmaMahesh. Here is the code I tried:

COPY INTO public_data.nppes_other_names_brnz
FROM (SELECT _c0::STRING NPI,
  _c1::STRING Provider_Other_Organization_Name
  FROM 's3://...')
FILEFORMAT = CSV
FORMAT_OPTIONS = ('skipRows' = '1');

I checked the number of records, and yes, it did not skip. Thanks for your help in advance!

Re: COPY INTO skipRows FORMAT_OPTIONS does not work

karthik-kobai — Mon, 25 Mar 2024 22:13:00 GMT

@yubin-apollo: Hello, there. I was testing the copy into command with the skipRows option and it doesn't seem to work. Were you able to figure out the right way or is it a bug? Thanks.

Here is the query for reference

COPY INTO dummynamespace.dummytable FROM 'azure blob xxx' WITH ( CREDENTIAL (AZURE_SAS_TOKEN = 'zzz') ) FILEFORMAT = CSV PATTERN = 'Employees_*.csv' FORMAT_OPTIONS ('mergeSchema' = 'true', 'delimiter' = ',', 'header'='true' ) COPY_OPTIONS ('mergeSchema' = 'true', 'skipRows' = '3')

Re: COPY INTO skipRows FORMAT_OPTIONS does not work

karthik-kobai — Tue, 26 Mar 2024 13:29:52 GMT

@yubin-apollo: My bad - I had the skipRows in the COPY_OPTIONS and not in the FORMAT_OPTIONS. It works, please ignore my previous comment. Thanks