Excel and DataTables : DEMO 1 - Working with Excel files

출처 : UiPath Academy

DEMO 1 - Working with Excel files 1

확장자가 다른 두 파일의 데이터 읽기, 데이터 테이블 필터링 및 데이터 통합 그리고 다른 파일에 데이터 쓰기

 

file 1 : data1.xlsx

Founding Year	Company	Revenue
1999	ACME Corp	69$
2005	ACME INC.	1337$
1974	ACME Heavy Industries	445$
2014	ACME Space Corp	1234$
2019	ACME Terraforming	5321$

 

file 2 : data2.xls

Revenue	Founding	Company Name
123$	2003	BrainWorks Industries
763$	2018	Green Coal Mineres Association
837$	1939	Military Industrial Inc.
4356$	2012	Horizon View Corp
753$	1998	Paradise Inc

---

Practice 1 - Working with Excel files 1

Filter Data Table outputDT1

[ 참고 ]

• Filter Wizard의 Filter Rows 섹션의 Column 필드는 대소문자를 구분하므로 열의 정확한 이름을 입력해야한다.
• 비교는 이중 정수값을 사용하여 이루어 지므로 2005는 2개의 소수 값으로 작성된다는 점에 유의한다.

 

Build Data Table filteredCompanies

 

1.1 Sequence (Sequence)							
	Private = False						
	Variables						
		outputDT1(DataTable)					
		outputDT2(DataTable)					
		filteredCompanies(DataTable)					
	Activities						
		1.59 Read Range XSLX (ReadRange)					
			DataTable = outputDT1				
			AddHeaders = True				
			PreserveFormat = False				
			WorkbookPath = Data\data1.xlsx				
			SheetName = Sheet1				
			Private = False				
		1.51 Excel Application Scope Input file XLS (ExcelApplicationScope)					
			WorkbookPath = Data\data2.xls				
			Visible = True				
			CreateNewFile = True				
			AutoSave = True				
			ReadOnly = False				
			Private = False				
			Body				
				1.52 Do (Sequence)			
					Private = False		
					Activities		
						1.53 Read Range XLS (ExcelReadRange)	
							DataTable = outputDT2
							AddHeaders = True
							UseFilter = False
							PreserveFormat = False
							SheetName = Sheet1
							Private = False
		1.41 Filter Data Table outputDT1 (FilterDataTable)					
			DataTable = outputDT1				
			SelectColumnsMode = Keep				
			FilterRowsMode = Keep				
			DataTable = outputDT1				
			Private = False				
			SelectColumns				
				value = "Company", Type = String, Direction = In			
				value = "Revenue", Type = String, Direction = In			
				value = "Founding Year", Type = String, Direction = In			
		1.31 Filter Data Table outputDT2 (FilterDataTable)					
			DataTable = outputDT2				
			SelectColumnsMode = Keep				
			FilterRowsMode = Keep				
			DataTable = outputDT2				
			Private = False				
			SelectColumns				
				value = "Company Name", Type = String, Direction = In			
				value = "Revenue", Type = String, Direction = In			
				value = "Founding", Type = String, Direction = In			
		1.28 Build Data Table filteredCompanies (BuildDataTable)					
			DataTable = filteredCompanies				
			Private = False				
		1.19 For Each Row outputDT1 (ForEachRow)					
			DataTable = outputDT1				
			Private = False				
			Body				
				1.20 Body (Sequence)			
					Private = False		
					Activities		
						1.21 Add Data Row (AddDataRow)	
							DataTable = filteredCompanies
							ArrayRow = row.ItemArray
							Private = False
		1.10 For Each Row outputDT2 (ForEachRow)					
			DataTable = outputDT2				
			Private = False				
			Body				
				1.11 Body (Sequence)			
					Private = False		
					Activities		
						1.12 Add Data Row (AddDataRow)	
							DataTable = filteredCompanies
							ArrayRow = row.ItemArray
							Private = False
		1.2 Excel Application Scope output XSLX (ExcelApplicationScope)					
			WorkbookPath = Data\output\CompaniesBefore2005.xlsx				
			Visible = True				
			CreateNewFile = True				
			AutoSave = True				
			ReadOnly = False				
			Private = False				
			Body				
				1.3 Do (Sequence)			
					Private = False		
					Activities		
						1.4 Write Range (ExcelWriteRange)	
							StartingCell = A1
							DataTable = filteredCompanies
							AddHeaders = True
							SheetName = Sheet1
							Private = False

 

>> Output : CompaniesBefore2005.xlsx

 

Company	Revenue	Founding Year
ACME Corp	69$	1999
ACME Heavy Industries	445$	1974
BrainWorks Industries	123$	2003
Military Industrial Inc.	837$	1939
Paradise Inc	753$	1998