topic Loading Multiple csv file from amazon s3 bucket in Connectivity & Data Prep

Loading Multiple csv file from amazon s3 bucket

Dataguy46 — Wed, 07 Jun 2023 07:55:39 GMT

Hi,

I am trying to pull data from the Amazon S3 bucket, and the path is ibv2-live\activity\server1\,

there are multiple CSV files in this path with prefix activity,

Could you please help me with a script to load this?

Thanks in advance.

Re: Loading Multiple csv file from amazon s3 bucket

datadiva — Tue, 13 Jun 2023 16:29:55 GMT

The best way to make this work without doing loops is to do to the Data Editor. In there, you should connect to your S3 bucket. Open the connection and navigate to the file path where the files you want to open are and select one file from the path and click "Insert Script".

You'll get something like:

LOAD
"Column 1",
"Column 2",
"Column 3"
FROM [lib://<SPACE>:<BUCKET>/<FOLDER>/<FOLDER>/<FILENAME>.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Change the columns listed to LOAD * and remove all of the <FILENAME> portion with the extension and leave the last line of code. That way you end up with something like this:

LOAD * FROM [lib://<SPACE>:<BUCKET>/<FOLDER>/<FOLDER1>/]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

That will pull all of the files.

Also for security it would be wise to not share your actual S3 bucket name and file path in questions.

Re: Loading Multiple csv file from amazon s3 bucket

Charvick — Wed, 26 Feb 2025 15:17:39 GMT

Hi datadiva, I have used the same approach and excluded the filenames from the path, so that it only points to the folder that has the csv files in it. However, I am getting a badFileSize error, this is my query:

LOAD Distinct
UM_USER_NAME as USERID,
Combo_ID
FROM [lib://Amazon_S3/qa/<S3>/<Folder>/<Folder 1>/<Folder 2>/<Folder 3>/<Folder 4>/]

Inside folder 4 all of my csv files will be present.

Can you please help me with this?

Thanks in advance.

Re: Loading Multiple csv file from amazon s3 bucket

Dataguy46 — Thu, 27 Feb 2025 10:24:24 GMT

Hello Charvick,

Please note that this method works only if your files have same schemas.

Re: Loading Multiple csv file from amazon s3 bucket

swarnapavani — Mon, 10 Mar 2025 07:11:32 GMT

Hello @Charvick

Please use this script to load the multiple files from web connectors.

// Manually define the list of file names

for each vFile in [add Filenames here] ex:'Backup_Jobs_Report_2025-01-09.csv','xxxx.csv','yyyy.csv'

// Load the data from each specified file

BackupJobs:

LOAD *

FROM [Lib path/foldername/$(vFile)]

(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

next vFile

Exit Script;