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

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

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

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

1.   普通行式

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

集算器處理這種文件也非常簡(jiǎn)單：

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

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

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

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

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

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

 

2.  多行表頭行式

大多數(shù)時(shí)候，Excel文件都不會(huì)象上例那么簡(jiǎn)單，表頭往往比較復(fù)雜，有表名、項(xiàng)目名、頁(yè)碼、填表人、填寫(xiě)日期等等。比如這個(gè)樣子：

對(duì)于這種表，我們?cè)谧x取時(shí)就要跳過(guò)表頭，直接從數(shù)據(jù)行開(kāi)始讀。

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

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

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

運(yùn)行后 A2 中的序表如下：

 

3.  自由格式

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

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

	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   打開(kāi) Excel 數(shù)據(jù)文件

A3   定義雇員信息所在單元格列號(hào)序列

B3   定義雇員信息所在單元格行號(hào)序列

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

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

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

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

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

運(yùn)行后得到的 A1 序表如下：

 

4.  交叉表

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

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

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

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

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

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

運(yùn)行后得到的 A3 序表如下：

 

5.  主子表

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

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

	A	B	C
1	=create(×××號(hào),姓名,性別,出生日期,民族,手機(jī)號(hào),部門(mén),家庭地址,婚姻狀況,入職時(shí)間)
2	=create(×××號(hào),姓名,關(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.頁(yè)名/"\")"))	>A1.record(B6)
7		=A4.xlsimport@t(家庭成員,姓名,關(guān)系,工作單位,聯(lián)系電話;A5.頁(yè)名,6)
8		=B7.rename(家庭成員:×××號(hào))	>B8.run(×××號(hào)=B6(1))
9		>A2.insert@r(0:B8)

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

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

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

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

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

B6   讀取員工信息序列

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

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

B8   將 B7 序表的家庭成員列改名為×××號(hào)

C8   為 B8 序表的×××號(hào)列賦值為員工信息中的×××號(hào)

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

程序運(yùn)行后，序表 A1 如下圖所示：

序表 A2 如下圖所示：

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

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

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

廣告

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