1 Convert single row-based text file to Excel

In row-based text, one row corresponds to one piece of data, which is a very common file format. For example, the following table is about the prices of fruits.

 

File name “Fruits.txt”:

Name     UnitPrice

Apple     1.69

Banana   0.69

Peach     0.88

Strawberry     1.97

 

Import the table to Excel as:

 

The code of the above operation is:

	A
1	=file("Fruits.txt").import@t()
2	=file("Fruits.xlsx").xlsexport@t(A1)

 

Here is a more complicated situation where some tabs are added in the text file to keep the data aligned as follows:

Name            UnitPrice

Apple            1.69

Banana          0.69

Peach            0.88

Strawberry     1.97

 

In this case, some de-space processing needs to be added in the following code:

 

	A	B
1	=file("Fruits.txt").import@s()	/import the text file, and @s option means to import full line without splitting fields
2	=A1.(~._1.split@t())	/use “tab” separator to split the file line by line, @t option means to delete extra space separators
3	=create(${A2(1).concat@c()})	/use the first line as column name to generate a table sequence
4	=A3.record(A2(to(2,A2.len())).conj())	/concatenate the following lines to a sequence and then store it in the table sequence in order
5	=file("Fruits.xlsx").xlsexport@t(A1)	/export the result to Excel

 

2 Concatenate multiple text files in one sheet of Excel

If we need to import several pieces of price table in the same sheet of the Excel as follows:

 

File name “Fruits.txt”:

Name     UnitPrice

Apple     1.69

Banana   0.69

Peach     0.88

Strawberry     1.97

 

File name “Meats.txt”:

Name     UnitPrice

mutton   7.69

Pork 4.58

Chicken  5.77

Duck      6.89

Beef 7.96

The expected result after importing the text files is:

 

The script of the above operation is:

	A
1	=file("Fruits.txt").import@t()
2	=file("Meats.txt").import@t()
3	=A1|A2
4	=file("Foods.xlsx").xlsexport@t(A3)

 

3 Import multiple text files in multiple sheets of Excel

Sometimes there is the situation where multiple text files need to be imported in separate sheets of the Excel, and the text file names are taken as names of the sheets.

 

File name “Fruits.txt”:

Name     UnitPrice

Apple     1.69

Banana   0.69

Peach     0.88

Strawberry     1.97

 

File name “Meats.txt”:

Name     UnitPrice

mutton   7.69

Pork 4.58

Chicken  5.77

Duck      6.89

Beef 7.96

 

The result of importing:

 

The script of the operation:

	A	B
1	[Fruits.txt,Meats.txt]
2	for A1	=file(A2).import@t()
3		==file("Foods.xlsx").xlsexport@ta(B2; A2)

 

4 Convert multiple lines into one line to generate Excel

A certain shopping mall compiles the contact information of repeat customers in the following format:

 

File name “Customers.txt”:

Peter

Mobile:13302111756

Facebook:asd003

Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China

 

Mark

Tel:83781385

Email:lisi@sina.com

Twitter:13445245

Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China

 

Alice

Mobile:12235345434

Facebook:dfg546

Twitter:456547567

Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China

 

Lily

Mobile:18902131756

Facebook:liu073

Tel:82781395

Email:liuliu@google.com

Twitter:12225245

Address:Room...,Unit...,Building...,...Road,...District,...City,...Prov,China

 

We can find that the contact information of each customer is quite different after analyzing the above data, so the corresponding lines of each customer are thus different in number in the final data table. Therefore, the text file is expected to be converted to Excel as shown below:

 

The script of the operation is:

	A	B
1	=file("Customers.txt").import@i()		/import the text file
2	=1
3	=A1.group@o(if(~==null,(A2=A2+1,A2),A2)).(~.select(~!=null))		/split into groups by taking blank lines as separation criteria
4	=create(Name,Mobile,Facebook,Tel,Email,Twitter,Address)		/generate the result table sequence
5	for A3	=A5.(~.split(":"))	/split each line by colons
6		=B5.(if(~.len()==1,"\""+~(1)+"\":Name","\""+~(2)+"\":"+~(1))).concat@c()	/take the line without colon as name, and in other lines, the left and right of colons are the column name and column value respectively
7		>A4.insert(0,${B6})	/insert the split results in the records of table sequence
8	=file("Customers.xlsx").xlsexport@t(A4)		/export the result to Excel

 

5 Split one line into multiple lines to generate Excel

A certain shopping mall exports a customer table from the system and stores the data in a txt file.

 

Name        Mobile      Facebook  Tel    Email Twitter      Address Peter 13302111756    asd003 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China Mark                  83781385  lisi@sina.com     13445245 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China Alice 12235345434    dfg546                        456547567 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China Lily    18902131756    liu073        82781395  liuliu@google.com     12225245 Room...,Unit...,Building...,...Road,...District,...City,...Prov,China

 

This table is inconvenient to view because there are too many fields and some fields are even null. we need to split one line into multiple lines, and each line is in the format of “field name: filed value”. If the value is null, then this line will not be displayed in the table. The result of converting the table and importing it to Excel is shown below:

 

The script of the operation:

	A	B	C	D
1	=file("CustomerTable.txt").import@t()
2	=A1.fname()	=[]
3	for A1	=[]
4		for A2	if(A3.${B4}!=null)	>B3.insert(0,B4+":"+string(A3.${B4}))
5		>B3.insert(0,null)
6		>B2.insert(0:B3)
7	=file("CustomerTable.xlsx").xlsexport(B2)

 

6 Import text data in specified positions of Excel template

A shopping mall collects the times and quantity of certain consumable items purchased by key customers, which are stored in different text files:

 

File name “Egg.txt”:

Name     Times     Quantity

Peter      7     42

Mark      2     2

Alice       6     6

Lily  3     6

File name “Tissue.txt”:

Name     Times     Quantity

Peter      5     5

Mark      2     10

Alice       6     24

Lily  6     24

File name “Bread.txt”:

Name     Times     Quantity

Peter      1     8

Mark      9     36

Alice       9     81

Lily  2     18

 

Import the data of text file in the corresponding positions of the Excel template which is shown as follows:

 

After importing the data, the result is:

 

The script is:

	A	B
1	[Egg,Tissue,Bread]
2	=file("Template.xlsx").xlsopen()
3	for A1	=file(A3+".txt").import@t()
4		=A2.xlscell(cellname(6,#A3*2),1;B3.(Times).concat("\n"))
5		=A2.xlscell(cellname(6,#A3*2+1),1;B3.(Quantity).concat("\n"))
6		=A2.xlscell(cellname(10,#A3*2),1;B3.(Times).sum())
7		=A2.xlscell(cellname(10,#A3*2+1),1;B3.(Quantity).sum())
8	=file("Template.xlsx").xlswrite(A2)

 

7 Transpose data to generate Excel

A shopping mall complies the times of certain consumable items purchased by key customers in the format of text file:

File name “Times.txt”:

Name     Times     Commodity

Peter      0     Egg

Mark      6     Egg

Alice       6     Egg

Lily  2     Egg

Peter      9     Tissue

Mark      9     Tissue

Alice       8     Tissue

Lily  8     Tissue

Peter      6     Bread

Mark      6     Bread

Alice       8     Bread

Lily  3     Bread

 

Now convert the data to Excel:

 

This is a very common transposition of data, and the script of the operation is as follows:

	A	B
1	=file("Times.txt").import@t()	/import the text file
2	=A1.pivot(Name;Commodity,Times;"Egg":"Egg","Tissue":"Tissue","Bread":"Bread")	/transpose
3	=file("Times.xlsx").xlsexport@t(A2)	/export the result to Excel

 

8 Transpose data to generate Excel (two columns at the cross spot)

A shopping mall complies the times and quantity of certain consumable items purchased by key customers in the format of text files:

 

File name “TimesQuantity.txt”:

Name     Times     Quantity Commodity

Peter      7     7     Egg

Mark      9     81   Egg

Alice       3     21   Egg

Lily  7     35   Egg

Peter      1     2     Tissue

Mark      3     24   Tissue

Alice       7     0     Tissue

Lily  2     12   Tissue

Peter      1     4     Bread

Mark      8     40   Bread

Alice       2     8     Bread

Lily  3     24   Bread

 

Import the data in Excel for the purpose of convenient calculation:

 

This is a common data transposition, and the script for the operation is:

	A	B
1	=file("TimesQuantity.txt").import@t()
2	=A1.pivot(Name;Commodity,[Times,Quantity];"Egg":"Egg","Tissue":"Tissue","Bread":"Bread")	/in a case of the two columns at cross spot, process them in the form of a sequence
3	=A2.new(Name,Egg(1):EggTimes,Egg(2):EggQuantity,Tissue(1):TissueTimes,Tissue(2):TissueQuantity,Bread(1):BreadTimes,Bread(2):BreadQuantity)	/perform new function on the data and read the members in the sequence as fields
4	=file("TimesQuantity.xlsx").xlsexport@t(A3)

 

9 Generate Excel from text file of complex format

During data analysis, some data are in text files of complex format, and the following points need to be considered thoroughly in order to retrieve useful data from them:

1) Verify the structure of data to be retrieved and which fields need to be retrieved.

2) Verify whether there are valid data in a line of the text file.

3) Find the rules of retrieving each field from the valid lines of data.

 

The rules may be different for different text data, but there must be one rule to figure it out.

 

There is a text format of custom quotation item list “item.txt” as follows:

 

The row before the horizontal line is a complex table header, and each subsequent row is a quotation record with blank lines between the records. The above figure shows only one table header and quotation record area, which repeatedly exist in the text file. The red boxes as shown are respectively Unit Price and Exp.Date field columns with Quotation Number, Customer Code, and Customer Name field columns in between, and each column of data is separated by spaces.

 

Now the quotation record data of the text file need to be retrieved and then stored in Excel as follows:

 

1. Observe and find the rules of the text file

The following rules can be found:

(1) There is no valid information in lines with less than 136 characters and such lines can be skipped.

(2) The required data are in the 59^th column to 136^th column of each row.

(3) Split the valid information of each line by taking spaces as the separators. If the first split value is a number, then this line is quotation record, otherwise, it can be skipped. The first split value is Unit Price column; the second one is Quotation Number column; the third one is Customer Code column; the last one is Contract Expiry Date column; the forth to the penultimate one is Custom Name column connected by spaces.

 

2. Write the script

	A	B	C
1	=create(Customer_Code,Customer_Name,Quotation_No,Unit_Price,Contract_Expiry_Date)
2	=file("E:/txt2Excel/item.txt").read@n()
3	for A2	if len(A3)<136	next
4		=right(left(A3,136),-58)	=B4.split@tp()
5		if !ifnumber(C4(1))	next
6		=C4.m(4:C4.len()-1).concat(" ")
7		>A1.insert(0,C4(3),B6,C4(2),C4(1),C4(C4.len()))
8	=file("E:/txt2Excel/item.xlsx").xlsexport@t(A1)

 

A1 create the target data set

A2 open the quotation item list file “item.txt”, read the file content, and @n option means to read one line as one string

A3 loop through each line, and execute the rules found previously

B3C3 skip the line if the length of the current line is less than 136

B4 retrieve the 59^th column to 136^th column of the current line

C4 split the data retrieved in B4 by spaces, option t means to remove the space at both ends after splitting, and option p means to parse the split strings into corresponding data types

B5C5 skip the line if the first value split in C4 is not a number

B6 concatenate the forth value to the penultimate value split in C4 as a string with spaces

B7 insert the third value split in C4, the second, first, and last values split in B6 & C4 to the new record of A1 accordingly

A8 stores all the retrieved data in the Excel “item.xlsx”

 

10 Generate Excel from xml

A shopping mall downloads a piece of customer information data from the system, which is in XML format as shown below:

 

Import the data to Excel:

 

This operation is a common data importing from XML and exporting to Excel, and the script is:

	A	B
1	=file("Customers.xml").read()	/read the whole text file in a big string
2	=xml(A1).xml.row	/xml() function parses the string to a sequence/table sequence, then access the members in the sequence, and read the core data table sequence
3	=file("Customers.xlsx").xlsexport@t(A2)	/export the result to Excel

 

11 Generate Excel from JSON

A shopping mall downloads a piece of custom information data from the system, which is in JSON format as shown below:

 

Import the data to Excel as follows:

 

This operation is a common data importing form json and exporting to Excel, and the script is:

	A	B
1	=file("Customers.json").read()	/read the whole text file to a big string
2	=json(A1)	/json() function parses the string to a table sequence
3	=file("Customers.xlsx").xlsexport@t(A2)	/export the result to Excel