import csv text file with fixed length columns

Question

I am trying to import into a sql table a fixed column length csv file and contains different types of records and I only need to import some of them

Is it posible using datafactory?

thanks in advanced

Answer 1

Yes, you can import a fixed-length column CSV file into a SQL table using ADF following these steps :

1. Create Linked Services:
   • Linked Service for Source: Create a linked service for the storage where your CSV file resides (e.g., Azure Blob Storage, SFTP, etc.).
   • Linked Service for Destination: Create a linked service for your SQL Database where you want to load the data.
2. Set up the Data Source:
   • Dataset for Source (CSV):
     • In the dataset for your CSV file, you can configure the file format as delimited text and specify the row delimiter (e.g., \n for a new line).
     • For fixed-width columns, you would specify custom delimiters or split columns based on character positions using expressions.
3. Use Data Flow for Complex Parsing:
   • If the records have different types and you only need to import some of them, you can use Data Flows in ADF:
     • Derived Column Transformation: You can create derived columns that split the rows based on the fixed-length positions of each column.
     • Conditional Split Transformation: Apply a conditional split based on the row content to only include certain types of records.
     • Filter Transformation: Filter out unwanted rows based on the record type or specific column values.
4. Mapping the Columns:
   • Once you have split the fixed-length columns in the data flow, you can map the columns to the corresponding SQL table columns.
5. Sink to SQL Database:
   • Define the sink as your SQL table.
   • Perform mapping to ensure the parsed columns are aligned with the SQL table schema.

Sample Setup for Fixed-Length Columns Parsing:

• Suppose your CSV has fixed-length columns like this:
  • First column: 10 characters (e.g., Record Type)
  • Second column: 20 characters (e.g., Name)
  • Third column: 30 characters (e.g., Address)
  In the Data Flow, you can use a Derived Column transformation to split them as:
  • substring(InputColumn, 1, 10) for the first column
  • substring(InputColumn, 11, 20) for the second column
  • substring(InputColumn, 31, 30) for the third column

Let me know if you need more specific guidance on configuring a particular transformation!