カゲンぱぱのイイカゲンメモ帳: csv

ラベル csv の投稿を表示しています。すべての投稿を表示

2019年3月29日金曜日

PHPでCSVを読み込んで、Arrayに変換するメソッド、最初の行を横方向連想配列のキーとするや、縦方向のid列を指定するや、一部の列のみ抽出するとか、機能盛りだくさん

ラベル: array, csv, php

/* $csvPath: CSV file path
  * $firstRowAsKey: whether make the first row as key horizontally. or just number, duplicate will not be over writed
  * $columnAsKey: whether to set key(column name or column index) column vertically. or just number, duplicate will not be over writed
  * $columnAsVal: Array or Single Column name or column index from 0, whether to filte columns
  * $smartFlat:if horizontally array only have one key, then falt it.
  *
  * For Example: CSV(this methord will take care of sjis utf8 encode problem)
  * name,point
  * Joe,2
  * Peter,321
  * Jane,34
  *
  * $firstRowAsKey = true, $columnAsKey = null, $columnsAsVal = null
  * Array([0] => Array([name] => Joe, [point] => 2), [1] => Array([name] => Peter,[point] => 321), [2] => Array([name] => Jane, [point] => 34))
  *
  * firstRowAsKey = false, $columnAsKey = 0, $columnsAsVal = null
  * Array([name] => Array([0] => name, [1] => point), [Joe] => Array([0] => Joe, [1] => 2), [Peter] => Array([0] => Peter, [1] => 321), [Jane] => Array([0] => Jane, [1] => 34))
  *
  * firstRowAsKey = true, $columnAsKey = 'name', $columnsAsVal = point
  * Array([Joe] => Array([point] => 2), [Peter] => Array([point] => 321), [Jane] => Array([point] => 34))
  */
 function readArrayCSV($csvPath, $firstRowAsKey = true, $columnAsKey = null, $columnsAsVal = null, $smartFlat = true) {
  $sjis = file_get_contents($csvPath);
  $utf8 = mb_convert_encoding($sjis, 'UTF-8', 'SJIS-win');
  $temp = tempnam(sys_get_temp_dir(), 'TMP_');
  file_put_contents($temp, $utf8);
  $csv = file($temp, FILE_IGNORE_NEW_LINES);

  if ($firstRowAsKey) {
   $titles = explode(",", array_shift($csv));
   $csv = array_map('str_getcsv', $csv);
   $csv = array_map(function ($row) use ($titles) {
       return array_combine($titles, $row);
   },$csv);
  } else {
   $csv = array_map('str_getcsv', $csv);
  }

  if ($columnAsKey !== null) {
   $result = array();
   if (is_int($columnAsKey) && $columnAsKey > 0) $columnAsKey--;
   foreach ($csv as $row) {
    if (isset($row[$columnAsKey])) {
     $row_filtered = $row;
     if ($columnsAsVal !== null) {
      $row_filtered = array_intersect_key($row, array_flip((array)$columnsAsVal));
     }
     $result[$row[$columnAsKey]] = ($smartFlat?(count($row_filtered) == 1?array_shift(array_values($row_filtered)):$row_filtered):$row_filtered);
    }
   }

  } else {
   $result = $csv;
  }
  return $result;
 }

2013年9月7日土曜日

vba querytable csv取り込み　文字コード対応　TextFilePlatform

ラベル: csv, vba

TextFilePlatform = 932 # SJIS 932, EUC-JP 20932, UTF8 65001, ASCII 1252

2013年6月20日木曜日

rubyでFTPからCSV(?)をゲットして、DBに突っ込む

ラベル: csv, ftp, mysql, ruby

ftp.yml



remote_path: pub/test

retry: 3

host: 192.168.1.70

username: anonymous

password: anonymous

get_ftp.rb
sample usage: # ruby get_ftp.rb index_hist
will get 'index_hist.gz' from ftp and gunzip it to index_hist

require 'net/ftp'
require 'yaml'
cnt_retry = 0
puts "start process..."
begin
  ftp_cfg = YAML.load_file("ftp.yml")
  fn = ARGV[0]

  ftp = Net::FTP.open(ftp_cfg["host"])
  ftp.login(ftp_cfg["username"],ftp_cfg["password"])
  ftp.chdir(ftp_cfg["remote_path"])
  puts "getting #{fn}.gz from FTP for attempt #{cnt_retry}..."
  ftp.getbinaryfile("#{fn}.gz")
  system("gunzip -f #{fn}.gz")
  ftp.close
rescue => err
  if cnt_retry + 1 < 3
    cnt_retry += 1
    sleep 5
    retry
  else
    raise err
    log.error "There was an error: #{err.message}"
  end
else
  puts "Job done."
end

import to a mysql database db.yml


host: 192.168.1.70

username: kagen

password: kagen

database: db_development

import_indices.rb
sample usage1: # ruby import_indices.rb master index_master
will import data from index_master into table 'indices'
sample usage2: # ruby import_indices.rb hist index_hist
will import data from index_hist into table 'index_value_hist'

require 'yaml'
require 'kconv'
require 'mysql'
puts "start process of data import..."
begin
  my = Mysql::init()
  db_cfg = YAML.load_file("db.yml")
  if ARGV[0] == "master"
    tbl = "indices"
    fld = "(itemcode, jpname, engname, jpsourcename, engunitname, unit, decimalpoint, startmonth, updated_at)"
  else
    tbl = "index_value_hist"
    fld = "(itemcode, subcode, cycle, yyyy, mm, dd, val, updated_at)"
  end
  puts "Connecting to host #{db_cfg["host"]} with user #{db_cfg["username"]} using database #{db_cfg["database"]}..."
  my.real_connect(db_cfg["host"], db_cfg["username"], db_cfg["password"], db_cfg["database"])
  puts "Connected Successfully"
  my.query("SET AUTOCOMMIT=0")
  puts "Start transaction..."
  my.query("START TRANSACTION")
  if ARGV[0] == "master"
    puts "Processing Index master"
  elsif ARGV[0] == "hist"
    puts "Processing Index values history"
  end

  begin
    File.open(ARGV[1]) do |f|
      f.each_line do |row|
        sql = nil
        rows = row.split(":")
        #puts rows.inspect
        if ARGV[0] == "master"
          sql = "INSERT INTO #{tbl} #{fld} VALUES('#{rows[0]}', '#{rows[1].toutf8}', '#{rows[2]}', '#{rows[3].toutf8}', '#{rows[4].toutf8}', '#{rows[5]}', '#{rows[6]}', '#{rows[7]}', CURRENT_TIMESTAMP)"
        elsif ARGV[0] == "hist"
          if rows[3] == "D"
            if rows[0].strip == "DEL"
              sql = "DELETE FROM #{tbl} WHERE itemcode = '#{rows[1].strip}' AND subcode = '#{rows[2].strip}' AND cycle = 'D' AND yyyy = '#{rows[4][0..3]}' AND mm = '#{rows[4][4..5]}' AND dd = '#{rows[4][6..7]}'"
            elsif rows[0].strip == "UPD"
              sql = "INSERT INTO #{tbl} #{fld} VALUES('#{rows[1].strip}', '#{rows[2].strip}', 'D', '#{rows[4][0..3]}', '#{rows[4][4..5]}', '#{rows[4][6..7]}', '#{rows[5]}', CURRENT_TIMESTAMP)"
            end
          elsif rows[3] == "M"
            if rows[0].strip == "DEL"
              sql = "DELETE FROM #{tbl} WHERE itemcode = '#{rows[1].strip}' AND subcode = '#{rows[2].strip}' AND cycle = 'D' AND yyyy = '#{rows[4][0..3]}' AND mm = '#{rows[4][4..5]}'"
            elsif rows[0].strip == "UPD"
              sql = "INSERT INTO #{tbl} #{fld} VALUES('#{rows[1].strip}', '#{rows[2].strip}', 'M', '#{rows[4][0..3]}', '#{rows[4][4..5]}', '', '#{rows[5]}', CURRENT_TIMESTAMP)"
            end
          end
        end
        if sql
          #puts sql
          my.query(sql)
        end
      end
    end
    my.query("COMMIT")
  rescue => err
    puts "rollback changes..."
    my.query("ROLLBACK")
    raise err
    log.error "There was an error while insert db: #{err.message}"
  end
rescue => err
  raise err
  log.error "There was an error: #{err.message}"
else
  puts "Job done."
end

create tabless


CREATE TABLE `indices` (

`itemcode`  varchar(15) NOT NULL ,

`jpname`  varchar(30) NOT NULL ,

`engname`  varchar(28) NOT NULL ,

`jpsourcename`  varchar(32) NOT NULL ,

`jpunitname`  varchar(30) NOT NULL ,

`engunitname`  varchar(30) NOT NULL ,

`unit`  int(8) NOT NULL ,

`decimalpoint`  int(8) NOT NULL ,

`startmonth`  int(8) NOT NULL ,

`updated_at`  datetime NOT NULL ,

PRIMARY KEY (`itemcode`)

) type=InnoDB;

CREATE TABLE `index_value_hist` (

`itemcode`  varchar(15) NOT NULL ,

`subcode`  varchar(5) NOT NULL ,

`cycle`  varchar(1) NOT NULL ,

`yyyy`  varchar(4) NOT NULL ,

`mm`  varchar(2) NOT NULL ,

`dd`  varchar(2) NOT NULL ,

`val`  double(24,7) NOT NULL ,

`updated_at`  date NOT NULL ,

PRIMARY KEY (`itemcode`, `subcode`, `cycle`, `yyyy`, `mm`, `dd`)

) type=InnoDB;

Sample Data to import (EUC, LF) No title row ☆index_value_hist only☆


UPD :AREGEN         :A    :M:201212:150463.7400000:20121218

UPD :AREGEN         :C    :M:201212:158030.9800000:20121218

UPD :AREGEN         :C    :W:2012124:158030.9800000:20121218

UPD :AREGEN         :H    :M:201212:158030.9800000:20121218

...

2012年9月6日木曜日

mysqlのload data infile

ラベル: csv, mysql

一般的にCSVファイルをmysqlコンソルで直接流し込む...



csvの中身はこんな感じ：

10000031,"ああああ",""

10000041,"いいいい",""

10000050,"うううう","おおおお"

10000130,"ささささ","しししし"

文字コードはsjisで、改行コードはCRLF



流しこむコマンド：

mysql> set character_set_database=sjis;

mysql> LOAD DATA INFILE "C:\\Documents and Settings\\username\\data.csv" INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' STARTING BY '';

LINES TERMINATED追加しないとおかしいことになる。

2012年8月15日水曜日

大量データのCSVをソート方法

ラベル: csv, excel, vba

Excelのソート機能を使う方法

Dim wb As New Workbook
    Dim st As New Worksheet
    Dim sortField As Range
    Dim rg As Range

    'csvファイルを開く
    Set wb = Workbooks.Open("c:\abc.csv")

    'シートをセット（csvファイルはsheet1しかない）
    Set st = wb.Sheets(1)
    
    For Each rg In st.Range("1:1")
        If rg = "容器番号" Then 'ソートする列を探す
            Set sortField = rg
            Exit For
        End If
    Next
    'ソート(容器番号)
    st.UsedRange.Sort Key1:=sortField, Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
        :=xlPinYin

大量のデータの時、65535行（excel2007以下）,100万？を超えた時

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source='c:\';" & _
                    "Extended Properties='Text;HDR=YES'"
    'c:\abc.csvというファイルをデータベーステーブルとして開く、「容器番号」という列を昇順でソート                
    Set rs = cn.Execute("SELECT * FROM abc.csv ORDER BY clng(容器番号) ASC")
    
    Do Until rs.EOF

       'データを書き出しなどの処理
       '...

       rs.MoveNext
    Loop

"HDR=YES"は最初の行はタイトルという意味。

ちなみに、下の順番にならないためには、

クエリーをこのようにclng関数を使う



SELECT * FROM abc.csv ORDER BY clng(容器番号) ASC

sqlではcastやconvertなど使いますが、Microsoft.Jet.OLEDB(Access)ではclngなどで文字列を数字に変換する

2011年12月30日金曜日

ラベル: csv, rails, ruby

前回（サーバからcsvを複数取得する）の続きに、取得したcsvをDBにinsertする

app/libの下にprice_importer.rbを作成し、app/で実行する



$ ruby script/runner PriceImporter.execute

以下の内容

require 'csv'
class PriceImporter
  class << self
    def execute()
      begin
          result = system("/path/to/script/getfile.sh")  #←<a href="http://kagen88.blogspot.com/2011/12/shelllftp.html">前回作成したshellスクリプト</a>
          print result
          Dir::glob("/path/to/data/*.csv").each {|f|  #←取得したcsvファイルを保存する場所の（すべての?）csvをループ
            puts "#{f}: #{File::stat(f).size} bytes"  #←サイズを表示してみる
            cf=File.open(f,"r")  #←開く
            buf=cf.read()  #←読む
            @parsed_file = CSV::Reader.parse(convertSjis2UTF8(buf))  #←CSVハッシュに格納、その前にsjisをutf8に
            @parsed_file.shift  #←最初のタイトル行をスキップ
            @parsed_file.each  do |row|
              pd = Product.find_by_code(row[0])  #←既存のテーブルモデルを使って、コードの有無を確認
              if pd
                Price.delete_all "pd_id = " + pd.id.to_s + " AND base_date_on = '" + row[4] + "'"  #←上書きしたいので、一致したレコードを削除
                p = Price.new
                p.pd_id = pd.id
                p.base_date_on = row[4]
                p.price = row[5]
                p.save  #←インサート
              else
                RAILS_DEFAULT_LOGGER.debug("コード（" + row[0] + "）がproductsテーブルに存在しない。ご確認ください。")
                puts "コード（" + row[0] + "）がproductsテーブルに存在しない。ご確認ください。"
              end
            end
          }
      rescue => e
        RAILS_DEFAULT_LOGGER.debug(e)
        raise
      end
    end
    def convertSjis2UTF8(val)←utf8変換
      val.kconv(Kconv::UTF8, Kconv::SJIS) unless val.blank?
    end
  end
end

以上

登録: 投稿 (Atom)