Welcome!

Related Topics: SYS-CON MEDIA

SYS-CON MEDIA: Article

Excel Data Processing Example

A case to illustrate reading, calculating and writing Excel data in batch more efficiently

It is convenient to read and write Excel files with esProc. We'll illustrate this through an example:

Example description: Read data of orders and sellers' information list from data.xlsx, compute sales amount of every department according to the two sheets, and enter the

result into result.xlsx.

Original Excel File: data.xlsx contains two sheets. The data of orders is shown in the following figure:

1

 

Sellers' information list is as follows:

Excel2

 

esProc code:

Excel3

 

A1-A2:Read the first sheet, data of orders, and the second sheet, sellers' information list, respectively from the excel file, data.xlsx, and stores them in cell A1 and A2 in the form of table

sequence.

A3-A4:First, make a left join of data of orders and seller's information list according to employee ID numbers, then seek the sales amount

of each department by grouping and summarizing. We won't discuss the algorithm here for it is not important in this

example.

A5:Store the result in result.xlsx.

result.xlsx

Excel4

 

Code interpretation

Ø Column name

[email protected],[email protected]s column name of esProc

table sequence. For example, click cell A1 and the values of its variables can be seen:

Excel5

 

Therefore, the algorithm that follows can access data in A1 using column names directly.

Ø Sheet name

The above code reads data according to the sheet's serial number, but sometimes, clients prefers sheet name. This

demand can be realized in esProc. For instance, "sales" is the sheet name for data of orders. In this case, reading by names will be realized only by changing the sheet's serial

number in A1 into sheet name directly. See below:

file("E:/data.xlsx").[email protected](;"sales")

The same will do when writing a result into a file. Say, we want to export result in A4 to a sheet named summary,

the code is:

file("E:/result.xlsx").[email protected](A4;"summary")

Ø Scope of data

The above code is to read data from the first row of sheet to the end by default. In reality, however, we are often

confronted with cases that a portion of the data is to be read. As shown in the following figure:

Excel6

Importxls function defines the scope of row numbers waiting to be read. Say, reading from the 4th row, which could be written as:

file("E:/data.xlsx").[email protected](;1,4)

Reading from the 4th row to 1000th row, which could be written as:

file("E:/data.xlsx").[email protected](;1,4:1000)

Column numbers for reading can also be defined. Say, three columns OrderID, SellerId and Amount are to be read, the

code for this could be:

file("E:/data.xlsx").[email protected](OrderID,SellerId,Amount;1)

Or read by column numbers:

file("E:/data.xlsx").[email protected](#1,#3,#4;1)

Extension:

With for loop statement, esProc's can read and write Excel files in batches.

With parameters and macros, esProc makes computations based on Excel data source more flexible.

By providing computations of multiple data sources, esProc is able to do hybrid computation of database, text files

and Excel, as well as data migration.

 

More Stories By Jessica Qiu

Jessica Qiu is the editor of Raqsoft. She provides press releases for data computation and data analytics.

Latest Stories
All organizations that did not originate this moment have a pre-existing culture as well as legacy technology and processes that can be more or less amenable to DevOps implementation. That organizational culture is influenced by the personalities and management styles of Executive Management, the wider culture in which the organization is situated, and the personalities of key team members at all levels of the organization. This culture and entrenched interests usually throw a wrench in the work...
SYS-CON Events announced today that Calligo, an innovative cloud service provider offering mid-sized companies the highest levels of data privacy and security, has been named "Bronze Sponsor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Calligo offers unparalleled application performance guarantees, commercial flexibility and a personalised support service from its globally located cloud plat...
In his session at 20th Cloud Expo, Mike Johnston, an infrastructure engineer at Supergiant.io, discussed how to use Kubernetes to set up a SaaS infrastructure for your business. Mike Johnston is an infrastructure engineer at Supergiant.io with over 12 years of experience designing, deploying, and maintaining server and workstation infrastructure at all scales. He has experience with brick and mortar data centers as well as cloud providers like Digital Ocean, Amazon Web Services, and Rackspace. H...
SYS-CON Events announced today that Massive Networks will exhibit at SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Massive Networks mission is simple. To help your business operate seamlessly with fast, reliable, and secure internet and network solutions. Improve your customer's experience with outstanding connections to your cloud.
FinTechs use the cloud to operate at the speed and scale of digital financial activity, but are often hindered by the complexity of managing security and compliance in the cloud. In his session at 20th Cloud Expo, Sesh Murthy, co-founder and CTO of Cloud Raxak, showed how proactive and automated cloud security enables FinTechs to leverage the cloud to achieve their business goals. Through business-driven cloud security, FinTechs can speed time-to-market, diminish risk and costs, maintain continu...
21st International Cloud Expo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, will feature technical sessions from a rock star conference faculty and the leading industry players in the world. Cloud computing is now being embraced by a majority of enterprises of all sizes. Yesterday's debate about public vs. private has transformed into the reality of hybrid cloud: a recent survey shows that 74% of enterprises have a hybrid cloud strategy. Me...
IoT is at the core or many Digital Transformation initiatives with the goal of re-inventing a company's business model. We all agree that collecting relevant IoT data will result in massive amounts of data needing to be stored. However, with the rapid development of IoT devices and ongoing business model transformation, we are not able to predict the volume and growth of IoT data. And with the lack of IoT history, traditional methods of IT and infrastructure planning based on the past do not app...
SYS-CON Events announced today that DXWorldExpo has been named “Global Sponsor” of SYS-CON's 21st International Cloud Expo, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Digital Transformation is the key issue driving the global enterprise IT business. Digital Transformation is most prominent among Global 2000 enterprises and government institutions.
SYS-CON Events announced today that Datera, that offers a radically new data management architecture, has been named "Exhibitor" of SYS-CON's 21st International Cloud Expo ®, which will take place on Oct 31 - Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Datera is transforming the traditional datacenter model through modern cloud simplicity. The technology industry is at another major inflection point. The rise of mobile, the Internet of Things, data storage and Big...
"Outscale was founded in 2010, is based in France, is a strategic partner to Dassault Systémes and has done quite a bit of work with divisions of Dassault," explained Jackie Funk, Digital Marketing exec at Outscale, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"At the keynote this morning we spoke about the value proposition of Nutanix, of having a DevOps culture and a mindset, and the business outcomes of achieving agility and scale, which everybody here is trying to accomplish," noted Mark Lavi, DevOps Solution Architect at Nutanix, in this SYS-CON.tv interview at @DevOpsSummit at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
SYS-CON Events announced today that Calligo has been named “Bronze Sponsor” of SYS-CON's 21st International Cloud Expo®, which will take place on Oct 31 – Nov 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Calligo is an innovative cloud service provider offering mid-sized companies the highest levels of data privacy. Calligo offers unparalleled application performance guarantees, commercial flexibility and a personalized support service from its globally located cloud platfor...
"We were founded in 2003 and the way we were founded was about good backup and good disaster recovery for our clients, and for the last 20 years we've been pretty consistent with that," noted Marc Malafronte, Territory Manager at StorageCraft, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
"DivvyCloud as a company set out to help customers automate solutions to the most common cloud problems," noted Jeremy Snyder, VP of Business Development at DivvyCloud, in this SYS-CON.tv interview at 20th Cloud Expo, held June 6-8, 2017, at the Javits Center in New York City, NY.
Kubernetes is an open source system for automating deployment, scaling, and management of containerized applications. Kubernetes was originally built by Google, leveraging years of experience with managing container workloads, and is now a Cloud Native Compute Foundation (CNCF) project. Kubernetes has been widely adopted by the community, supported on all major public and private cloud providers, and is gaining rapid adoption in enterprises. However, Kubernetes may seem intimidating and complex ...