{"id":108877,"date":"2026-03-30T14:09:00","date_gmt":"2026-03-30T14:09:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=108877"},"modified":"2026-04-03T12:54:59","modified_gmt":"2026-04-03T12:54:59","slug":"from-python-to-esproc-spl-part-1-how-to-set-up-your-data-analysis-environment","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/development\/from-python-to-esproc-spl-part-1-how-to-set-up-your-data-analysis-environment\/","title":{"rendered":"How to set up a data analysis environment in esProc SPL (compared to Python)"},"content":{"rendered":"\n

This article is the first in this six-part “Moving from Python to esProc SPL”<\/a> series. You’ll learn how to set up esProc SPL, install it on different operating systems, configure your development environment, and load your first dataset. You’ll also write your first SPL script and compare the setup process with Python. By the end of this first article, you’ll have a fully-functional esProc SPL environment and be ready to look into its capabilities in-depth.<\/strong><\/p>\n\n\n\n

Data analysis is an important skill, and Python<\/a> has been the go-to language for many analysts and data scientists over the years. With libraries like Pandas<\/a> and NumPy<\/a>, Python makes handling structured data easy. However, as datasets grow larger and queries become more complex, performance issues arise – which is where esProc SPL<\/a> (Structured Process Language) comes in.

esProc SPL is designed specifically for structured data analysis, offering a simpler and more efficient approach to handling large-scale data operations. Unlike Python, which requires external libraries and extensive code to manipulate data, esProc SPL provides a streamlined, SQL<\/a>-like syntax that reduces complexity and improves performance.<\/p>\n\n\n\n

What is esProc SPL, and why should Python users care?<\/h2>\n\n\n\n

esProc SPL is a data computing language designed specifically for structured data processing. Unlike Python, which requires external libraries like Pandas for data manipulation, esProc SPL is built from the ground up to handle structured data efficiently. It combines the best aspects of SQL\u2019s<\/a> data manipulation capabilities with the flexibility of a scripting language, making it an excellent tool for filtering, grouping, and aggregating datasets.<\/p>\n\n\n\n

One of the key advantages of esProc SPL is its simplified syntax. Operations that require multiple lines of Pandas code can often be accomplished in a single SPL statement. Additionally, its cellset structure optimizes performance for large datasets, reducing memory overhead and improving execution speed. esProc SPL allows users to perform SQL-like operations directly on files without requiring a database, making it a practical choice for working with CSV<\/a>, JSON<\/a>, and Excel<\/a> data.<\/p>\n\n\n\n

For Python users, esProc SPL is not a replacement but a complementary tool that can handle the heavy lifting in data transformation before passing results to Python for visualization or machine learning tasks. It’s particularly useful for those who find Pandas\u2019 syntax complex, or those who need a more SQL-like approach without the overhead of a full database.<\/p>\n\n\n\n

How to install esProc SPL on Windows, macOS and Linux: step-by-step guide<\/h2>\n\n\n\n

Here’s how to install esProc SPL on Windows, macOS, and Linux:<\/p>\n\n\n\n

How to install esProc SPL on Windows<\/h3>\n\n\n\n

Visit the official esProc SPL download page<\/a> and download the Windows installer. To run the installer, double-click the .exe file and follow the on-screen instructions. After installation, launch esProc SPL from the Start menu.<\/p>\n\n\n\n

How to install esProc SPL on macOS<\/h3>\n\n\n\n

Download the macOS version of esProc SPL from the website<\/a>. Open the .dmg file and drag esProc SPL to the Applications folder. To launch esProc SPL, open the application from the Launchpad or Terminal.<\/p>\n\n\n\n

How to install esProc SPL on Linux<\/h3>\n\n\n\n

First, you’ll need to download the Linux archive by getting the .tar.gz file from the official site. Then, extract the files: tar -xvzf esProc-linux.tar.gz<\/code>, and run esProc SPL: <\/p>\n\n\n\n

cd esProc \n.\/esProc\n<\/pre><\/div>\n\n\n\n
How to configure your development environment (IDE options)<\/h2>\n\n\n\n
The esProc IDE (integrated development environment) comes with several configuration options, allowing you to tailor your development experience for better efficiency. Adjusting these settings can improve readability, performance, and overall workflow. <\/p>\n\n\n\n
Before coding, you may want to customize the IDE to suit your preferences. To access the settings, click on “Tools” in the top menu and select “Options” to open the configuration dialog.
 
One of the first things you can adjust is the editor\u2019s appearance. Choosing a monospaced font like Consolas<\/a> or Source Code Pro<\/a> can improve code readability. A comfortable font size, typically between 12 to 14pt, ensures your scripts remain clear and easy to read. You can switch between a light or dark theme depending on your preference.
 
Optimizing memory allocation is necessary for performance if you plan to work with large datasets. Navigate to the “Performance” tab in the settings and increase the maximum memory allocation. A good starting point is 50% of your system\u2019s available RAM, which helps the IDE handle large-scale computations smoothly.
 
Another useful configuration is setting default file locations. You can specify where your SPL scripts should be saved and define default directories for datasets, ensuring quick access to frequently used files.<\/p>\n\n\n\n
Keyboard shortcuts for faster workflow<\/h3>\n\n\n\n
Mastering keyboard shortcuts in esProc SPL can significantly improve your productivity. The following shortcuts help streamline your workflow:
 
F5 \u2013 Executes the current script

F9 \u2013 Runs a selected block of code

Ctrl + Space \u2013 Activates code completion

Ctrl + \/ \u2013 Comments or uncomments selected lines

Ctrl + S \u2013 Saves the current script

F1 \u2013 Opens the help documentation
 
You can view and customize additional shortcuts by navigating to the “Keyboard” section in the Options dialog. Configuring the IDE and familiarizing yourself with these shortcuts will make coding in esProc SPL more efficient and enjoyable. For this guide, we’ll focus on using the esProc IDE, as it provides the most seamless experience for beginners.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Simple Talk is brought to you by Redgate Software<\/h3>\n                
\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                                            Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
How to load your first dataset in esProc SPL: CSV, JSON, and database connections<\/h2>\n\n\n\n
Now that you’ve installed and configured esProc SPL, it’s time to start working with actual data. One of the first skills you need to understand is loading data from various sources. In this section, I’ll walk you through importing data from CSV files, JSON documents, and database connections using the esProc IDE.<\/p>\n\n\n\n
How to work with CSV in esProc SPL<\/h3>\n\n\n\n
Comma-separated value (CSV)<\/a> files are among the most common formats for storing tabular data. They’re simple text files where each line represents a row, and values are separated by commas (or other delimiters). Loading CSV files in esProc SPL is remarkably simple. <\/p>\n\n\n\n
To load a CSV file in the esProc IDE, you’ll use the `file()` function combined with the `import@ct()` method. Open the esProc IDE and create a new script. Then, type the following code:<\/p>\n\n\n\n
= file(\"data\/sales.csv\").import@ct()<\/code> (load a CSV file with headers)<\/p>\n\n\n\n
=A1.to(5)<\/code> (display the first 5 rows to verify that the data loaded correctly)
 
When you run this code, EsProc will load the CSV file and display the results in cell A1. The `import@ct()` method tells EsProc to import the file as a table, with the first row treated as column headers. The `@c` option specifies that the file is comma-separated, which is typical for CSV files. The `t` option indicates that the first row contains column names.<\/p>\n\n\n\n
Let’s look at the output you’ll see in cell A2:<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
This output shows the first 5 rows of your dataset, with column headers and the corresponding values. You can see that esProc has correctly identified the structure of your data, including the column names and data types. The ID and Quantity columns contain integers, Price contains decimal numbers, Product contains text strings, and Date contains date values.<\/p>\n\n\n\n
How does working with CSV in esProc SPL compare to doing so in Python?<\/h3>\n\n\n\n
If you’re coming from Python, the equivalent code using pandas would look like this:<\/p>\n\n\n\n
import pandas as pd\n\n# Load the CSV file\ndf = pd.read_csv('data\/sales.csv')\n\n# Display the first 5 rows\nprint(df.head())\n<\/pre><\/div>\n\n\n\n
Notice how esProc SPL achieves the same result with a more straightforward syntax. You don’t need to import any libraries or modules \u2013 the file handling capabilities are built right into the language.<\/p>\n\n\n\n
Sometimes, your CSV files might have different delimiters or require specific data type conversions. esProc gives you full control over these options:<\/p>\n\n\n\n
\"The<\/figure>\n\n\n\n
The output for A2 will show you the structure of your dataset:<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
Notice how the ID column is now treated as a string<\/a> (as specified in our data type parameter) rather than an integer<\/a>. This level of control is particularly useful when working with data that contains leading zeros in codes or other special formatting requirements.<\/p>\n\n\n\n
After loading your CSV data, you might want to explore it further. The esProc IDE allows you to examine your data directly in the cell results. Simply click on the cell containing your dataset (A1 in our example), and you’ll see a preview of the data. <\/p>\n\n\n\n
For a more detailed view, right-click on the cell and select “Browse” from the context menu. This opens the Data Browser, where you can sort columns, apply filters, and get a better understanding of your data.<\/p>\n\n\n\n
How to work with JSON files in esProc SPL<\/h3>\n\n\n\n
JavaScript Object Notation (JSON)<\/a> has become increasingly popular for data exchange due to its readable format and compatibility with web technologies. esProc SPL makes working with JSON files just as easy as CSV files. To load a JSON file in esProc, you’ll use the `file()` function with the `read()` method:<\/p>\n\n\n\n
 <\/td>A<\/td><\/tr>
1<\/td>file(\"\/Users\/Chisom\/Documents\/sales.json\").read()<\/code><\/td><\/tr>
2<\/td>= json(A1)<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
When working with JSON files in esProc SPL, you might find that different approaches work better depending on your specific environment and file structure. In my case, I found success with this approach. It first reads the raw content of the JSON file as text using the `read()` function without any options, then parses it into a JSON object using the `json()` function. <\/p>\n\n\n\n
This approach can be more explicit and sometimes more reliable when dealing with different JSON formats. Let’s look at the output:<\/p>\n\n\n\n
\"An<\/figure>\n\n\n\n
This output shows five records from your JSON file, now structured as a table with columns for each property in the JSON objects. esProc has automatically detected the structure and converted it to a format that’s easy to work with for data analysis.<\/p>\n\n\n\n
How do JSON files work in Python?<\/h3>\n\n\n\n
In Python, you would typically use pandas to achieve a similar result:<\/p>\n\n\n\n
import pandas as pd\nimport json\n\n# Read the JSON file\nwith open('documents\/sales.json', 'r') as f:\n    data = json.load(f)\n\n# Convert to DataFrame\ndf = pd.DataFrame(data)\n\n# Display the first 5 rows\nprint(df.head(5))<\/pre><\/div>\n\n\n\n
Notice how esProc simplifies this process with its built-in JSON handling capabilities. You don’t need to import multiple libraries or write separate code for reading and converting the data. To verify the column structure, use:<\/p>\n\n\n\n
 <\/td>A<\/td> <\/td><\/tr>
3<\/td>= A2.fno().new(A2.fname(~):Field, A2.field(~):Type)   <\/code><\/td>Check the structure of the table  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\"\"<\/figure>\n\n\n\n
It gives a table with field names and data types.<\/p>\n\n\n\n
JSON files can have complex nested structures. esProc provides powerful tools for handling these as well:<\/p>\n\n\n\n
 <\/td>A<\/td> <\/td><\/tr>
1<\/td>=file(\"\/Users\/Chisom\/Documents\/sales.json\").read()<\/code><\/td> <\/td><\/tr>
2<\/td>= json(A1)<\/code><\/td> <\/td><\/tr>
3<\/td>  = A2.derive(Customer.name:CustomerName, Customer.email:CustomerEmail<\/code>)<\/td>Extract nested customer information.  <\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The output will look like this:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this example, the `derive()` method creates new columns by extracting values from nested objects. The syntax `customer.name:CustomerName` tells esProc to take the `name` property from the `customer` object and create a new column called `CustomerName`. This method makes it easy to work with nested JSON structures without writing complex extraction logic.<\/p>\n\n\n\n
How to connect to databases with esProc SPL<\/h2>\n\n\n\n
One of esProc SPL’s strengths is its ability to connect directly to various databases. This capability is built into the language, eliminating the need for additional database connector libraries.<\/p>\n\n\n\n
To connect to a database in the esProc IDE, you first need to configure a database connection. Click on “Tools” in the top menu, then select “Database Connection.” In the dialog that appears, click “New” to create a connection. You’ll need to provide details such as the database type, host, port, username, and password. After saving your connection, you can use it in your scripts.<\/p>\n\n\n\n
How do you query a database in esProc SPL?<\/h3>\n\n\n\n
 <\/td>A<\/td> <\/td><\/tr>
1<\/td>  = connect(\"SalesDB\")    <\/code><\/td>  Connect to a configured database<\/td><\/tr>
2<\/td>  = A1.query(\"SELECT * FROM sales WHERE region = 'West' LIMIT 5\")  <\/code><\/td>  Execute a SQL query  <\/td><\/tr>
3<\/td>  = \"Number of rows: \" + A2.len()        <\/code><\/td>  Display information about the result set<\/td><\/tr>
4<\/td>= \"Number of columns: \" + A2.fno()<\/code><\/td> <\/td><\/tr>
5<\/td>   = A1.close()  <\/code><\/td>  Close the connection<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
When you run this code, esProc will connect to your database, execute the SQL query, and store the results in cell A2. The output will show a table with five rows containing sales data from \u201cwest\u201d region, the number of rows retrieved and the number of columns in the dataset. esProc has retrieved the data and converted it into a cellset (esProc’s equivalent of a DataFrame<\/a>), which you can now analyze using SPL’s data manipulation capabilities. <\/p>\n\n\n\n
How do you query a database in Python?<\/h3>\n\n\n\n
In Python, you would typically use a combination of libraries like SQLAlchemy<\/a> or a database-specific connector along with pandas:<\/p>\n\n\n\n
import pandas as pd\nimport mysql.connector\n\n# Connect to the database\nconn = mysql.connector.connect(\n    host=\"localhost\",\n    user=\"username\",\n    password=\"password\",\n    database=\"mydatabase\"\n)\n\n# Execute a query and load results into a DataFrame\nquery = \"SELECT * FROM customers WHERE region = 'West' LIMIT 5\"\ndf = pd.read_sql(query, conn)\n\n# Display the results\nprint(df)\n\n# Close the connection\nconn.close()<\/pre><\/div>\n\n\n\n
How does esProc SPL simplify the process of querying a database?<\/h3>\n\n\n\n
esProc simplifies this process by handling the connection and data retrieval in a more integrated way. You don’t need to import multiple libraries or write boilerplate connection code \u2013 just use the `connect()` function with your pre-configured connection name. esProc also makes it easy to work with stored procedures:<\/p>\n\n\n\n
 <\/td>                          A<\/td> <\/td><\/tr>
1<\/td>   = connect(\"SalesDB\")  <\/code><\/td>  Connect to the database  <\/td><\/tr>
2<\/td>  = A1.call(\"GetCustomerOrders\", [101])   <\/code><\/td>  Call a stored procedure with parameters  <\/td><\/tr>
3<\/td>   = A2.fetch(5) <\/code><\/td>  Fetch and display the first 5 results  <\/td><\/tr>
4<\/td>  = A1.close()    Close the connection  <\/code><\/td>  Close the connection<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The output (A3) shows the first five orders for customer 101, retrieved by calling the `GetCustomerOrders` stored procedure. The `call()` method takes the procedure name as the first parameter and an array of parameter values as the second parameter.<\/p>\n\n\n\n
One of the advantages of using esProc for database operations is that you can seamlessly combine SQL queries with SPL’s data manipulation capabilities. For example, you can retrieve data from a database and then perform additional transformations that might be difficult or verbose to express in SQL:<\/p>\n\n\n\n
 <\/td>                                A<\/td> <\/td><\/tr>
1<\/td>= connect(\"SalesDB\")<\/code><\/td> <\/td><\/tr>
2<\/td>= A1.query(\"SELECT product, category, sale_date, amount FROM sales WHERE sale_date >= '2023-01-01'\")    <\/code><\/td>Retrieve sales data  <\/td><\/tr>
3<\/td>   = A2.derive(month(sale_date):Month)     <\/code><\/td>Extract month from sale_date<\/td><\/tr>
4<\/td> = A3.groups(category, Month; sum(amount):MonthlySales)    <\/code><\/td>  Group by category and month, summing sales amounts<\/td><\/tr>
5<\/td>  = A4.pivot(category, Month; MonthlySales)    <\/code><\/td>  Pivot the data to display monthly sales per category  <\/td><\/tr>
6<\/td>   = A5.fetch()       <\/code><\/td>  Fetch and display results<\/td><\/tr>
7<\/td>   = A1.close()  <\/code><\/td>  Close the connection<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The output (A6) will be a pivot table showing total monthly sales per category. This example demonstrates how you can combine SQL and SPL to create a powerful data analysis workflow. First, you retrieve the raw sales data from the database using SQL. Then, you use SPL to extract the month from the sale date, group the data by category and month, calculate the sum of sales for each group and, finally, create a pivot table that shows monthly sales for each category. The result is a concise summary that would be much more verbose to create using SQL alone.<\/p>\n\n\n\n
Comparing the setup process: Python vs SPL<\/h2>\n\n\n\n
When you’re starting a new data analysis project, the initial setup can significantly impact your productivity and experience. Python and esProc SPL take fundamentally different approaches to this process, each with its own advantages and considerations.<\/p>\n\n\n\n
Installation<\/h3>\n\n\n\n
The installation process is where you’ll notice the first major difference between Python and esProc SPL. With Python, you’re embarking on a multi-step journey. First, you need to download and install the Python interpreter itself. A typical Python data analysis setup requires installing multiple packages:<\/p>\n\n\n\n
# Installing essential Python data analysis packages\npip install pandas numpy matplotlib seaborn scikit-learn jupyter\n<\/pre><\/div>\n\n\n\n
This command alone brings in six different packages, each with its own dependencies. While this approach gives you flexibility to install only what you need, it also requires you to know what you need in advance.<\/p>\n\n\n\n
In contrast, esProc SPL takes an all-in-one approach. You download a single package that includes the runtime environment, development IDE, and all the core functionality needed for data analysis. There’s no separate package manager to learn, no dependencies to resolve, and no version conflicts to troubleshoot. You simply download, extract, and run.<\/p>\n\n\n\n
The esProc installation process looks more like this:<\/p>\n\n\n\n
1. Download the esProc package for your operating system
<\/p>\n\n\n\n
2. Extract the files to a location of your choice
<\/p>\n\n\n\n
3. Run the executable<\/p>\n\n\n\n
It gets you up and running quickly, especially if you’re new to data analysis or don’t want to spend time configuring a development environment.<\/p>\n\n\n\n
Environment management: simplicity vs. flexibility<\/h3>\n\n\n\n
Python’s ecosystem has evolved to address the challenges of managing dependencies across different projects. Tools like virtualenv, venv, and conda environments allow you to create isolated environments with specific package versions for each project. This approach prevents conflicts between projects that might require different versions of the same library. Setting up a Python virtual environment typically involves commands like:<\/p>\n\n\n\n
# Creating and activating a virtual environment\npython -m venv myproject_env\nsource myproject_env\/bin\/activate  # On Windows: myproject_env\\Scripts\\activate\n\n# Installing project-specific packages\npip install pandas==1.4.2 matplotlib==3.5.1\n<\/pre><\/div>\n\n\n\n
You’ll need to activate this environment each time you work on the project, and you might maintain a requirements.txt file to track dependencies. While this system offers excellent flexibility for complex projects, it adds another layer of complexity to manage.<\/p>\n\n\n\n
esProc SPL takes a different approach by eliminating the need for environment management altogether. Since all functionality is built into the core product, there are no external dependencies to manage. You don’t need to create or activate environments, install packages, or track dependency versions. This approach sacrifices some flexibility but gains simplicity and consistency.<\/p>\n\n\n\n
When you open the esProc IDE, you’re immediately ready to start working with all the available features. This consistency means you don’t have to worry about whether you’ve activated the right environment or installed the correct package versions.<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Enjoying this article? Subscribe to the Simple Talk newsletter<\/h3>\n                
\n                                            Get selected articles, event information, podcasts and other industry content delivered straight to your inbox.                                    <\/div>\n            <\/div>\n                                            Subscribe now<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
Development environment: integrated vs. modular<\/h3>\n\n\n\n
The development environment is another area where Python and esProc SPL differ significantly. With Python, you have a wealth of options for your development environment. You might use a general-purpose IDE like PyCharm<\/a> or VS Code<\/a>, a notebook interface like Jupyter<\/a>, or even a simple text editor with a terminal. <\/p>\n\n\n\n
Each option has its own setup process, configuration options, and learning curve. You’ll likely spend time customizing your environment with extensions, themes, and settings to suit your preferences. A typical Python data analysis workflow might involve:<\/p>\n\n\n
\n