C# と VB.NET の質問掲示板

↓アドリブで書いてみた。

Module Module1

    Sub AddRecord(ByVal dic As SortedDictionary(Of String, SortedDictionary(Of String, Integer)), ByVal name As String, ByVal ds As String, ByVal price As Integer)
        If Not dic.ContainsKey(name) Then
            dic.Add(name, New SortedDictionary(Of String, Integer)) ' "商品名"追加
        End If
        If Not dic(name).ContainsKey(ds) Then
            dic(name).Add(ds, 0) ' "日付"追加
        End If
        dic(name)(ds) = dic(name)(ds) + price ' ここで積算
    End Sub

    Sub MakeSummary(ByVal dic As SortedDictionary(Of String, SortedDictionary(Of String, Integer)))
        For Each name As String In dic.Keys
            Console.Write("{0} ", name)
            For Each datestr As String In dic(name).Keys
                Console.Write(",{0},{1} ", datestr, dic(name)(datestr))
            Next
            Console.WriteLine()
        Next
    End Sub

    Sub Main()
        Dim dic As New SortedDictionary(Of String, SortedDictionary(Of String, Integer))
        AddRecord(dic, "りんご", "0521", 10)
        AddRecord(dic, "みかん", "0521", 20)
        AddRecord(dic, "ぶどう", "0521", 30)
        AddRecord(dic, "りんご", "0522", 40)
        AddRecord(dic, "みかん", "0522", 50)
        AddRecord(dic, "ぶどう", "0522", 60)
        AddRecord(dic, "りんご", "0521", 10)
        AddRecord(dic, "みかん", "0521", 20)
        AddRecord(dic, "ぶどう", "0521", 30)
        AddRecord(dic, "りんご", "0522", 40)
        AddRecord(dic, "みかん", "0522", 50)
        AddRecord(dic, "ぶどう", "0522", 60)
        MakeSummary(dic)
    End Sub

End Module

--- 実行結果 : "品名, 日付, 金額, 日付, 金額 ..." ---
ぶどう ,0521,60 ,0522,120
みかん ,0521,40 ,0522,100
りんご ,0521,20 ,0522,80

※ わざと「要求とは異なる仕様」になってます。

■No36110 (K さん) に返信
> 日付    ,商品名,売上数量,売上金額
> 20080401,AAAA  ,       1,     150
> 20080401,BBBB  ,       2,     200
> 20080401,CCCC  ,       4,     400
> 20080402,AAAA  ,       2,     300
> 20080402,BBBB  ,       1,     100
> 20080402,CCCC  ,       3,     300
> ･･･以下月末まで

固定長 CSV ですか?
それとも、実際にはスペースは含まれていないのでしょうか?


> 商品名,1日_数量,1日_金額,2日_数量,2日_金額,･･･(同様に月末まで)
> AAAA　,       1,     150,       2,     300,･･･
> BBBB  ,       2,     200,       1,     100,･･･

AAAA の後に全角スペース1つ、
BBBB の後に半角スペース2つ…?


> ですが、どのように集計をすれば良いかがわからないです。

クロス集計をかけてみるとか。

Imports System
Imports System.Text
Imports System.Data.OleDb

Public Class Form1
    Private TargetDirectory As String = "C:\DATA\"
    Private FileName As String = "TEST.CSV"
    Private tbl As DataTable

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs) Handles MyBase.Load
        Dim q As New StringBuilder()
        q.AppendLine("TRANSFORM SUM(売上{0}) ")
        q.AppendLine("SELECT 商品名 FROM [" & FileName & "]")
        q.AppendLine("WHERE 日付 BETWEEN 20080400 AND 20080499")
        q.AppendLine("GROUP BY 商品名")
        q.AppendLine("PIVOT '{0}_' & RIGHT(日付, 2)")

        Dim c As New OleDbConnectionStringBuilder()
        c.Provider = "Microsoft.Jet.OLEDB.4.0"
        c.DataSource = TargetDirectory
        c.Add("Extended Properties", "Text;HDR=YES;IMEX=1")
        Dim s As String = c.ConnectionString

        Dim t1 As New DataTable()
        Using adp As New OleDbDataAdapter(String.Format(q.ToString(), "数量"), s)
            adp.Fill(t1)
        End Using
        Dim t2 As New DataTable()
        Using adp As New OleDbDataAdapter(String.Format(q.ToString(), "金額"), s)
            adp.Fill(t2)
        End Using
        t1.PrimaryKey = New DataColumn() {t1.Columns("商品名")}
        t2.PrimaryKey = New DataColumn() {t2.Columns("商品名")}
        t1.Merge(t2, False, MissingSchemaAction.AddWithKey)

        tbl = t1
        DataGridView1.DataSource = tbl
    End Sub
End Class