美味しいCSVファイルをExcelが食べて無駄なデータを排泄するのを防ぐにはどうすればいいのでしょうか？

私は、シリアル番号でウィジェットの売上を追跡するデータベースを持っています。ユーザーは購入者データと数量を入力し、カスタムクライアントプログラムに各ウィジェットをスキャンします。そして、注文を確定します。これはすべて完璧に機能しています

購入したウィジェットのエクセル対応のスプレッドシートが欲しいというお客様がいらっしゃいます。当社では、データベースに問い合わせを行い、その結果を店舗名と関連データを含むCSVとして出力するPHPスクリプトを使用してこれを生成しています。これも完璧に機能します

メモ帳やviなどのテキストエディタで開くと、このようなファイルになります

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

ご覧のように、シリアル番号が存在し（この場合は2回、2次シリアルがすべて同じではありません）、長い数字の文字列になっています。このファイルをエクセルで開くと、結果は次のようになります

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ファイルを開く方法をエンドユーザーに教えるのは、言うなれば面倒くさくなってきました。希望はありますか？私が見つけられなかった設定があるのでしょうか？エクセル2003、2007、2010ではそうなっているようです

　 130　 atroon　 2011-01-19

ベストアンサー

しかし、エクセルは必ずこれらのファイルを無駄なゴミに変換してしまいます

エクセルは使い物にならないゴミ

Solution

あなたのデータをエクセル形式にしたいと思っているクライアントが、これらの3つの列に表示されている書式を、小数点以下の桁数が0の “数字 “か “テキスト “に変更することができなかったとしたら、私は少し驚きます。しかし、短いハウツー文書は問題外だと仮定してみましょう

あなたの選択肢は

シリアル番号の中に、空白ではなく数字以外の文字を入れてください
xlsファイルまたはxlsxファイルを、いくつかのデフォルトフォーマットで書き出します
カンニングして、これらの数値を数式 ="268435459705526269","",="268435459705526269" として出力します (2文字の節約になる ="268435459705526269",,="268435459705526269" もできます)。これは正しく表示されるという利点があり、おそらく一般的には便利ですが、微妙に壊れています (数式なので)

オプション3には注意してください。いくつかのプログラム(Excel & Open Office Calcを含む)は、=""フィールド内のカンマをエスケープされたものとして扱わなくなるので、注意してください。つまり、="abc,xyz"は2つの列にまたがってしまい、インポートが中断されてしまいます

"=""abc,xy"""のフォーマットを使用することでこの問題を解決できますが、Excelの数式の長さ制限のため、この方法では255文字に制限されます

58　 Tyler　 2011-01-19

3-5のような範囲を含む列を持つCSVファイルを持っていたのですが、Excelは常にそれらを日付に変換してしまい、例えば3-5は3月3日になります。この問題は以下の方法で解決しました

CSVをTXT拡張子に名前を変更します
そして、それをExcelで開くと、テキストインポートウィザードが起動します
ウィザードのステップ3では、問題の列がテキストであることを伝え、適切にインポートされました

ここでも同じことができると思います

text import wizard

Cheers

44　 user65525　 2011-01-31

より良い解決策は、XMLワークブックを生成することです。こんな感じで

<?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　 twentybeersreserved　 2015-02-17

私の解決策シリアル番号のインポートで同じ問題が発生しました。彼らは数字として扱われる必要はありません、すなわち、数学的な関数はそれに実行されませんが、我々はそこに番号全体を必要とします。一番簡単なのは、シリアル番号にスペースを挿入することです。これをExcelでインポートすると、数字ではなくテキストとして扱われます

1　 Peterlip　 2012-05-08

インポートウィザードは、カジュアルなユーザーや単発的な状況に最適なソリューションです。プログラム的なソリューションが必要な場合は、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

1　 PBeezy　 2018-07-23

長い口座番号が文字化けしていました

こんな感じで修正しました

Libre Office/Open Officeでファイル.csvを開き（区切り文字などを指定する必要があるかもしれません）、Excel XMLファイルとして保存します

その後、このファイルをExcelで開くと、列が科学的な形式に変更されていないことがわかります。安全のためには、列を右クリックして明示的にフォーマットをテキストに設定し、Excelファイルのフォーマットで保存します

Excel形式のファイルを開くと、列はまだ大丈夫なはずです!

0　 user127379　 2013-05-17