Reading files without headers - then scheduling the job

Nigel Light
Nigel Light Contributor
edited December 2023 in General

Hi

We would like to use Aperture (currently using v1.6.2) to read a series of files that are sent on a regular basis. This files are supplied without headers.

Building the original solution, we successfully manually loaded the files and applied headers using 'Preview and Configure', setting the '1st line header' flag and editing the headings.

However, when we schedule this job to run regularly we will need to automate this process. I am wondering about creating a kind of join with the new file and an empty file containing the correct headers but was wondering if there is a better way.

Any suggestions appreciated.

Nige

Comments

  • Clinton Jones
    Clinton Jones Experian Elite

    If you have a schema file defined as a DDL you can accompany the datafile with the DDL containing the column definitions, you will also need to preview and configure your source-datafile to not treat the first row as a column header row.

    You may want to consider a custom file parser if this file has any other particular attributes that you want to handle in a regularized way, such as file extension, character-set or columns you would typically discard.

  • Nigel Light
    Nigel Light Contributor

    Thanks @Clinton Jones

    In this case, the files are simple .txt files and we were looking the avoid the preview/configure step and automate the process

    A custom file parser might be the way forward for us - is this something we need to request @Pavan Rao or something that a user can build? These files are a defined standard - the test are to confirm the quality of this (as well as content) @Pavan Rao

  • Pavan Rao
    Pavan Rao Experian Contributor

    Custom File Parser will require development effort...meanwhile we can explore DDL file pair for each of those VCT files to begin with.

  • Clinton Jones
    Clinton Jones Experian Elite

    Yes @Nigel Light this is something that the professional services team would typically create for you if you don't feel comfortable building it yourself or don't have the necessary develop resources at hand.

  • Nigel Light
    Nigel Light Contributor

    Thanks chaps - I'll schedule a call with @Pavan Rao  to discuss. Good to have clarity though

    Keep safe

  • Clinton Jones
    Clinton Jones Experian Elite

    here is a dummy data file, code snippet for the schema and a video that shows how it works

    mydata.csv


    create a file with the same name but a DDL extension that contains the following:

    create table MYDATA as
(cust_id,
Date_Created,
Last_Updated,
First_Name,
Surname,
Phone_1,
Email_1,	
Dob,	
Address_1,	
Address_2,	
City,	
State,	
Country,	
ZIP,	
SSN,	
Job_Title
)


    Creating a file with a DDL schema.mp4


  • Nigel Light
    Nigel Light Contributor

    Thanks @Clinton Jones - above and beyond...

Categories