ExcelブックとCSVを相互に変換する
CSVにはシート1つ分のデータしか保存できない
CSVには「シート」という概念がない。そのため、CSVファイルとExcelブックの間の変換は、「シートにまとめる」または「シートをばらす」に置き換えることもできる
CSVファイルをExcelブックに変換する
(方針)
・フォルダにある3ヶ月分の売り上げデータ(1ヶ月分が1つのCSVファイル)を、1つのExcelブックに変換する。
⇨CSVファイルごとにシートを追加し、「シート名」にはCSVファイル名を用いる
・また、データは次のように「1列目(売上日)」「2列目(顧客名称)」「6列目(計)」の3項目を抽出して転記することとする
(ポイント)
・まずは新規ブックを作成し、CSVファイルごとに「新規のシート」を追加する
・そこにws.append()で行ごとにデータを書き込む
・シート名には、file.stemにより「拡張子を除くファイル名」を取得して用いる
・ws.append()の括弧の中には、1行分のデータを「リスト」で追加する
・1列目、2列目、6列目のデータは、row[0]、row[1]、row[5]で取得できるが、日付と数値については、それぞれのデータ型に変換する必要がある
文字列型のデータを日付データに変換する
・CSVファイルでは、日付は「文字列」で入寮されているので、Excelのセルに「日付」として入力するには「datetime型」に変換する必要がある
⇨datetime.datetime.strptime()を用いる。括弧の中には、文字列の日付と書式を指定する
import datetime #書式が”2020/04/01”のような文字列の場合 dt = datetime.datetime.strptime(文字列の日付, "%Y/%m/%d") #書式が”2020-04-01”のような文字列の場合 dt = datetime.datetime.strptime(文字列の日付, "%Y-%m-%d")
・数値については、今回は金額を「整数」で表しているのでint()で整数型に変換する。ここで整数型に変換しないと、文字列でデータが書き込まれる
・全てのCSVファイルのデータを読み取り、各シートに転記。そのあとは、ブック作成時に自動追加されるシートを取り除いて、ブックを保存する
import csv import openpyxl from pathlib import Path import datetime #新規ブック作成 wb = openpyxl.Workbook() #「売上月別」フォルダー内CSVファイルの読み込み header_num = 3 for file in Path("売上月別").glob("*.csv"): #新規シートを追加 ws = wb.create_sheet(file.stem) #CSVファイルを開く f = open(file,encoding="shift-jis", errors='ignore') reader = csv.reader(f) #CSVファイルを1行ずつ読み込む for row in reader: if reader.line_num <= header_num: #ヘッダー行はそのまま追加 ws.append([row[0],row[1],row[5]]) continue #データ行は適宜データ型を変換 dt = datetime.datetime.strptime(row[0], "%Y/%m/%d") customer = row[1] sales = int(row[5]) #シートにデータ行を追加 ws.append([dt, customer, sales]) #日付のセルは書式を指定する ws.cell(reader.line_num,1).number_format = "yyyy/m/d" f.close #ブック作成時の既存シートを取り除く ws_first = wb.worksheets[0] wb.remove(ws_first) #ブック保存 wb.save("第1四半期売上.xlsx")
ExcelブックをCSVファイルに変換する
(ポイント)
・Excelブックの複数のシートを、1つのCSVファイルに格納することはできないので、シートごとにバラして保存する
pathlibモジュールでフォルダを作る
Excelブックから変換したCSVファイルは、新たに「売り上げデータ」というフォルダを作成して保存する
⇨プログラムでフォルダを作成するには、pathlibモジュールの「Path」でmkder()を実行する
・この括弧の中に「exist_ok=True」を指定しておくと、作成しようとするフォルダーが既に存在している場合でもエラーにならない(この命令自体がスキップされる)
from pathlib import Path Path(作成するフォルダ名).mkdir(exist_ok=True)
日付の書式を設定する
・日付のセルから.valueで値を取得すると、セルの値の読み取りで学習した通り、「datetime型」のデータが得られる。
これをそのままCSVファイルに書き込むと「2022-04-01 00:00:00」のような書式になるため、strftime()を使い、datetime型のデータを書式を指定した文字列に変換してから書き込む
書式を指定してdatetime型のデータを文字列に変換する
import datetime dt = datetime型のデータ #"2022/04/01"のような書式で文字列にする ds = dt.strftime("%Y/%m/%d") #"2022/4/1"のような書式で文字列にする ds = dt.strftime("%Y/%#m/%#d") #"2022-04-01"のような書式で文字列にする ds = dt.strftime("%Y-%m-%d")
ExcelブックをCSVファイルに変換するプログラム
import csv import openpyxl from pathlib import Path import datetime #元のブックを開く wb = openpyxl.load_workbook("売上データ.xlsx", data_only=True) #CSVファイル保存先のフォルダー Path("売上データ.xlsx").mkdir(exist_ok=True) #シートを1つずつCSVファイルに変換 for sheet in wb.worksheets: #CSVファイルのパスを作成 file = "売上データ /" + sheet.title + ".csv" #書き込みモードでCSVファイルを開く f = open(file, mode="w", newline="", encoding="shift-jis", errors='ignore') writer = csv.writer(f) #シートのデータをCSVファイルに書き込む for row in sheet: #1行分データを入れるリスト values = [] for cell in row: if isinstance(cell.value, datetime.datetime): #日付のデータは書式を指定して文字列にする values.append(cell.value.strftime("%Y/%m/%d")) else: values.append(cell.value) writer.writerow(values) f.close
結果