New DataSet Wizard
Configure streaming source
Date,Open,High,Low,Close,Volume,Adj Close 2009-12-31,6.00,6.00,6.00,6.00,400,6.00 2009-12-30,5.91,5.91,5.89,5.89,13500,5.89 2009-12-29,5.83,5.96,5.83,5.90,12700,5.90 ...
using System; using System.Collections.Generic; using System.Text; using System.Drawing; using WealthLab; using WealthLab.Indicators; using System.Data; using System.Data.SqlClient; using System.IO; using System.Globalization;namespace WealthLab.Strategies { public class SQLBulkImportCSV : WealthScript { /// <summary> /// Database name /// </summary> public static string dbName = "Database"; /// <summary> /// Path to directory with source CSV files /// </summary> public static string baseDir = @"C:\Data\"; /// <summary> /// Table name for bulk import /// </summary> public static string dirName = "Table"; /// <summary> /// SQL Server connection string /// </summary> public static string connString = @"server =.\MySQLServer;database=" + dbName; /// <summary> /// SQL Bulk import routine /// </summary> /// <param name="ticker">Symbol name</param> /// <param name="filepath">Full path to the symbol's data file</param> /// <returns></returns> static void ImportCSV(string ticker, string filepath) { using( SqlConnection con = new SqlConnection(connString) ) { // Split the file line by line StreamReader sr = new StreamReader(filepath); string line = sr.ReadLine(); // Here are the columns found in the data: Date, Open, High... string[] value = line.Split(','); string[] result = new string[0]; // An extra column for the Symbol field int newColumns = value.GetLength(0) + 1; // A DataTable for putting the CSV data in System.Data.DataTable dt = new System.Data.DataTable(); DataRow row; // Insert new column "Symbol" in CSV file being parsed (missing in the data) dt.Columns.Add(new DataColumn("Symbol")); // Add the other columns (DOHLCV+custom series) foreach (string dc in value) { dt.Columns.Add(new DataColumn(dc)); } // Prefill a string array with the Symbol name string[] sym = { ticker, "," }; int i = 0; // Read the CSV file line by line while (!sr.EndOfStream) { string source = sr.ReadLine(); // Skip empty lines and garbage if (source.Length <= 1) continue; // Treat the CSV comma-separated line as an array value = source.Split(','); // Resize the array to add space for the Symbol column Array.Resize(ref result, newColumns); // Insert the parsed row Array.Copy(value, 0, result, 1, value.Length); // Add the ticker to the very beginning result[0] = ticker; DateTime _dt = DateTime.Now; if (result.Length == dt.Columns.Count) { row = dt.NewRow(); // Assign the resulting string array to a new DataRow row.ItemArray = result; if (i != 0) dt.Rows.Add(row); } i++; } // Use the SqlBulkCopy class to write the DataTable made of a single stock's CSV file, to SQL Server SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock); bc.DestinationTableName = dirName; bc.BatchSize = dt.Rows.Count; con.Open(); bc.WriteToServer(dt); bc.Close(); con.Close(); } } protected override void Execute() { // Get the collection of Files from the Source Directory string destination = Path.Combine(baseDir, dirName); DirectoryInfo dirCustom = new DirectoryInfo(destination); FileInfo[] filCustom = dirCustom.GetFiles(); // Loop by each file with .CSV extension, importing its data to SQL Server foreach (FileInfo f in filCustom) { ImportCSV(f.Name.Replace(".csv",string.Empty), f.FullName); } } } }