假設(shè)我們當(dāng)前需求為：將一個(gè)xlsx格式的excel文件轉(zhuǎn)換為以|作為分隔符的txt文本文件。
我們通常的方法有：
- 將整個(gè)excel復(fù)制出來(lái)，替換
\t為|符號(hào)。但這種方法要求你的文本處理工具需要有正則功能，例如ue、notepad++等。而且像ue這種工具你可能還需要通過(guò)ctrl+h轉(zhuǎn)換到16進(jìn)制，替換16進(jìn)制的\t符號(hào)(即09)為16進(jìn)制的|符號(hào)(即7C)。
09、7C是table鍵(\t)、|符號(hào)的16進(jìn)制ascii值，可以在以下網(wǎng)址找到對(duì)應(yīng)數(shù)值:http://ascii.911cha.com
在excel中，通過(guò)
concat公式或&符號(hào)，手動(dòng)拼接字符串，然后粘貼到txt文件中。與方法1類似，在excel中先通過(guò)另存為csv的方式導(dǎo)出以逗號(hào)作為分隔符的文件，然后再替換逗號(hào)為|符號(hào)。
上述方法，要么是需要手動(dòng)拼接，要么是需要手動(dòng)做分隔的替換工作。當(dāng)記錄數(shù)量較少或格式?jīng)]那么復(fù)雜的情況下問(wèn)題不大，但是當(dāng)記錄數(shù)達(dá)到幾十萬(wàn)上百萬(wàn)以后，替換操作會(huì)非常緩慢，并且如果文件中出現(xiàn)超長(zhǎng)度的數(shù)值或是日期等特殊數(shù)據(jù)格式時(shí)，可能導(dǎo)出的數(shù)據(jù)還會(huì)出現(xiàn)各種各樣的問(wèn)題。
因此我們可以使用以下的方式進(jìn)行處理。
通用處理方式
首先，我們的處理方式總結(jié)起來(lái)，就是修改系統(tǒng)中的分隔符配置，這樣在excel中導(dǎo)出csv就是我們想要的分隔符了，之后在修改為txt后綴就完成了處理。
假設(shè)我們有以下數(shù)據(jù)：
將其另存為csv文件：
這時(shí)以文本方式打開(kāi)csv文件，會(huì)發(fā)現(xiàn)實(shí)際上這是一個(gè)以逗號(hào)為分隔符的普通文本文件：
打開(kāi)控制面板，點(diǎn)擊更改日期、時(shí)間或數(shù)字格式：
點(diǎn)擊其他設(shè)置：
在打開(kāi)的窗口中，將列表分隔符由原來(lái)的,修改為|，點(diǎn)擊確定/應(yīng)用：
這時(shí)回到excel中，同樣的我們使用另存為csv的方式進(jìn)行處理：
再次以文本文件的方式打開(kāi)csv文件，就會(huì)發(fā)現(xiàn)分隔符已經(jīng)變成了| ：
特殊處理
末尾需要增加一個(gè)分隔符
有時(shí)候需要我們?cè)谀┪蔡砑右粋€(gè)分隔符，例如a|b|c需要我們輸出為a|b|c|，這時(shí)我們只需要在excel文件的最后填充一列內(nèi)容為="",即填充一列空白字符:
這時(shí)再重復(fù)導(dǎo)出csv的步驟，導(dǎo)出的文件中就會(huì)多出一列|:
內(nèi)容中存在超長(zhǎng)數(shù)字或日期等特殊格式
超長(zhǎng)數(shù)字或日期格式可能會(huì)導(dǎo)致的問(wèn)題
- excel中，11位及11位以下的數(shù)字將會(huì)以普通數(shù)字的方式進(jìn)行展示和輸出，而超過(guò)11位的數(shù)字將會(huì)以
1.2345E+11這樣的科學(xué)計(jì)數(shù)法表示。如果此時(shí)我們按以上的方式導(dǎo)出為csv，它的內(nèi)容將同樣被修改為1.2345E+11，這是我們不希望看到的。 - 日期格式在excel中，是可以用普通日期格式和數(shù)字格式兩種方式展示的。如果某一列我們使用的是日期格式，此時(shí)我們?nèi)绻麑?duì)其進(jìn)行處理，例如將其復(fù)制到了另一列，或是使用
trim、clean等公式進(jìn)行去除空格、去除不可見(jiàn)符號(hào)等操作時(shí)，excel會(huì)將其轉(zhuǎn)換為默認(rèn)的數(shù)字格式(因?yàn)閿?shù)字格式較為通用，而且也方便轉(zhuǎn)換為其他格式，但是它可能會(huì)帶來(lái)精度丟失)。此時(shí)如果我們直接導(dǎo)出為csv，看到的將會(huì)是日期的數(shù)字表示形式而不是我們想要的yyyy/mm/dd形式。
12.jpg
處理方式
數(shù)字格式的處理
對(duì)于數(shù)字格式，選中該列，點(diǎn)擊數(shù)據(jù)，在選擇分列，一路點(diǎn)擊下一步，到步驟3列數(shù)據(jù)格式選擇，選擇為文本，并點(diǎn)擊完成：
此時(shí)我們會(huì)發(fā)現(xiàn)之前以科學(xué)記數(shù)法表示的超長(zhǎng)數(shù)字此時(shí)是以文本數(shù)字的方式展示的(特點(diǎn)是左上角有一個(gè)綠色的小三角)：
日期格式的處理
選中日期列，通過(guò)分列的方式將其修改為文本格式:
此時(shí)原來(lái)日期那一列可能沒(méi)什么特別的表現(xiàn)，但是公式中引用日期列的部分，將會(huì)以普通文本的方式進(jìn)行展示，而不再使用數(shù)字的方式展示日期：
導(dǎo)出結(jié)果
此時(shí)我們使用跟之前一樣的方式，導(dǎo)出為csv，并以文本方式打開(kāi)csv文件，會(huì)發(fā)現(xiàn)數(shù)字部分是以普通數(shù)字的方式輸出的，日期部分也是以普通日期格式進(jìn)行輸出的：
收尾工作
導(dǎo)出為csv后，將其后綴修改為txt就可以直接拿來(lái)使用了。
此外，由于不確定是否有其他文件會(huì)用到這個(gè)系統(tǒng)分隔符，所以為避免出現(xiàn)問(wèn)題，再處理完以后建議將分隔符還原為默認(rèn)的,：
