What is the maximum size of a CSV file that an Adapter can read?

Document ID : KB000022585
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction: 

The CSV file format is a popular file format for CA Business Service Insight Text Adapters. The size of the files can grow very large and any size limitations should be known prior to creating.

Question: 

What is the maximum size of a CSV file that an Adapter can read?

Answer: 

The CSV file standards do not seem to have a limit on the number of rows, columns or size but is limited by the program using it and the amount of available memory on the system. Depending on the program used to create the CSV file there may be limitations of 255 or 1024 columns and 65536 or 250000 rows using programs like Open Office or Microsoft Excel. BSI does not have a set limit other than the amount of memory available to the process. Since BSI adapters are bound by the limit of 32-bit processes the maximum amount of memory it can utilize, assuming it is also free in the system, is roughly 1.8 gig.

The method of how the adapter reads this file will determine how much memory is required as well. BSI text adapters will try to read the entire file the first time it is run. If the end of file is modified (not recreated) then the adapter can continue reading from the last line that was previously processed which reduces the amount of ram memory required on subsequent reads of the edited file. Modifying lines already read in this method is not supported using the text adapter without rereading through the entire file. Caution should be exercised when using a CSV file with no set limits though for two reasons. First, if anything should cause you to have to reread the entire file the process memory limitations may be reached. Secondly, if you need to view your source data to confirm or troubleshoot then you may also be bound by the limitations of the program you use to review this file.

Another method for reading CSV files in a more dynamic way is to use a SQL adapter in conjunction with Microsoft's ODBC driver. This method may have additional limitations on size and the performance of the adapter may be diminished because each time the adapter is run it will effectively be reviewing each record. This method is often used to offset issues reading CSV files which have characteristics that cause problems with standard formatting. The adapter process will still process in chunks but these will be treated the same as reading records from a database.

The most common method used by clients currently is for CSV files to be created as dated files by month or week and are backed up before being deleted to ensure they can be recovered later. Keeping these files smaller makes handling and organizing the files easier. It also makes transferring these files between UAT, Test, Development, and Production servers much simpler and quicker. Either way understanding the differences between how these methods work will help to ensure you don't encounter any issues with the size of your CSV files.

Additional Information:

Data Gathering and Adapters chapter of the BSI Wiki:

https://docops.ca.com/ca-business-service-insight/8-3-5/en/design-and-implementation/data-gathering-and-adapters