10行代碼提取復(fù)雜Excel數(shù)據(jù)

把 Excel 文件導(dǎo)入關(guān)系數(shù)據(jù)庫是數(shù)據(jù)分析業(yè)務(wù)中經(jīng)常要做的事情，但許多 Excel 文件的格式并不規(guī)整，需要事先將其中的數(shù)據(jù)結(jié)構(gòu)化后再用 SQL 語句寫入數(shù)據(jù)庫。而一般情況下，結(jié)構(gòu)化的工作量會比較大，而且很難通用，每次都要針對文件格式進(jìn)行分析后再進(jìn)行開發(fā)。
集算器的 SPL 語言是一款高效、靈活的工具，它能夠輕松讀取 excel 數(shù)據(jù)，然后結(jié)構(gòu)化成“序表”后導(dǎo)入數(shù)據(jù)庫。使用 SPL 語言后，以往需要編寫數(shù)千行代碼才能完成的 Excel 數(shù)據(jù)結(jié)構(gòu)化入庫工作，現(xiàn)在只需要不到 10 行代碼就可以勝任，簡單情況下甚至只需要 2、3 行代碼！真的這么神奇嗎？下面就來看看神奇之處吧

成都創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于做網(wǎng)站、網(wǎng)站設(shè)計、那曲網(wǎng)絡(luò)推廣、重慶小程序開發(fā)公司、那曲網(wǎng)絡(luò)營銷、那曲企業(yè)策劃、那曲品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；成都創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供那曲建站搭建服務(wù)，24小時服務(wù)熱線：028-86922220，官方網(wǎng)址：bm7419.com

10行代碼提取復(fù)雜Excel數(shù)據(jù)

下面我們將分情況討論如何利用集算器將Excel數(shù)據(jù)進(jìn)行結(jié)構(gòu)化。文中用到的函數(shù)請參看集算器文檔《函數(shù)參考》。

1.   普通行式

先看最簡單的情況：如下圖所示，Excel文件中第一行是列標(biāo)題，從第二行開始，每行是一條數(shù)據(jù)記錄。

集算器處理這種文件也非常簡單：

	A
1	=file( "學(xué)生成績表.xlsx"   ).xlsimport@t()
2	=connect("demo")
3	=A2.update(A1,xscj)

A1  打開“學(xué)生成績表.xlsx”文件并導(dǎo)入成序表，選項@t表示文件第一行是列標(biāo)題

A2  連接demo數(shù)據(jù)庫

A3  將A1中的序表存入到demo數(shù)據(jù)庫的xscj表中，由于表中的列名和序表中的字段名一樣，所以只需指定數(shù)據(jù)表名即可。update函數(shù)的更詳細(xì)用法請查閱函數(shù)文檔。

A1中得到的序表如下圖所示：

A2、A3兩步連接數(shù)據(jù)庫和將序表存入數(shù)據(jù)庫的方法是通用的，所以后面的例子中將不再寫這兩步，只關(guān)注于如何把Excel中的數(shù)據(jù)結(jié)構(gòu)化成序表。

 

2.  多行表頭行式

大多數(shù)時候，Excel文件都不會象上例那么簡單，表頭往往比較復(fù)雜，有表名、項目名、頁碼、填表人、填寫日期等等。比如這個樣子：

對于這種表，我們在讀取時就要跳過表頭，直接從數(shù)據(jù)行開始讀。

	A
1	=file( "措施項目清單與計價表.xlsx"   ).xlsimport(;1,5)
2	=A1.rename(#1:序號,#2: 項目編碼,#3: 項目名稱,#4: 計量單位,#5: 數(shù)量,#6: 單價,#7: 合價 )

A1   打開文件并導(dǎo)入數(shù)據(jù)成序表，參數(shù)“1,5”表示讀第一個 sheet，從第 5 行開始讀，一直讀到文件結(jié)尾

A2   將 A1 中讀到的序表列名依次改為“序號、項目編碼、項目名稱、計量單位、數(shù)量、單價、合價”，即要存入的數(shù)據(jù)表的列名。

運行后 A2 中的序表如下：

 

3.  自由格式

有時Excel文件的數(shù)據(jù)并不是網(wǎng)格式的規(guī)則表，而是字段名后緊跟著字段值的自由格式，如下圖的雇員信息表：

每個雇員信息占據(jù)9行，依次往下排列。對這種文件，該怎么結(jié)構(gòu)化呢？請看：

	A	B	C
1	=create(雇員 ID, 姓名, 性別, 職位, 生日, 電話, 地址, 郵編 )
2	=file("雇員信息表.xlsx").xlsopen()
3	[C,C,F,C,C,D,C,C]	[1,2,2,3,4,5,7,8]
4	for	=A3.(~/B3(#)).(eval($[A2.xlscell(]/~/")"))
5		if len(B4(1))==0	break
6		>A1.record(B4)
7		>B3=B3.(~+9)

A1   創(chuàng)建列名為“雇員 ID, 姓名, 性別, 職位, 生日, 電話, 地址, 郵編”的空序表

A2   打開 Excel 數(shù)據(jù)文件

A3   定義雇員信息所在單元格列號序列

B3   定義雇員信息所在單元格行號序列

A4   用 for 循環(huán)讀取每個雇員信息

B4   A3.(~/B3(#))先算出當(dāng)前雇員單元格編號序列, 再讀出這些單元格值組成雇員信息序列。第一次循環(huán)時為 [C1,C2,F2,C3,C4,D5,C7,C8]，第二次循環(huán)時為[C10,C11,F11,C12,C13,D14,C16,C17]……每次行號加 9。$[A2.xlscell(] 與 "A2.xlscell(" 相同，都是表示一個字符串，它的好處是在 IDE 中編寫程序時，如果 A2 單元格的編號發(fā)生了變化，$[A2.xlscell(]中的 A2 會自動變化，比如在 A2 前插入了一行，這個表達(dá)式就會變成 $[A3.xlscell(]，而用引號的話，就不會自動變了。

B5   判斷雇員 ID 值是否為空，為空則退出循環(huán)，結(jié)束運行

B6   將一條雇員信息存入 A1 序表尾

B7   讓雇員信息的行號序列都加上 9，讀取下一條雇員信息

運行后得到的 A1 序表如下：

 

4.  交叉表

Excel中還有交叉表格式的數(shù)據(jù)，如下圖：

數(shù)據(jù)結(jié)構(gòu)化程序如下：

	A
1	=file("交叉表.xlsx").xlsimport@t(;1,2)
2	=A1.rename(#1:運貨商)
3	=A2.pivot@r(運貨商;貨主地區(qū),訂單數(shù)量)

A1   打開文件并導(dǎo)入數(shù)據(jù)成序表，參數(shù)“1,2”表示讀第一個 sheet，從第 2 行開始讀，一直讀到文件結(jié)尾。選項 @t 表示開始行是列標(biāo)題。

A2   由于第二行第一個單元格是圖片，讀的數(shù)據(jù)為 null，第一列沒有列標(biāo)題，所以將第一列列名改為運貨商。

A3   以運貨商為分組，對序表數(shù)據(jù)進(jìn)行行列轉(zhuǎn)換，選項 @r 表示將列數(shù)據(jù)轉(zhuǎn)換為行數(shù)據(jù)，轉(zhuǎn)換后新的列名分別為“貨主地區(qū)”、“訂單數(shù)量”。

運行后得到的 A3 序表如下：

 

5.  主子表

在下圖所示的員工信息登記表中，除了有員工本人的信息外，還有他的家庭成員信息。每個 sheet 保存一個員工的相關(guān)信息，所以有多少員工，就有多少個 sheet。

對這種主子表結(jié)構(gòu)的數(shù)據(jù)，需要創(chuàng)建兩個序表分別保存主表和子表的數(shù)據(jù)，集算器程序如下：

	A	B	C
1	=create(×××號,姓名,性別,出生日期,民族,手機(jī)號,部門,家庭地址,婚姻狀況,入職時間)
2	=create(×××號,姓名,關(guān)系,工作單位,聯(lián)系電話)
3	[B4,B3,D3,F3,H3,F4,H4,B5,F5,H5]
4	=file("員工信息表.xlsx").xlsopen()
5	for A4
6		=A3.(eval($[A4.xlscell(]/~/",\""/A5.頁名/"\")"))	>A1.record(B6)
7		=A4.xlsimport@t(家庭成員,姓名,關(guān)系,工作單位,聯(lián)系電話;A5.頁名,6)
8		=B7.rename(家庭成員:×××號)	>B8.run(×××號=B6(1))
9		>A2.insert@r(0:B8)

A1   創(chuàng)建列名為“×××號, 姓名, 性別, 出生日期, 民族, 手機(jī)號, 部門, 家庭地址, 婚姻狀況, 入職時間”的空序表，用于保存主表員工信息

A2   創(chuàng)建列名為“×××號, 姓名, 關(guān)系, 工作單位, 聯(lián)系電話”的空序表，用于保存子表員工家庭成員信息

A3   定義主表員工信息所在單元格序列

A4   打開 Excel 數(shù)據(jù)文件

A5   循環(huán)讀取 Excel 文件各 sheet 數(shù)據(jù)

B6   讀取員工信息序列

C6   將 B6 讀取的員工信息保存到序表 A1

B7   從第 6 行開始讀取員工家庭成員信息，只讀指定的“家庭成員, 姓名, 關(guān)系, 工作單位, 聯(lián)系電話”5 列

B8   將 B7 序表的家庭成員列改名為×××號

C8   為 B8 序表的×××號列賦值為員工信息中的×××號

B9   將 B8 中的員工家庭成員信息保存到序表 A2

程序運行后，序表 A1 如下圖所示：

序表 A2 如下圖所示：

上面這些情況基本羅列了常見的 Excel 數(shù)據(jù)格式，如果遇到更復(fù)雜的文件，也可以靈活使用例子中的技巧予以應(yīng)對。簡單總結(jié)一下，集算器提供了非常靈活的在 excel 文件中定位和讀取數(shù)據(jù)的功能，既可以成片讀取網(wǎng)格數(shù)據(jù)，也可以精確定位單元格進(jìn)行讀取。再結(jié)合特有的“序表”對象，以往需要編寫數(shù)千行代碼才能完成的 Excel 數(shù)據(jù)結(jié)構(gòu)化入庫工作，現(xiàn)在只需要不到 10 行代碼就可以勝任，簡單情況下甚至只需要 2、3 行代碼！

網(wǎng)頁標(biāo)題：10行代碼提取復(fù)雜Excel數(shù)據(jù)
文章位置：http://bm7419.com/article44/ijppee.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計、定制網(wǎng)站、關(guān)鍵詞優(yōu)化、響應(yīng)式網(wǎng)站、服務(wù)器托管、網(wǎng)站營銷

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)