"Extract complex excel It takes a lot of work to extract the irregular format excel with the conv .."

Jerry RaqForum 14 No.
583 View • 4 Years ago

Mighty Calculating Functions

Extract complex excel

It takes a lot of work to extract the irregular format excel with the conventional language. esProc can easily read excel data. First, the data is structured into “sequence table”, and then it uses the powerful SPL script to do all kinds of analysis and processing directly.

Single row header, multi row header, cross table, main sub table, free format, etc. can be extracted step by step through simple coding, and then imported into database or other Excel files.

More…

Extract Multi Header Excel and import into database

Import specific data

Multi layer JSON data file

Importing complex format text data is the difficulty of Excel. It is very troublesome to use expression step by step or write VBA. It is much easier to deal with this kind of problems with esProc SPL script. It has carefully designed a set of concise model and agile syntax, which makes it easier to deal with complex data.

Easily handle various semi-structured and unstructured data such as JSON, XML, HTML, log, etc.

More…

Merge and split excel

Merge multiple sheets from different Excel files

To merge and split excel with esProc, it is no longer necessary to write complex and inefficient VBA. A few simple lines of SPL code can complete the task, no matter how many files or how large the file is.

Merge worksheets in multiple files, merge data in different ranges in worksheets, merge multiple data items, merge data rows with the same data items, update the current table with other table data, split a column into multiple columns, and split a large table into several small tables.

More…

Comparison and Deduplication

Compare two data files

Deduplicate with different names

Compare files to find new, deleted, and modified data rows; delete duplicate data based on one or multiple data fields;

More…

Combine Excel and Related Query

Join querying multiple tables is one of the most difficult tasks in Excel. If you do this manually, it may take hours and still a mess. If you are an experienced Excel Professional, you can rely on vlookup and index match formula. With the help of a large number of Excel practical experience, you know how to use macros to complete the work, but sometimes the task is still very difficult. For ordinary excel users who understand join query or have SQL experience, using esProc through simple coding can easily complete various complex join queries, which can greatly save your time.

More…

Group summary

There are many options available in Excel to get group summary result, such as number of rows, maximum or minimum, average or sum of each group. But there are still a lot of actions that you can’t perform through the GUI, such as getting the last or first item in each group. Pandas can solve this problem, but to master pandas, you need to know Python first, and it will take at least a few weeks. esProc is ready to be installed and used. You only need basic programming experience to get started quickly. Simple data model, generic syntax features, more concise than other languages. For many grouping operations that are cumbersome to implement, you only need one line using esProc. Part of the code is as follows:

How to get rows greater than the median in a group? What is the largest increase in the group? Which groups had a continuous rise?

More…

Sorting and ranking

Sort by total number of employees in each state

It is not troublesome to sort a field in ascending or descending order or by a specified list in Excel, but it is troublesome to sort by a summary result. esProc saves the calculation result of each step in the grid variable, which is convenient for subsequent calculation and provides a simple and easy-to-use sorting method by list and range.

Student grade ranking in class, school and global

When processing data in Excel, it is often necessary to add some calculation columns. It is very convenient for Excel to calculate the original data with formulas, but when there are multiple grouping and loop calculations, it is not only difficult to write with VBA, but also difficult to debug. The code of esProc is presented in the grid, the grid is the statement execution unit, and the grid value is the execution result of each step. It can be executed in a single step, and the breakpoint can be set to debug results, which makes the natural unity of action and result.

Transposition

Bidirectional transposition

First, the columns are converted to rows, converting online and store to field values of category column. Then row to column, the unique value of the day field is converted to the column name.

Dynamic transposition

If the number of columns after row to column conversion is uncertain, or even the column names cannot be determined, the pivot function cannot be used, and the dynamic transposition method needs to be used.

More…

Text and Datetime Operation

Find the most frequently used search criteria

Use “?” or “=” as the separator to split a string into two texts. Use @1 option with s.split(d) function to find the first d. A6’s Value is bigdata.

Calculate the average daily sales for a quarter

days(dateExp) function gets the number of days in the year, quarter or month that the specified date dateExp belongs to. @q option is used to get the number of days in the quarter that the specified date belongs to.

A5’s Value:

More…

Big File Supported

Join a large file with a small file

Write data in a large text file to different files according to judgements

Results of A3, B4 of the first loop

More…

Automated manual work

For example, as a background person in the FMCG industry, it is often necessary to prepare and print reports for sales personnel. Detailed transactions of customers have been exported to .CSV file in advance. The row data needs to be polished before printing. Delete unnecessary data rows, highlight the rows exceeding the credit limit, enlarge the number in the total column, and provide the salesperson and other details at the top of the worksheet, etc.

With a simple script, esProc can import CSV file into internal table, analyze and summarize each user’s data, generate excel object with template, mark and fill in according to the output requirement.