Attribute VB_Name = "sort01_code"

' 各銘柄の時系列データと財務情報を取得するためのクエリファイルを作成する
' 入力ファイル："C:\mysql\mysqldata\csv\bland_market_code.csv"
Option Base 1
Sub sort01_code()
    initial_time = Timer   ' 開始時刻
    '警告の表示を停止
    Application.DisplayAlerts = False
    
' ----------------------------------------------------------------------------------
    ' CSVファイル高速読み込み。CSVファイルをTXTファイルでSheet1に開く
    Dim corp_list(5000, 2) As String ' 行数5000、列数2を確保する(カラム数は正確に)
    Dim list_idx  As Integer         ' 会社リストファイルの会社コード数
    Const csv_dir As String = "C:\mysql\mysqldata\csv" ' CSVファイルのディレクトリ
    Const read_csv_name As String = "bland_market_code.csv" ' CSVファイル名

    
    If Dir(csv_dir & "\" & read_csv_name) <> read_csv_name Then
        MsgBox "ファイル「" & read_csv_name & "」はありません"
        Exit Sub
    End If
    Application.StatusBar = "( " & read_csv_name & " )読み込み中"

    ' CSVファイル読み込み
    list_idx = 1
    Open csv_dir & "\" & read_csv_name For Input As #1
    
    Do Until EOF(1)
        Input #1, corp_list(list_idx, 1), corp_list(list_idx, 2)
        list_idx = list_idx + 1
    Loop
    Close #1
    ' セルに書き込み。必要なのは第１フィールド
    ' Sheets("Sheet1").Range(Cells(1, 1), Cells(list_idx - 1, 1)).Value = corp_list
' ----------------------------------------------------------------------------------------
    
    Dim ix As Integer
    Dim corp_paste_row As Integer

    Dim idx_t As Integer
    Dim idx_q As Integer
    Dim idx_o As Integer
    Dim idx_n As Integer
    Dim idx_j As Integer
    Dim idx_s As Integer
    Dim idx_x As Integer

    Dim market_t(5000) As String
    Dim market_q(5000) As String
    Dim market_o(5000) As String
    Dim market_n(5000) As String
    Dim market_j(5000) As String
    Dim market_s(5000) As String
    Dim market_x(5000) As String
    
    ix = 1
    idx_t = 1
    idx_q = 1
    idx_o = 1
    idx_n = 1
    idx_j = 1
    idx_s = 1
    idx_x = 1


    
    Do While ix < list_idx
        If corp_list(ix, 2) = "t" Then
            market_t(idx_t) = corp_list(ix, 1)
            idx_t = idx_t + 1
        ElseIf corp_list(ix, 2) = "q" Then
            market_q(idx_q) = corp_list(ix, 1)
            idx_q = idx_q + 1
        ElseIf corp_list(ix, 2) = "o" Then
            market_o(idx_o) = corp_list(ix, 1)
            idx_o = idx_o + 1
        ElseIf corp_list(ix, 2) = "n" Then
            market_n(idx_n) = corp_list(ix, 1)
            idx_n = idx_n + 1
        ElseIf corp_list(ix, 2) = "j" Then
            market_j(idx_j) = corp_list(ix, 1)
            idx_j = idx_j + 1
        ElseIf corp_list(ix, 2) = "s" Then
            market_s(idx_s) = corp_list(ix, 1)
            idx_s = idx_s + 1
        ElseIf corp_list(ix, 2) = "x" Then
            market_x(idx_x) = corp_list(ix, 1)
            idx_x = idx_x + 1
        Else
            market_x(idx_x) = corp_list(ix, 1)
            idx_x = idx_x + 1
        End If
        ix = ix + 1
    Loop

    ' セルに書き込み
    Sheets("Sheet1").Select
    ix = 1
    corp_paste_row = 1
    Do While ix < idx_t
        Cells(corp_paste_row, 1) = market_t(ix)
        Cells(corp_paste_row, 2) = "t"
        ix = ix + 1
        corp_paste_row = corp_paste_row + 1
    Loop
    ix = 1
    Do While ix < idx_q
        Cells(corp_paste_row, 1) = market_q(ix)
        Cells(corp_paste_row, 2) = "q"
        ix = ix + 1
        corp_paste_row = corp_paste_row + 1
    Loop
    ix = 1
    Do While ix < idx_o
        Cells(corp_paste_row, 1) = market_o(ix)
        Cells(corp_paste_row, 2) = "o"
        ix = ix + 1
        corp_paste_row = corp_paste_row + 1
    Loop
    ix = 1
    Do While ix < idx_n
        Cells(corp_paste_row, 1) = market_n(ix)
        Cells(corp_paste_row, 2) = "n"
        ix = ix + 1
        corp_paste_row = corp_paste_row + 1
    Loop
    ix = 1
    Do While ix < idx_j
        Cells(corp_paste_row, 1) = market_j(ix)
        Cells(corp_paste_row, 2) = "j"
        ix = ix + 1
        corp_paste_row = corp_paste_row + 1
    Loop
    ix = 1
    Do While ix < idx_s
        Cells(corp_paste_row, 1) = market_s(ix)
        Cells(corp_paste_row, 2) = "s"
        ix = ix + 1
        corp_paste_row = corp_paste_row + 1
    Loop
    ix = 1
    Do While ix < idx_x
        Cells(corp_paste_row, 1) = market_x(ix)
        Cells(corp_paste_row, 2) = "x"
        ix = ix + 1
        corp_paste_row = corp_paste_row + 1
    Loop


' ------------------------------------------------------------------------------

    ' ファイル名を日付にしてCSV形式で保存
    ActiveWorkbook.SaveAs Filename:=csv_dir & "\" & "bland_market_sort.csv", _
        FileFormat:=xlCSV, CreateBackup:=False

    Sheets("bland_market_sort").Name = "Sheet1"

End Sub