locked
csv columns into multiple tables RRS feed

  • Question

  • importing CSV file columns into multiple tables in  sql server and with C# coding any examples?

    Friday, November 1, 2013 8:56 PM

Answers

  • If u wanna import a correct formation to DataTable, you can read this into a DataTable, and then do batchly inserting with the help of SqlDataAdapter.Update to upgrate all the newly-built rows into a table in SQL. (Below codes are from a Chinese forum for a general usage)

        public class CSVHelper
        {
            private string _csvFile;
    
            public CSVHelper(string csvFile)
            {
                this._csvFile = csvFile;
            }
    
            #region ICSVWriterReader Members
    
            public string CSVFile
            {
                get{ return _csvFile; }
                set{ _csvFile=value ;}
            }
    
            public DataTable Read()
            {
                FileInfo fi = new FileInfo(this._csvFile);
                if (fi == null || !fi.Exists) return null;
    
                StreamReader reader = new StreamReader(this._csvFile);
    
                string line = string.Empty; int lineNumber = 0;
    
                DataTable dt = new DataTable();
    
                while ((line = reader.ReadLine()) != null)
                {
                    if (lineNumber == 0)
                    {//Create Tole
                        dt = CreateDataTable(line);
                        if (dt.Columns.Count == 0) return null;
                    }
                    else
                    {
                        bool isSuccess = CreateDataRow(ref dt, line);
                        if (!isSuccess) return null;
                    }
                    lineNumber++;
                }
    
                return dt;
            }
    
            public bool Write(DataTable dt)
            {
                FileInfo fi = new FileInfo(this._csvFile);
                if (fi == null || !fi.Exists) return false;
    
                if (dt == null || dt.Columns.Count == 0 || dt.Rows.Count == 0) return false;
    
                StreamWriter writer = new StreamWriter(this._csvFile);
                //writer.AutoFlush = true;
    
                string line = string.Empty;
    
                line = CreateTitle(dt);
                writer.WriteLine(line);
    
                foreach (DataRow dr in dt.Rows)
                {
                    line = CretreLine(dr);
                    writer.WriteLine(line);
                }
    
                writer.Flush();
    
                return true;
            }
    
    
            private DataTable CreateDataTable(string line)
            {
                DataTable dt = new DataTable();
                foreach (string field in 
                    line.Split(FormatSplit, StringSplitOptions.None))
                {
                    dt.Columns.Add(field);
                }
                return dt;
            }
    
            private bool CreateDataRow(ref DataTable dt, string line)
            {
                DataRow dr = dt.NewRow();
    
                string[] fileds=line.Split(FormatSplit, StringSplitOptions.None);
    
                if (fileds.Length == 0 || fileds.Length != dt.Columns.Count) return false;
    
                for (int i = 0; i < fileds.Length; i++)
                {
                    dr[i] = fileds[i];
                }
    
                dt.Rows.Add(dr);
                return true;
            }
    
            private char[] FormatSplit
            {
                get { return new char[] { ',' }; }
            }
    
            private string CreateTitle(DataTable dt)
            {
                string line = string.Empty;
    
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    line += string.Format("{0}{1}", dt.Columns[i].ColumnName,FormatSplit[0].ToString());
                }
    
                line.TrimEnd(FormatSplit[0]);
    
                return line;
            }
    
            private string CretreLine(DataRow dr)
            {
                string line = string.Empty;
    
                for (int i = 0; i < dr.ItemArray.Length;i++)
                {
                    line += string.Format("{0}{1}", dr[i], FormatSplit[0].ToString());
                }
    
                line.TrimEnd(FormatSplit[0]);
    
                return line;
            }
    
            #endregion
        }
    }

    For Account Validation, please follow "Verify Account+Number" at http://social.msdn.microsoft.com/Forums/en-us/home?forum=reportabug

    For ASP.NET Question, please ask at http://forums.asp.net

    For other questions, you can ask at http://stackexchange.com/sites

    Click and Donate at http://www.freerice.com


    • Edited by ThankfulHeart Saturday, November 2, 2013 9:50 AM
    • Marked as answer by Caillen Tuesday, November 12, 2013 7:14 AM
    Saturday, November 2, 2013 9:49 AM