CSV檔是很普遍的Grid式資料匯出格式,而很多人也都知道Excel可以直接將CSV檔開啟成試算表。不過稱作Comma Separated Value的CSV,除了"用逗號將值隔開"之外,還有一些進階的花式技巧。例如:

1. 如果是內含逗號的字串值,可以用雙引號將字串包起來,例如: 886,"Taipei, Taiwan"

2. 如果在雙引號中的字串中又包含了雙引號,則可以遵循VB/VBScript的慣例,用兩個雙引號代替。例如: "12'30"""-->12'30"

3. 用Excel開啟CSV時,Excel會自動判斷值的性質,進行資料轉換。比較煩的是,明明編號是007,就算用了雙引號包夾成"007",Excel還是會將它轉換成7,即使在Excel中重新調整它的儲存格式為字串,也無法還原回007。

這裡分享一個小技巧,我們可以用="007"的宣告方式,強制指定字串值,Excel就不會雞婆將它轉成數字。而這個技巧還可以再延伸,甚至可以用=SUM(B2:B5)的寫法,指定儲存格的運算式。範例如下:

[Updated 2007/02/01]
在公司分享這篇KB後,同事Daniel也提供了他的寶貴經驗: 看來Excel只接受ANSI/BIG5編碼的CSV檔,如果將CSV檔存成Unicode編碼,Excel就無法識別出逗號,而會將整列資料視為同一個值。
謝謝Daniel的分享!

[Updated 2009/09/05]
補充關於CSV中文編碼的進一步研究


Comments

# by steve

恭喜喬遷 ="007"這個很實用 我試過加註解的方式 '007 結果出來的資料也是'007......囧rz 感謝有為的中年人大師

# by steve

也謝謝大牛 又派上用場了 XD

# by 靜靜地

真的非常感謝.剛好用得上

# by 小君

請問怎麼看CSV檔是哪種編碼阿?

# by Jeffrey

to 小君,要省事的話,可以考量用現成的文書編輯軟體識別,參考: http://blog.darkthread.net/blogs/darkthreadtw/archive/2006/07/18/encoding.aspx

# by vivi

thnx it's really help!

# by guest

不好意思 因為剛好處理CSV的時候遇到問題了~ 找解答的時候找到你的網頁。 想請問一下如果又要有逗號又要當成自串處理 那該怎麼做呢? 像是要存 01,234.00 這樣的一個字串 結果不是前面的0不見( "01,234.00") 就是在逗號會被切開:(="01,234.00") 不知道這樣有沒有方法解決

# by 路過的路人

不好意思 因為剛好處理CSV的時候遇到問題了~ 找解答的時候找到你的網頁。 想請問一下如果又要有逗號又要當成自串處理 那該怎麼做呢? 像是要存 01,234.00 這樣的一個字串 結果不是前面的0不見( "01,234.00") 就是在逗號會被切開:(="01,234.00") 不知道這樣有沒有方法解決

# by Jeffrey

to 路過的路人, 好問題,我覺得這超出Excel原始設計的預期,看來是無解的。我在stackoverflow.com替你發問,全球高手一超動動腦,看來結論是無解... http://stackoverflow.com/questions/308324/csv-for-excel-including-both-leading-zeros-and-commas 最接近的答案是"""01,234.00""",但必須忍受前後多了一個雙引號。

# by 邪教

請問可以用char()嗎 ="01"&CHAR(44)&"234.00"

# by Jeffrey

to 邪教,漂亮!! 在Excel 2003/2007上測試過,很管用!! 感謝分享~~~

# by Ark

我本身是開 OWC11 然後資料採用jQuery ajaj(webservice + json)拉後台的SQL數據 遇到那種 00X 0Y開頭的 fill OWC11 一率"'" + 數據 ==>就強制轉成字串了 , 要運算~自己剪去旁邊算

# by Ark

window.onload = function() { $.blockUI({ message: '<div><img src="/js/snake_transparent.gif" /> Loading...... !<br /></div>' }); PageMethods.rtrt(doso); } function doso(obj) { var myRS = obj.rsxmls[0]; var o = document.createElement('object'); with (o) { id = "spreadsheet"; width = "80%"; height = "80%"; classid = "CLSID:0002E559-0000-0000-C000-000000000046"; uiMode = "none"; XMLData = obj.rexcelorhtm; var StartCell = obj.rStartCell; var myexcel = ActiveSheet.Range(StartCell); try { $.each(myRS.RS, function(i, j) { $.each(j, function(k, l) { if (l) { myexcel.Cells(i + 2, k + 1).Value = "'" + l; } }); }); ActiveSheet.Columns.AutoFit(); style.display = "" } catch (e) { } } $get('divID').appendChild(o); var tnow = new Date(); $.unblockUI(); } 以上~AJAJ(Json) 偽 WEBSERVICE + jQuery的一些棉角 數據前面 +"'" 強制轉字串~要算自己減去旁邊算

# by Joe

感謝您的分享 剛好遇到相同問題 因此解決了資料匯出Excel的問題 不過要把資料匯回資料庫又要花一番功夫了 呵呵呵

# by 小狐狸

謝謝大大的分享, 之前也被cvs的問題困擾了好久 原來excel只讀ANSI/BIG5的編碼 害我之前用UTF-8一直出現亂碼 可以借引用在我的blog嗎?

# by Jeffrey

to 小狐狸,歡迎引用,請明確註明出處及連結即可。

# by Vincent

你好, 如果資料為 16進位, 例如 42E5, 結果讀入後被視為科學記號, 變成 420000, 怎麼辦??

# by Jeffrey

to Vicent, 用="42E5"指定為字串,是否符合你的需求?

# by 小馬

請問黑哥,我目前就是遇到項目 3 的問題,雖然可以將之調整為 ="007" 的方式,讓 Excel 開啟時開到完全體;只是萬一資料非常的多,一個個改似乎不符合經濟效益,是否有其他方法?

# by Jeffrey

to 小馬,"一個一個改不符合經濟效益"是指手工進行嗎? 若是如此,我傾向用程式以Regular Expression方式掃瞄替換自動修改。

# by Heaven Chou

提供一個技巧, 如果是儲存成 UTF16 的編碼,並且以 TAB 做為分隔,則 excel 就能接受了。

# by Jeffrey

to Heaven Chou, 實測過,UTF16+Tab真的可行(NotePad++的話要存成UCS-2 Litten Endian),這招好用,感謝分享!

Post a comment