題 如何阻止Excel吃掉我美味的CSV文件並排出無用的數據?


我有一個數據庫,可以按序列號跟踪小部件的銷售情況。用戶輸入購買者數據和數量,並將每個小部件掃描到自定義客戶端程序中。然後他們完成訂單。這一切都完美無瑕。

有些客戶需要與他們購買的小部件的Excel兼容電子表格。我們使用PHP腳本生成它,該腳本查詢數據庫並將結果輸出為帶有商店名稱和相關數據的CSV。這也非常有效。

在文本編輯器(如Notepad或vi)中打開時,文件如下所示:

"Account Number","Store Name","S1","S2","S3","Widget Type","Date"
"4173","SpeedyCorp","268435459705526269","","268435459705526269","848 Model Widget","2011-01-17"

如您所見,序列號存在(在這種情況下是兩次,並非所有二級序列都相同)並且是長串數字。 在Excel中打開此文件時,結果將變為:

Account Number  Store Name  S1  S2  S3  Widget Type Date 
4173    SpeedyCorp  2.68435E+17     2.68435E+17 848 Model Widget    2011-01-17

您可能已經觀察到,序列號用雙引號括起來。 Excel似乎不尊重.csv文件中的文本限定符。將這些文件導入Access時,我們沒有任何困難。當打開它們作為文本時,沒有任何麻煩。但Excel無疑會將這些文件轉換為無用的垃圾。我們說,試圖向最終用戶指導使用非默認應用程序打開CSV文件的過程令人厭煩。有希望嗎?有沒有我無法找到的設置?這似乎是Excel 2003,2007和2010的情況。


123
2018-01-19 01:08


起源


我可以給這個名字+1嗎? - tombull89
Excel does not seem to respect text qualifiers in .csv files  - 雙引號是 不 文本限定符,它們只是允許數據中的逗號,如果您不在數據中使用逗號,那麼它們就是 無意義的。 CSV文件中的所有數據都是無類型的,因此Excel只能猜測您的大型序列號是a 數那就是你跑進Excel的時候 最高精度 15位數,這是截斷你的數字。 - DMA57361
Excel似乎不尊重雙引號內的所有逗號。考慮“2012年8月12日”Excel也將其轉化為垃圾。 - zundarz
我想提一下這個 SU問題。它解釋了在Excel中處理CSV時您有哪些選項。 - nixda
@nixda謝謝!這些是有用的建議,特別是對於更有經驗的用戶。我的問題幾乎是一個人為問題,因為Excel將自己與.csv文件聯繫起來,人們看到圖標,然後雙擊(因為這就是你打開的方式),然後通常點擊Save(因為我們總是在說他們要保存!),一切都迷失了。但是我肯定會盡可能地使用你的方法。 - atroon


答案:


但Excel無疑會將這些文件轉換為無用的垃圾。

Excel是無用的垃圾。

如果任何客戶想要Excel格式的數據無法將這三列上的可見格式更改為帶小數位零的“數字”或“文本”,我會感到有些意外。但是我們假設一個簡短的操作方法文檔是不可能的。

你的選擇是:

  1. 將非數字字符,而不是空格字符放入序列號中。
  2. 使用一些默認格式寫出xls文件或xlsx文件。
  3. 作弊並輸出這些數字作為公式 ="268435459705526269","",="268435459705526269" (你也可以這樣做 ="268435459705526269",,="268435459705526269" 為自己保存2個字符)。這具有正確顯示的優點,並且可能通常是有用的,但是巧妙地破壞(因為它們是公式)。

注意選項3,因為某些程序(包括Excel和Open Office Calc)將不再處理逗號 ="" 字段為轉義。這意味著 ="abc,xyz" 將跨越兩列並打破導入。

使用的格式 "=""abc,xy""" 解決了這個問題,但由於Excel的公式長度限制,此方法仍然限制為255個字符。


57
2018-01-19 01:57



實際上,這並不嚴厲。將上述數字之一複制並粘貼到Excel中,然後按照建議更改數字格式。 Excel更改值,導致垃圾。 - Joe Internet
@Joe,我的初步概述太粗略了。 Excel確實在生產垃圾,而且本身就是垃圾。我已經更新了我的答案以反映這一點。一個選項可能是“Excel csv”,也有“可用,有價值的csv” - Tyler
@Tyler - 我不認為Excel是垃圾,只是說OP在這種情況下產生垃圾是正確的。這實際上是一個非常好的問題,沒有看似優雅的解決方案。 - Joe Internet
已建議使用Format Cells ...選項,我嘗試使用它。在這種情況下,當您打開文件時,Excel似乎將序列號轉換為科學記數法(同意,而不是意外)並拋出精度。當您將它們更改為數字或文本時,字符串不會返回。 那 真的是問題的本質。輸出公式可能會做到這一點......我沒有想到這一點。 - atroon
@ DMA57361行為不是預期的,它是可以確定的。數字精度有詳細記錄,如何讀取CSV不是。缺乏警告和默默地丟棄數據是荒謬的。您甚至無法告訴Excel如何導入數據這一事實同樣荒謬。是否定性 需要?不,但誠實是最好的政策,這就是我的感受。 - Tyler


我們遇到了類似的問題,我們的CSV文件包含3-5個範圍的列,Excel會將它們轉換為日期,例如3-5將是3月3日,之後切換回數字給我們一個無用的日期整數。我們繞過它了

  1. 將CSV重命名為TXT擴展名
  2. 然後,當我們在Excel中打開它時,這將啟動文本導入嚮導
  3. 在嚮導的第3步中 我們告訴它有問題的列是文本 他們進口得當。

你可以在這裡做同樣的事情。

text import wizard

乾杯


42
2018-01-31 18:30



+1是正確的方法。 (編輯:抱歉不得不編輯一點來澄清解決方案) - Jay
您不必重命名文件。只需使用導入嚮導Shift - 選擇所有列並選擇文本。 - nixda
文本導入嚮導是答案。由於不了解如何使用Excel查看和編輯CSV,所有其他解決方案都是不必要的hackery。 - Excellll
@Excellll,一次做一個文件。當自動化這個過程時,“不必要的hackery”通常會節省一天。 - Parrish Husband
當標準用戶使用excel顯示CSV文件時,這完全沒用。在我嘗試向15位初學者技能辦公室用戶解釋如何使用text-import-wizard之前,我寧願自己輸入excel文檔源代碼。 - northkildonan


更好的解決方案是生成XML Workbook。像這樣:

<?xml version="1.0" encoding="UTF-8"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  </OfficeDocumentSettings>

  <ss:Worksheet ss:Name="Sheet 1">
    <Table>
    <Column ss:Width="100"/>
    <Column ss:Width="100"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="150"/>
    <Column ss:Width="80"/>
    <Column/>

    <Row>
      <Cell><Data ss:Type="String">Account Number</Data></Cell>
      <Cell><Data ss:Type="String">Store Name</Data></Cell>
      <Cell><Data ss:Type="String">S1</Data></Cell>
      <Cell><Data ss:Type="String">S2</Data></Cell>
      <Cell><Data ss:Type="String">S3</Data></Cell>
      <Cell><Data ss:Type="String">Widget Type</Data></Cell>
      <Cell><Data ss:Type="String">Date</Data></Cell>
    </Row>

    <Row>
      <Cell><Data ss:Type="String">4173</Data></Cell>
      <Cell><Data ss:Type="String">SpeedyCorp</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">x</Data></Cell>
      <Cell><Data ss:Type="String">268435459705526269</Data></Cell>
      <Cell><Data ss:Type="String">848 Model Widget</Data></Cell>
      <Cell><Data ss:Type="String">2011-01-17</Data></Cell>
    </Row>


    </Table>
    <x:WorksheetOptions/>
  </ss:Worksheet>
</Workbook>

該文件必須具有.xml擴展名。 Excel和OpenOffice正確打開它。


9
2018-02-17 11:13



你的意思是OP應該使用PHP腳本將數據庫轉換為XML格式嗎? - Prasanna
比讓用戶在Excel中打開.csv或弄亂你的CSV以便只有Excel可以理解你的CSV要清晰得多。它甚至不是架構的複雜。 - binki
該標准在哪裡記錄?我想了解更多有關可用數據類型的信息。 - John Doherty


我的解決方案 導入序列號時遇到了同樣的問題。 它們不必被視為數字,即不對其執行數學函數,但我們需要整數。 我最簡單的方法是在序列號中插入一個空格。 例如“12345678 90123456 1234”。 當Excel導入它時,它將被視為文本而不是數字。


1
2018-05-08 08:08





我的帳號長了亂碼。

這就是我修復它的方法:

在Libre Office / Open Office中打開file.csv(您可能必須指定分隔符等),然後將文件另存為Excel XML文件。

然後在Excel中打開此文件,然後您將看到列不再更改為科學格式或其他任何內容。為安全起見,右鍵單擊該列並將格式顯式設置為Text,然後另存為Excel文件格式。

打開Excel格式文件,列仍然可以!


0
2018-05-17 14:53



雖然這個 將 工作,試圖向只會說英語的人解釋為什麼他需要使用不同的辦公套件會產生同樣多的問題。 M $ Office的替代軟件在我看來都很好,但我意識到我無法轉換每個人。 - atroon


導入嚮導是休閒用戶和一次性情況的最佳解決方案。如果需要編程解決方案,可以使用QueryTables.Add方法(這是導入嚮導在幕後使用的方法)。

Workbooks.Add
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & "C:\myfile.csv", Destination:=Range("$A$1"))
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 2, 2) 'Edit this line. Add a number for each column, 1 is general, 2 is text. Search the internet for other formats.
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

0
2017-07-23 19:48