none
Complex file format import

    Question

  • Hi there,

    I have a slightly more complicated than usual file format. In my file I have a header record, detail records, and trailer record.

    Example:

    CSVHDR,4.5,20170628,122144

    ATSSTORES,CEL,11122001,,,,20170425,150201,,,,10000,SF04S1FWDL9M,,BLO10,,INV00000022,01

    ATSSTORES,CEL,11122001,,,,20170425,150201,,,,10000,SF04S1FWDL9M,,BLO10,,INV00000022,01

    ATSSTORES,CEL,11122001,,,,20170425,150201,,,,10000,SF04S1FWDL9M,,BLO10,,INV00000022,01

    CSVFTR,10,80000,0

    I need to be able to import this into an Azure SQL database with a similar table structure:

    Header table

    - Detail table(foreign key to header table)

    - Trailer table(foreign key to header table)

    Please note that each record type in the file has a different column count.  Is it possible to do this in data factory?

    Friday, November 2, 2018 2:41 PM

All replies

  • Hi there,

    A single copy activity may not meet your requirement, what about preprocessing and splitting the source file into three files then copying into header table, detail table and trailer table respectively? 

    Thanks.

    Sunday, November 4, 2018 12:31 PM
  • Thanks for the reply. Let me see if I can make that work. I will let you know how it goes

    Thanks.

    Monday, November 5, 2018 9:18 AM