https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31531#M22969 <P>What would be the best way of loading several files like in a single table to be consumed?</P><P><A href="https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-07.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-08.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-09.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-07.csv" target="test_blank">https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-07.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-08.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-09.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-07.csv</A></P> Mon, 17 Jan 2022 20:24:41 GMT CleverAnjos 2022-01-17T20:24:41Z https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31531#M22969 <P>What would be the best way of loading several files like in a single table to be consumed?</P><P><A href="https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-07.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-08.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-09.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-07.csv" target="test_blank">https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-07.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-08.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-09.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-07.csv</A></P> Mon, 17 Jan 2022 20:24:41 GMT https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31531#M22969 CleverAnjos 2022-01-17T20:24:41Z https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31533#M22971 <P>New Your Taxi data from your example is already included in your workspace as it is demo dataset.</P><P></P><P>It is enough to read "yellow" folder and it will read all csvs from there.</P><P></P><P>If you want to save it as a single file you can do .repartition(1).write.csv(destination_folder).save()</P><P></P><P><span class="lia-inline-image-display-wrapper" image-alt="image.png"><img src="https://community.databricks.com/t5/image/serverpage/image-id/2167iB2A09AAF2739A058/image-size/large?v=v2&amp;px=999" role="button" title="image.png" alt="image.png" /></span></P> Tue, 18 Jan 2022 20:49:35 GMT https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31533#M22971 Hubert-Dudek 2022-01-18T20:49:35Z https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31534#M22972 <P>Great! </P><P></P> Tue, 18 Jan 2022 20:56:41 GMT https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31534#M22972 CleverAnjos 2022-01-18T20:56:41Z https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31535#M22973 <P>Unfortunately it seems that nytaxi is outdated. there is no records from 2021 and 2020 and 2019 is barely uncomplete</P><P>+-----------+------------------+</P><P>| 2010| 169001154|</P><P>| 2011| 176897208|</P><P>| 2015| 146112990|</P><P>| 2014| 165114361|</P><P>| 2013| 173179759|</P><P>| 2012| 178544324|</P><P>| 2009| 170896987|</P><P>| 2016| 131165043|</P><P>| 2017| 113496933|</P><P>| 2018| 102803387|</P><P>| 2041| 3|</P><P>| 2008| 585|</P><P>| 2001| 15|</P><P>| 2029| 6|</P><P>| 2002| 33|</P><P>| 2053| 2|</P><P>| 2003| 23|</P><P>| 2020| 438|</P><P>| 2019| 84397753|</P><P>| 2037| 1|</P><P>+-----------+------------------+</P> Wed, 19 Jan 2022 15:45:36 GMT https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31535#M22973 CleverAnjos 2022-01-19T15:45:36Z https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31537#M22975 <P>Thanks Kaniz, I already have the files. I was discussing about the best way to load them</P> Mon, 31 Jan 2022 12:04:37 GMT https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31537#M22975 CleverAnjos 2022-01-31T12:04:37Z https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31539#M22977 <P>Yes, </P><P>1) Downloaded the files using sh from here <A href="https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-07.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-08.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-09.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-07.csv" alt="https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2019-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-07.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-08.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-09.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-10.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-11.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2020-12.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-01.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-02.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-03.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-04.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-05.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-06.csvhttps://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_2021-07.csv" target="_blank">https://s3.amazonaws.com/nyc-tlc/trip+data/yellow_tripdata_&lt;year&gt;-&lt;month&gt;.csv</A> to /mnt</P><P>2) Loaded a dataframe with the csv files</P><P>3) Stored as a partitioned table</P><P></P><P>I don´t know if this the best approach, but its working</P> Mon, 31 Jan 2022 13:36:34 GMT https://community.databricks.com/t5/data-engineering/best-way-of-loading-several-csv-files-in-a-table/m-p/31539#M22977 CleverAnjos 2022-01-31T13:36:34Z