Table of Contents
- 1 Flat File Import Tool: Import flat file into SQL Server, Oracle and other databases with BPA Platform
- 1.1 What is a flat file database?
- 1.2 What is a flat file used for?
- 1.3 Flat file database advantages and disadvantages
- 1.4 What is the difference between flat file and relational database?
- 1.5 What is flat file integration?
- 1.6 API vs CSV – Which should you use?
- 1.7 What is a flat file import?
- 1.8 What is a flat file import tool?
- 1.9 Tutorial: How to import flat file into SQL server using stored procedure
- 1.9.1 Step One: Setting up the file location
- 1.9.2 Step Two: Import Flat File Tool
- 1.9.3 Step Three: Transform Data
- 1.9.4 Step Four: The Decision Step
- 1.9.5 Step Five: Convert XML to Recordset
- 1.9.6 Step Six: Sending Data to the Staging Database with Stored Procedure
- 1.9.7 Step Seven: File Management Tool
- 1.9.8 Step Eight: Test the task
- 1.10 Flat file import next steps
- 2 Frequently Asked Questions
Flat File Import Tool: Import flat file into SQL Server, Oracle and other databases with BPA Platform
The following article details a flat file definition, including types of flat file, working flat file examples, the difference between a flat file and a relational database, and how to import a flat file into SQL Server, Oracle and other databases by using BPA Platform’s Flat File Import Tool. There is an accompanying whitepaper, tutorial guide and video demonstrating how to import a flat file into SQL Server using stored procedure with BPA Platform. The resources can be found below:
What is a flat file database?
The definition of a flat file is a file that does not contain any internal hierarchy. Initially developed by IBM in the early 1970s, a flat file database is used to store unstructured data in plain text format. Simply put, it’s a database stored in a file and called a flat file. Individual lines of the text file hold one record with additional data fields separated by delimiters. These delimiters can be commas or tabs to help you define a structured format.
The structured format of a flat file database does not hold multiple tables that can are often found in a relational database, such as an Oracle database, MySQL or Microsoft SQL Server. Most database programs can, however, import flat file databases so that they can be used in a relational database. A flat file is often used when importing data between databases, such as importing a product catalogue or customer contact list.
An example of a flat file structure can be seen when viewing a spreadsheet of a list of products, customers, vendors or suppliers. The spreadsheet can easily be created in Microsoft Office applications and can include details such as a unique ID, company name, contact person, contact address and telephone number. The spreadsheet is often saved as a CSV file, and is easy to sort, view and extracted, as and when required.
Image: Flat File Example: Products in a CSV file
Data within a flat file does not contain:
- Any internal hierarchy
- Any links to other files
- Any word processing information or formatting
Types of flat files
Flat files can be used to transfer data from one database or system to another. Types of flat files are:
- CSV (Comma Separated Value)
- TXT files
- XML files
The difference between a flat file and a CSV file is that a CSV file uses a delimiter to separate data fields (in most instances this is a comma, although enclosing characters can be used, such as double quotes) and delimited files only use a delimiter to structure data. When formatting data, delimiters are used to find different fields within records and have a fixed width. As CSVs are used to store tabular data, they can be easily imported and exported from spreadsheets into a readable format.
What is a flat file used for?
A flat file is used for importing data into a business system, most commonly when an initial synchronisation or one-off import of a large volume of data is needed. It’s common for organisations to move large volumes of data such as customer information and product catalogues into a database or to regularly pass valuable CSV data files between colleagues, suppliers, customers or remote sites to start, support or end a manual business process.
Despite there being no data manipulation on the data that is stored in the flat file, it can carry relevant information from the server to the desired database. Its simple structure means that it uses minimal space compared to more structured files. The data within a flat file maintains its original form until it is transferred into a staging database within a business system. Once the data is in its staging area, it is then transformed into its assigned field(s).
When considering the scenario of a new eCommerce platform, such as Magento, Shopify or WooCommerce, product catalogue data needs to be imported. Bulk product uploads are usually initiated using a CSV file along with the eCommerce platform’s import routine. However, these processes are employee-intensive, prone to errors and slow to process. Automatically parsing CSV files with a dedicated tool enables you to completely eradicate repetitive employee administration, improve company performance and reduce data latency between business systems.
The downside of bulk flat file imports, especially when importing 5,000+ products, is that the CSV may be rejected due to processing errors, such as having double commas or a comma in the wrong place. This means that the file will have to be manually searched to locate the anomaly, correct the file and then re-import it with the hope that the file does not lose its formatting in the process.
Bulk import flat file errors can be avoided by utilising an extract transform load (ETL) tool that will dynamically extract the relevant data from the CSV file, transform the data so that it is compatible with the target database and load the data to the desired database location. An ETL tool can be used to perform standard import routines in bulk, with or without data transformation, or it can be used when a file import routine does not exist.
“We’ve done quite a few 3PL integrations, integrating SAP with custom warehousing modules, as well as other types of EDI systems; moving flat files between different systems that may not be mainstream with web APIs.
We completed one recently which rectified the manual processing of sending 3PL data. The customer was manually sending over dispatch notes to the separate 3PL system every day and then making sure that all the right items had been sent, which would take them about an hour and a half a day. We’ve taken that whole piece out and it now automatically passes the files electronically and the other system picks them up. They are no longer reliant on someone doing that. “
Customer Success Manager, Seidor UK
Seidor UK uses Codeless Platforms’ BPA Platform for automating and integrating a range of third-party systems
Discover the commercial benefits that you can achieve by dynamically reading flat files and triggering more sophisticated automated processes by downloading the brochure below.
Flat file database advantages and disadvantages
A flat file database, also commonly referred to as a text database, is used to store important data as a plain text file. The structured format of a flat file is easy to understand and allows the user to quickly sort results in order. Below we detail flat file advantages and disadvantages
Advantages of a flat file database
The advantages of a flat file database include:
- A good option for small databases, e.g. small volume of products in a catalogue or customer contact list
- Data records are stored in one place
- Easy for users to configure and read when using Microsoft Office applications
- Quick and easy to set up and use immediately
- Flat files provide search and sorting tools often needed by users
- Records can be easily viewed or extracted on simple criteria sets
Disadvantages of a flat file database
The disadvantages of a flat file database include:
- Flat file databases can be harder for the user to update
- Prone to formatting errors and difficult to change data format
- Flat file import routines can fail from having delimiter(s) in the wrong place
- Limited ability for more complex queries
- There are no transactions in a flat file database
- Can be prone to duplication with multiple database entries
- Not as secure or consistent as a relational database
What is the difference between flat file and relational database?
In this section we will discuss the differences between a flat file and a relational database. As we have found out so far, a flat file can be used when an initial synchronisation or one-off import of a large volume of data is needed. However, we need to understand the definition of a relational database. When deciding upon whether to use a flat file or a relational database to be deployed for a project, it’s best to weigh the pros and cons of each database solution. Key points to consider include business requirements, volumes of data and ease of use for your employees.
What is a relational database?
The definition of a relational database is a database that is structured to recognise a relationship between stored items of business data and information. A relational database, also known as a relational database management system (RDMS), is a database that stores business data whilst allowing access to data points that are linked to each other. A relational database is often used when connected data needs to be stored and retrieved with minimal system logic required.
Relational databases provide data consistency through structured tables across business systems and applications. Data is stored in separate columns and rows. The columns and rows create a table, similar to a spreadsheet. Unlike a flat file, a relational database maintains its consistency by having rules that do not cater for duplicate rows of data in its table. This removes the risk of incorrect data entering the database, such as duplicate customer accounts. Relational databases allow the user to query the structured tables with report generation tools to produce monthly reports, such as monthly sales figures, finance projections, inventory and account statements.
Popular relational databases include:
- Microsoft SQL Server
- Oracle database
Relational databases are often used by multiple users within an organisation where employees need to access data to make changes, or increased data visibility is required throughout the business to aid decision making.
Difference between a flat file and a relational database overview
As we have found out, a flat file database stores data in a single table structure in its simplest format, and a relational database uses multiple table structures that enable the cross-referencing and querying or records between tables. The table below details the overall difference between a flat file database and a relational database.
|Flat File Database||Relational Database|
|A flat file is used to store data in a single table structure||A relational database is used to store data in a multiple table structure, e.g. columns and rows|
|A flat file is represented using a dictionary||A relational database uses Schema data|
|Easily accessible via many software applications, e.g. Microsoft Office||Uses a Relational Database Management System (RDMS) to access data|
|Can be created using software such as Microsoft Excel, Microsoft Access, FileMaker etc.||Common relational databases include MySQL, Microsoft SQL Server, Oracle database etc.|
|A flat file contains files, records, fields and characters||A relational database contains attributes and relationships|
|Used by small organisations or for bulk uploads||Used by larger organisations where many employees need to access and change data|
As mentioned above, a flat file is often used by organisations when they are commissioning a new eCommerce store and need to import product catalogue data into a business system. This provides the initial synchronisation or a one-off import of a large volume data into a relational database. The issue is that the database structures may differ with the flat file data structure being different to the destination database.
When using a flat file, a CSV file is often the preferred option for bulk uploads. However, it does have its limitations. When a flat file import or database integration is required, ETL systems provide the ability to synchronise data from one data source to another by extracting data from a specific source; transforming this information so that it is compatible with the target systems format or database structure and then loading the data to the database location.
Discover how your IT department can utilise process automation tools to enable business systems and databases to interact and become the main driver for your organisation’s digital transformation strategy by downloading the eBook below.
What is flat file integration?
Flat file integration is the scenario where the initial data source and its destination data is represented in a flat file. File-based integration can be a CSV file. However, more modern business systems can require an API integration to synchronise the relevant data. File-based integration allows data synchronisation with another business system. CSV integration is traditionally used when a flat file import is required in programs that store data in tables, such as in eCommerce, data warehousing or when legacy business systems do not have an available API.
A flat file import is often used to perform the following integration scenarios:
- Importing CSV files into an ERP system, e.g. bulk eCommerce product stock import
- Passing a CSV of CRM data files between organisations, e.g. passing a file of customers approaching the end of their contract to a customer retention agency
What is an API?
An API stands for Application Programming interface. The meaning of an API defines a data synchronisation between multiple business systems and databases, such as the types of data called and requested, how to make them, the data formats that should be used and what happens next in the business process. Put simply, an API allows two or more business systems or applications to interact and ‘talk to each other’.
Modern business systems and applications, such as accounting, ERP, CRM and eCommerce web services, provide an API. An API calls back and forth between systems enabling them to interact and can provide the added ability to extend related business process automation tasks.
An API provides developers with simplified request methods that can be used during the data synchronisation. These include:
- GET: Enables you to pull information, e.g. sales order
- PUT: Used to update specific data, e.g. the updating of product pricing and lists
- POST: Allows you to create an entity, e.g. new product category
- DELETE: Delete entity/item
For more information on API integration, read this article: Simplifying data integration with an API.
What is an API used for?
An API provides organisations with the ability to allow systems and applications to interact. This means that businesses can manage their data more efficiently. An API can be used for streamlining business processes, such as an eCommerce product catalogue, to maintain the consistency of stock levels or to reduce order to fulfilment times by integrating an eCommerce platform with a selected courier service. For example, an API can be used to automatically GET sales orders from an eCommerce platform and PUT them into an ERP system in pseudo real-time.
The sales order data and associated customer information (Name, Address, Payment details, Order Number etc.) are automatically placed into the ERP system and, if required, dynamically placed for fulfilment with a courier service, all without any employee intervention.
API vs CSV – Which should you use?
Depending on the information and regularity of the data synchronisation required, both integration with an API or a flat file import through a CSV file are viable options for moving business data. Where business systems do not have an API in place, when small or irregular updates are required, or for the initial synchronisation or one-off import of a large volume of data is required, data integration often performed using a flat file import.
Depending on the size of the organisation, data volumes and business systems, an API is often seen as the preferred integration method as it can be used as a business driver to automate repetitive data entry tasks.
Using an API pros and cons
Common API pros and cons include
- Enables data to be synchronised automatically between systems and applications without employee interaction
- Provides the most recent and available information required
- Minimises data import errors, duplicates, missing records etc.
- Improves data import speed and consistency
- Easier for developers to automate business processes
- System APIs can change, and an automated process could fail. However, this is easily fixed depending on the integration tools you use
Unlike an API that calls back and forth between systems and applications, flat files, such as plain text or CSV (delimited or fixed width), do not. When a flat file import routine is required, it’s advisable to use a flat file import tool to dynamically extract, transform and load the required format and maintain data consistency.
Learn more about API integration and how system integration can become a key element in formulating your digital transformation roadmap by downloading the workbook below and by reading: A Guide to Digital Transformation.
What is a flat file import?
A flat file import is a means to import data from a flat file, such as CSV or TXT, to a new database. It’s commonly used for when your data source and its destination data must be processed in a flat file and can be used as an alternative to an API. Although it’s common practice for businesses to pass data files to another database location, it can be employee intensive and prone to manual data entry errors.
Common scenarios for importing flat files include:
- Reading CSV files to update an ERP solution: For example, passing a CSV file between manufacturer, wholesaler and retail partners for flat file EDI (Electronic Data Interchange)
- Reading CSV files to update a CRM system: For example, passing CRM data to a customer retention agency
BPA Platforms’ Flat File Import Tool can improve performance and reduce the time and associated errors of a manual import routine by providing the tools to dynamically transform and map data as and when required.
What is a flat file import tool?
BPA Platform’s Flat File Import Tool is seen as a valuable tool that can be used in numerous business process automation scenarios. It is used to import a delimited or fixed width file from a disk, extract the file content including the file properties, and then output it as either a recordset or as XML. It provides flat file mapping capabilities and can import multiple flat files into SQL Server, Oracle and other relational databases.
Where data is held as a CSV, spreadsheet or as a more complex flat file database, the Flat File Import Tool can extract data to be used as part of wider business processes, such as within eCommerce or data warehousing.
Used as part of a process with other BPA Platform Tools, the flat file import tool can be used to transform and map data to eCommerce and ERP systems, and is particularly useful for the initial synchronisation of large volumes of data, such as product and manufacturing data sheets.
The Import Flat File tool imports delimited or fixed-width files from disk into BPA Platform, extracts the file content, including any file properties, then outputs it as either a recordset or as XML. The columns that make up the recordset, or the nodes and nested elements for the XML file, can either be inferred from an imported example or created manually. Data validation can also be applied to only import required data.
Image: Flat File Mapping Example
Flat File Import Tool Features
The Flat File Import Tool provides the following features:
- Flat file mapping capability
- Import multiple flat files into SQL Server, Oracle and other relational databases
- Manually create a schema for the XML output consisting of nodes and nested elements
- Configure properties of each node and element to select the data to be extracted for each
- Preview the XML schema structure before saving and running the task
- Specify the source file properties to be extracted at task run-time and exposed to other steps
- Browse and select a file containing column headings in the first row to structure the output data
- Output XML for use by other task steps
- Output XML as a string for use by other task steps
- Output a recordset for use by other task steps
Typical uses of the Flat File Import Tool
BPA Platform’s Flat File Import Tool removes tedious data processing from employee workloads. BPA Platform’s tool set removes repetitive administration, improves company performance and reduces data latency between business systems.
Reading CSV files to auto-update an ERP solution: Stock availability is a common business file passed between a manufacturer, wholesaler and retail partners for flat file EDI imports or product catalogue updates. However, CSV files often require an employee to process the data and update an ERP system. This is both time-consuming and prone to errors.
Codeless Platforms’ BPA Platform, with its drag and drop graphical user interface, enables developers to automate the reading of files and update an ERP system to exact business rules.
Reading CSV files to auto-update a CRM solution: Passing CRM data files between companies is another common everyday business process. For example, it is common practice for large businesses in the telecoms sector to pass a file of customers approaching the end of their contract to a customer retention agency. However, like the example above, this can create a great deal of data entry and is prone to errors.
Codeless Platforms’ BPA Platform can automatically capture and read a CSV file containing CRM data and update your internal CRM application. It can also create a subsequent report on customers retained and dynamically distribute it to the original data provider.
Reading CSV files to trigger other business processes: Reading a CSV file, regardless of whether it is dynamically retrieved from an FTP site, inbound email or network location, often represents the beginning, middle or end of a manual employee process.
Codeless Platforms’ BPA Platform can be deployed to read business files and automate subsequent business processes based on the data within the file. For instance, it can identify product price changes within a file and dynamically update a web shop.
Tutorial: How to import flat file into SQL server using stored procedure
In the following example we will use BPA Platforms’ Flat File Import Tool to import a flat file into SQL Server using stored procedure. The import tool will extract product data in CSV format and use a decision step to verify that the file exists. We will then show you how to transform, loop and map the data to our required format, convert the XML to a recordset and send it to the SQL database with a stored procedure tool. We will finally use the file management tool to move the processed file from ‘awaiting processing’ to a processed folder.
- Extract CSV data
- Loop and map the CSV data
- Convert XML to recordset
- Send the data to the SQL staging database
- Move the processed files with the File Management Tool
To complete this example, you will need a staging database, a configured database connection, and a populated flat file (CSV). You will also need BPA Platform with the following tools:
- Import Flat File Tool
- Decision Tool
- Run VB Script
- Convert XML to Recordset Tool
- Call Stored Procedure Tool
- File Management Tool
Before you start, don’t forget to download the Flat File Import Tutorial Guide below and the associated BPA Platform tool whitepapers for each step.
Step One: Setting up the file location
Set up two folders on the desktop. Name the first Awaiting Processing and the second Products Processed. We need to drag our products list CSV to the Awaiting Processing folder. Notice the products and structure in the file.
The Products Processed folder is where the file will be automatically moved to when the task is run.
Image: Flat File Import Tutorial – Folder Set Up
Step Two: Import Flat File Tool
Open BPA platform, navigate to your task folder, and then create a new task. Name it “CSV ETL”. Click the Design tab.
Image: Flat File Import Tutorial: Import Flat File CSV ETL
Select Import Flat File and drag the tool onto the design area.
In the General tab, in Path, click Browse and select the Awaiting Processing folder. In File Name Type, select the file or type “products” and add a wildcard (“*”) so it can pick up any incremented or dated versions. Add “.CSV” at the end.
Image: Import Flat File Dialogue: CSV ETL
In the Main / File tab, ensure XML is selected, as this is required for the next step in the task. In Root Node Name, type “root”.
Select Header Row in File and then click Browse. We are going to use an example CSV file which BPA Platform will use to configure the output structure. Locate our product CSV file and click Open.
The file we are using is comma delimited with a standard carriage return line feed end marker. Ensure these options are selected.
In the Elements/Columns tab we can see the data structure. This can be used to change the values of each node and element. Change New Node to Products. Within here you can specify the type of data and other element properties.
Click Preview Output and then click OK.
Next, in the Options tab, we will set the all options to Continue, to allow for processing of all available files.
Click OK to close this step.
Step Three: Transform Data
We will now transform the data so that the CSV input matches the expected output.
Drag in a Transform Data Step from the browser and in the General tab, in Data Source, select Use a Recordset or XML From Another Step and select OutputXML from Import Flat File.
Image: Transform Data – CSV ETL Example
In the Main tab, click Replicate Structure From Input Data Source. We can see the output structure of the XML. Change the output name of CSV_SKU_Code to ERP_SKU_Code.
OK, let’s go to the Mapping tab. Expand the trees on both sides and here we can see the input XML on the left and Output XML on the right. Now, drag Products Node to Products. BPA Platform will offer to automatically map child nodes at this point. Click Yes.
Image: Transform Data Mapping Example
Notice the simple loop icon that appears between the two. This will ensure the step loops through all the products in the CSV file.
The CSV_SKU_Code will need to be manually mapped to ERP_SKU_CODE, as we wanted to remap the input and output on this.
Step Four: The Decision Step
As part of good practice, and to reduce unnecessary processing, we will drag in a Decision step. We will set this up to terminate the task if no files are found by proceeding to an empty run VBScript step.
Image: Import Flat File – Decision Step
Now drag in a Run VBScript step and name it END Task and click OK. Next, link these steps together.
Image: End VBScript Example
Open the Decision step and click on the Transform Data Branch and click Edit. Name it: “File exists”.
Click the Script tab and delete the word ‘true’. In the browser Environment tab, with scope set to Task, turn down the import flat file tree and drag SuccessCount into the script window.
At the end, type “> 0”. This sets the logic to take this route if a file exists. Now click Save and close.
Image: Import Flat File: Decision Step Branches
Now click the end branch and click Edit. Name it: “No File”. In the Script tab select Else.
Now click Save and Close for the branch, and Save and Close for the step.
Image: Import Flat File: Decision Step Script
Step Five: Convert XML to Recordset
Next, drag in a Convert XML to Recordset step to align the output with the required format.
Image: Convert XML to Recordset
We can go straight to the main tab and ensure data source is set to Task Step. In the drop down click Output XML From Transform Data.
In the Output Recordset configuration click All in Select Fields and click OK. Click Yes to the warning dialog, as our requirements are within safe limits.
Image: Convert Recordset to XML
Step Six: Sending Data to the Staging Database with Stored Procedure
We now need to pass the data to the staging database. Drag in a Stored Procedure OLEDB step. In the General tab change the name to Send to Staging DB. Then, ensure that Use a Recordset is selected. In the dropdown ensure Results From Convert XML to Recordset is selected.
Image: Call Stored Procedure
In the Main tab, set your database connection to your preconfigured connection, browse to your required procedure and select SP_Product_Upsert. Click OK.
On the left you will see the parameters within the staging database. We will assign the fields we want to transfer by dragging the record sources from the browser. Let’s drag in some fields to populate the database.
In addition to your chosen fields you will need to set TC_SyncStatus with a value of ‘ready’ by typing this manually.
And Allow _Insert with a value of 1. Click OK to close the step.
Image: Call Stored Procedure Dialog
Step Seven: File Management Tool
The final step moves the processed CSV file from the Awaiting Processing Folder to the Products Processed folder.
Let’s drag in a File Management step. In the General tab name it move processed files. Browse to the file source path of the CSV file, which is the Awaiting Processing folder we made previously. In File Name browse for the file, or type in the name of the CSV and add a wildcard to the filename in case it will be incremented or dated. Add .csv at the end.
Image: File Management Tool
In the Main tab, we can use this tool to copy, rename, delete or upload. For our purposes, ensure the Move option is selected.
Set the destination path by browsing to the Products Processed folder we made earlier. We will leave the other settings at their default values.
Click OK and save and close the task.
Image: File Management Tool Dialog
Step Eight: Test the task
The final step is to link the remaining steps. Click Save and Close.
In a real-world scenario this file could be added by FTP or via a network, for example.
Our task could be setup with a schedule step that runs at regular intervals.However, for demonstration purposes, let’s run it by right clicking and selecting Queue Now.
When the task has completed, look at the folders on the desktop. We can see that the file has been moved to the Products Processed folder.
In the Microsoft SQL Server Management Studio, in the Products Table, we can see that the contents of the CSV file have been transferred.
This example introduces the concepts and techniques of BPA Platform.
Image: BPA Platform Flat File Import Tutorial – Completed CSV ETL Task
Flat file import next steps
BPA Platform provides you with the ability to import multiple flat files quickly and easily into SQL Server, Oracle and many other leading relational databases. For more information on how BPA Platform can be used to automate your business processes, or to understand more on the Import Flat File tool, download the BPA Platform brochure and whitepaper below, or call us on +44 (0)330 99 88 700.