Q&A
取り消し
すみません、プログラムのご相談ではないのですが....
システムのマスタデータをユーザから受けまして、Oracleデータベースのあるテーブルへ インポート（SQLLoader）を行おうと思っています。
（SQLServerであれば バルクインサートを使うところでしたが）
受け取ったデータは .xlsxなので 自分で 標題のとおり CSVもしくは タブ区切りテキストへ 変換した上 利用しようと思いました。
【相談内容】
xlsxから CSVもしくは タブ区切りテキストへ 変換したら、明らかに 妙なところで 改行を起こしてしまっている行が 多数発生しています。
全部ではありません。
取引先名称の後ろに 何か目には見えない 余計なコードが含まれていることを疑っています。これを除去したいです。
Excel側で 対応すべきか エディタで 対応すべきか
また、対応するにしても どういう手立てを とれば 良いのかが分からないです。
（問題を起こしている各行について、要因の列はすべて同じといえるが 全て同じ文字コード起因で改行が起こされているのか窺い知れない）
サクラエディタの置換で、オプションに 正規表現選択をOnにした上で 「\r\n」 を 「」に変換してしまうと
全行が 術つなぎになってしまいました。
不用意な改行をとり除きたいの一言ですが、どういった手順をとるのがベストでしょうか、ご教示を頂けないでしょうか？
回答5件
0
この手順ではどうでしょうか。(末尾の列がXとして)
1.Excelの末尾の列値へ特殊文字を付加。(例えばX列へ◆◆を付ける)
2.csvファイルへ書き出し。
3.サクラエディタで改行文字をなしへ変換。(1行にする)
4.◆◆を改行文字へ変換して戻す。
投稿2020/07/09 06:49
編集2020/07/09 06:49総合スコア553
確かに 臨時的なら その措置でよいですねぇ
データがデータベースに入らず、後工程の開発まで 途絶えていますから 一先ずその方式で正規CSVかタブ区切りのテキストファイルを 作ることとします。
恒久的な策でないものの 大変参考になりました。
0
取引先名称の後ろに 何か目には見えない 余計なコードが含まれていることを疑っています。これを除去したいです。
テキストエディタで開いて改行されて見えるのはCrかLfの何れかです。
サクラエディタならそれぞれ(Lf：←、Cr：↓、Crlf：⏎）で表示されているはずです。
この改行されるものは、一つの項目内で改行が使用されているのではないですか？
これを除去してしまうと、ユーザーの意図通りのデータでは無くなってしまいます。
これらのデータをテキストエディタで参照すると、行の途中で改行されて見えますが、データとしては正しいものです。
但し、このデータを取込む際には行の終端である改行なのか項目内の改行なのかを識別させる必要があり、通常は文字列引用符(")を指定します。
残念ながら、excelでは指定が出来ませんので、CSVでの受領に切り替えるか、Accessなどを経由して、CSVを出力する。
または、対応できるCSVエディターがあるかもしれないので探してみるのもいいかもしれません。
大量なデータでなければ、A5:SQLというDBツールではエクセルデータをそのまま貼り付けできたりします。
投稿2020/07/09 04:09
編集2020/07/09 04:51
総合スコア25195
ご見解ありがとうございます。
CSVでの受領に切り替える、という手立てですが
今回ブックで提供してきた人間は 別システムからデータをエキスポートしただけのことでしょうから、CSVでの提供を強いると 私同様 そちらに同じ問題が発生しそうです。
ということで、こちらでの課題解決が必須なのですが 以外に 悩ましい課題なのですね～。
Excelでだけ 無事に形成されているから 拡張子を変えることなく 行・列を意識したデータ取り込みを対応するほか...ない ということなのですかね～
VB.NETのファイルダイアログで、CSVかタブ区切りテキストを開く想定でいましたが ブックのファイルを展開することに挑戦してみるかなぁ
問題は、エクセルでCSV保存した時に文字列引用符が付けられない事だと思います。
こんなのを利用するのも手ですね。
【超簡単】ワンタッチでエクセルからCSV出力するVBAプログラム
https://www.excelspeedup.com/csvsyuturyoku/
個人的にExcelからCSVに変換するのは、もっぱらAccessに取り込んでからCSV出力していますね。
プログラム無しで行えますし、定型業務ならマクロ対応もできますから。
Accessなしでやるなら。Netまで敷居は高くしたくないのでVBSかな。
そもそもエクセルから、直接インポートするならCSVの変換は考えなくて良いので、それが一番だと思いますが、データがそれなりにあり処理時間も考慮が必要なら、CSV経由の方が高速なケースもあるかとは思います。
先ずはexcelを読み込んでbulk insertという所でしょうね。
OracleのSQLoaderで利用としているので、デミリッター付きのファイルの必要がある認識をしています。Excelでもいけるのかな？？？
アプリでファイルダイアログ開いて即SQLoaderを実行しようとしていたけれど、Oneステップかますかどうか、という感じです。
かますかな
ExcelデータをOracleに格納する3つの手法 (1/3)
https://www.atmarkit.co.jp/ait/articles/0507/08/news117.html
SQL＊Loderではexcelを直接使用する事はできません。
では やっぱり頑張るしかないですね。調べて頂きまして ありがとうございます。
0
エクセルで「Ctrl+h」で置換ボックスをだし、
検索する文字列に「Ctrl+j」として
すべて置換してみるのはいかがでしょうか。
※Ctrl+jは見た目は何もないように見えますが改行を表します。
投稿2020/07/09 03:30
総合スコア1923
一番分かりやすい対処法と思い 当該ブック状上で Ctrl+j を検索枠に入れ検索しましたが、対象なしとなりました。
当方Windows10ですが ピリオド らしき文字が 検索枠に 表示されました。
エクセルの場合、セル内での改行は提案した操作で取り除けることがあります。※改行コードがLFの場合
こういったシンプルなものでない場合は他の方の提案を参考にするのが良いと思います。
0
ベストアンサー
実際のデータになにが入ってるかをまず確認しましょう
あなたは不要な改行を、といってますが、それは果たして改行なんでしょうか
投稿2020/07/09 03:29
総合スコア87774
サクラエディタ以外とExcel以外で 何のユーティリティで確認すべきでしょうか？
それが分からないのです。
サクラエディタで開けば、どういう文字がそこにあるのかを見れると思います。ふつうはそれでそこにどういう文字があるのかを確認し、それに対してどう対応するのかを考えます。
が、それでもわからないということであるなら、
バイナリエディタなどでそのファイルを開いて、どういう文字が入ってるのかを確認する必要がありそうです。
ご見解をありがとうございます。サクラエディタで文字はみえず、改行されてしまっていることしか分かりません。
では 今からVectorで「バイナルエディタ」というキーワードで模索し 入手してみます。
Excelから出力したタブ区切りテキストファイルを バイナリエディタで確認したところ
意図せず改行されている部分文末で 「0d」が入っていることが分かりました。
論理的なレコードの区切りにも「od」が使われているので、やはりエディタでは 対策なし ということなのでしょうか
0x0dというのはCRというコントロールコードで、
改行は、0x0d0x0aの２文字で表されます（0x0aはLFです）
そこに意図せず混入してるコードが0x0d0x0aという組み合わせであるなら、改行との区別は付きません
そのばあいは、行先頭からの文字数かなんかで判定してこの改行を削除するということをしないといけないでしょうね
了解しました。なんとかExcelブックのまま 読み込むアプリにして、行列意識して内部で 区切り文字付きファイルを生成して DB側に採用するようにします。
0
LFを本当に削っていいのならば、sakuraエディターで
- \nを""(空列)に変換する
- \rを\r\nに変換する
の2ステップでCRLFは元のままで、LFのみ削除できます。
しかしあなたが余計なコードと判断しているコードは、本当に余計なコードとして削除して処理していいことを(周囲、上司に)確認していますか?
excelの1つのセル内で2行以上のデータを表示したい場合、改行したい位置でCNTL+改行と入力することで画面上は改行され、内部的にはLFコードが挿入されます。
excelでcsvに保存する場合、セルの内容に空白や改行記号を含むセルは「"」で囲まれます。
LFコードを含んでエディター画面上は改行されていても「"」で囲まれた範囲が1つのセルの値です。
セルのデータ中にLFコードを含んでいても問題ないようなプログラムにして、セル内のLFコードはそのままLFコードとして使用したほうがいいのでは?
(それともデータを改変して利用することが認められている/データを改変して利用することが求められているのですか?)
なお、sakuraエディターでこのcsvファイルを開くとLFコード部分でも改行されて表示されますが、通常の改行(CRLF)かLFかはエディター画面の下のコード表示に部分にCRLFやLFと表示されるので区別が付きます。
投稿2020/07/10 02:59
総合スコア1216
ご見解ありがとうございました。
エディタで開いた場合に 改行されたように見えるだけ、との問題であれば 無視しようと思ったのですが Oracleデータベース向けのSQLLoaderに その崩れた書式の区切り文字付のファイルでは 支障をきたしてしまったもので....
Excelブックをそのまま 読み込んで サーバ側で 常に同じレイアウトのファイルが形成されるようがんっばってみます。
LFを削ってOracleに登録すると、システムのマスターデータとOracleデータベースとで取引先名称の値が異なることになり、同一性のチェックができなくなる(難しくなる)ことを周知し、合意をとっておかないと、後で面倒なことになりかねませんよ。特にマスターデータに他の部分が同じで取引先名称中にLFがあるデータとLFがないデータとで区別したかった場合は対処方法がなくなります。
あなたの回答
tips
プレビュー
