C#调用csvtk进行CSV的转置，并拼接成ClickHouse

csvtk 下载地址，选择Windows64-bit， 下载后解压到D盘

原始行式CSV文件--data.csv

image.png

P1,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20
P2,1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31,33,35,37,39
P3,1,4,7,10,13,16,19,22,25,28,31,34,37,40,43,46,49,52,55,58
P4,1,5,9,13,17,21,25,29,33,37,41,45,49,53,57,61,65,69,73,77
P5,1,6,11,16,21,26,31,36,41,46,51,56,61,66,71,76,81,86,91,96
P6,1,7,13,19,25,31,37,43,49,55,61,67,73,79,85,91,97,103,109,115
P7,1,9,17,25,33,41,49,57,65,73,81,89,97,105,113,121,129,137,145,153
P8,1,10,19,28,37,46,55,64,73,82,91,100,109,118,127,136,145,154,163,172
P9,1,11,21,31,41,51,61,71,81,91,101,111,121,131,141,151,161,171,181,191
P10,1,12,23,34,45,56,67,78,89,100,111,122,133,144,155,166,177,188,199,210

image.png

WPF 拖一个按钮，后台代码为

 private void Button_Click(object sender, RoutedEventArgs e)
        {
            try
            {

                string tableName = "Test";
                string sourcePath = @"D:\data.csv";
                string targetPath = sourcePath.Replace(".csv", "_out.csv");

                Transpose(sourcePath, targetPath);
                Import(targetPath, tableName);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        private string Transpose(string sourcePath, string targetPath)
        {
            Process process = new Process();
            process.StartInfo.Arguments = String.Format(" transpose {0} -o {1}", sourcePath, targetPath);
            process.StartInfo.UseShellExecute = false;
            process.StartInfo.CreateNoWindow = true;
            process.StartInfo.FileName = @"D:\csvtk.exe";
            process.StartInfo.RedirectStandardInput = true;
            process.StartInfo.RedirectStandardOutput = true;
            process.StartInfo.RedirectStandardError = true;
            process.StartInfo.StandardOutputEncoding = Encoding.UTF8;//将编码设置成utf-8，保证中文不会乱码。
            process.Start();
            return process.StandardOutput.ReadToEnd();//返回结果
        }

        private void Import(string targetPath, string tableName)
        {
            string insertSqlTemp = "INSERT INTO {0}.{1} ({2}) VALUES";
            string valueSqlTemp = "({0}),";
            string strLine = string.Empty;
            StringBuilder sb = new StringBuilder();

            using (StreamReader sr = new StreamReader(targetPath))
            {
                strLine = sr.ReadLine();
                if (strLine == null)
                {
                    return;
                }
                sb.Append(string.Format(insertSqlTemp, "default", tableName, strLine)); //第一行作为数据库的列名
                while ((strLine = sr.ReadLine()) != null)
                {
                    sb.Append(string.Format(valueSqlTemp, strLine));
                }
            }
            string sql = sb.ToString().Remove(sb.Length - 1, 1);
            //CKHelper.Insert(sql);
        }

转置后自动生成常见的CSV形式，并生成插入SQL语句

image.png image.png