در این مقاله ما یاد خواهیم گرفت چگونه در برنامه تحت ویندوز خود یک CSV FILE UPLOADER داشته باشیم. CSV یا همان Comma Seperated Values یک قالب فایل معروف و خیلی قدیمی است که در محیط‌ ها و نرم افزارهای بسیار مختلفی مورد استفاده قرار می‌گیرد.

CSV یا همان Comma Seperated Values یک قالب فایل معروف و خیلی قدیمی است که در محیط‌ ها و نرم افزارهای بسیار مختلفی مورد استفاده قرار می‌گیرد. در این قالب هر رکورد از اطلاعات در یک سطر فایل ذخیره می‌شود. و در هر سطر هم فیلدها به وسیله کاراکتر کاما «,» از هم جدا می‌شوند.

این آموزش برای آپلود فایلهای CSV در برنامه های تحت ویندوز و ذخیره داده در بانک اطلاعاتی اکسس مفید می باشد.

یک کلاس برای آپلود فایل های CSV بسازید.

    using System.IO;  
    using System.Text.RegularExpressions;  
    using System.Data;  
    public sealed class CsvReader: System.IDisposable {  
        public CsvReader(string fileName): this(new FileStream(fileName, FileMode.Open, FileAccess.Read)) {}  
        public CsvReader(Stream stream) {  
            __reader = new StreamReader(stream);  
        }  
        public DataSet RowEnumerator {  
            get {  
                if (null == __reader) throw new System.ApplicationException("I can't start reading without CSV input.");  
                __rowno = 0;  
                string sLine;  
                string sNextLine;  
                DataSet ds = new DataSet();  
                DataTable dt = ds.Tables.Add("TheData");  
                while (null != (sLine = __reader.ReadLine())) {  
                    while (rexRunOnLine.IsMatch(sLine) && null != (sNextLine = __reader.ReadLine()))  
                    sLine += "\n" + sNextLine;  
                    __rowno++;  
                    DataRow dr = dt.NewRow();  
                    string[] values = rexCsvSplitter.Split(sLine);  
                    for (int i = 0; i < values.Length; i++) {  
                        values[i] = Csv.Unescape(values[i]);  
                        if (__rowno == 1) {  
                            dt.Columns.Add(values[i].Trim());  
                        } else {  
                            if (Csv.CharNotAllowes(values[i])) {  
                                dr[i] = values[i].Trim();  
                            }  
                        }  
                    }  
                    if (__rowno != 1) {  
                        dt.Rows.Add(dr);  
                    }  
                    //yield return values;  
                }  
                __reader.Close();  
                return ds;  
            }  
        }  
        public long RowIndex {  
            get {  
                return __rowno;  
            }  
        }  
        public void Dispose() {  
            if (null != __reader) __reader.Dispose();  
        }  
        //============================================  
        private long __rowno = 0;  
        private TextReader __reader;  
        private static Regex rexCsvSplitter = new Regex(@  
        ",(?=(?:[^"  
        "]*"  
        "[^"  
        "]*"  
        ")*(?![^"  
        "]*"  
        "))");  
        private static Regex rexRunOnLine = new Regex(@  
        "^[^"  
        "]*(?:"  
        "[^"  
        "]*"  
        "[^"  
        "]*)*"  
        "[^"  
        "]*$");  
    }  
    public static class Csv {  
        public static string Escape(string s) {  
            if (s.Contains(QUOTE)) s = s.Replace(QUOTE, ESCAPED_QUOTE);  
            if (s.IndexOfAny(CHARACTERS_THAT_MUST_BE_QUOTED) > -1) s = QUOTE + s + QUOTE;  
            return s;  
        }  
        public static string Unescape(string s) {  
            if (s.StartsWith(QUOTE) && s.EndsWith(QUOTE)) {  
                s = s.Substring(1, s.Length - 2);  
                if (s.Contains(ESCAPED_QUOTE)) s = s.Replace(ESCAPED_QUOTE, QUOTE);  
            }  
            return s;  
        }  
        public static bool CharNotAllowes(string s) {  
            if (s.IndexOfAny(CHARACTERS_THAT_NOT_ALLOWED) > -1) {  
                return false;  
            } else {  
                return true;  
            }  
        }  
        private const string QUOTE = "\"";  
        private const string ESCAPED_QUOTE = "\"\"";  
        private static char[] CHARACTERS_THAT_MUST_BE_QUOTED = {  
            ',', '"', '\n'  
        };  
        private static char[] CHARACTERS_THAT_NOT_ALLOWED = {  
            '?', '!', '^', '*', '~', 'Ñ', '½', 'Ð', '', '»', 'µ', 'º', 'Ñ', '´'  
        };  
    }

using System; 
using System.Drawing; 
using System.Collections; 
using System.ComponentModel; 
using System.Windows.Forms; 
using System.Data; 
using System.Data.SqlClient; 
using System.Data.Odbc; 
using System.IO; 
using System.Data.OleDb; 
namespace Upload_Stock_Data { 
    /// <summary> 
	///sharhe kholase baraye frmMain
    /// </summary> 
    public class frmMain: System.Windows.Forms.Form  
    { 
        #region Declarations 
        private System.Windows.Forms.GroupBox gbMain; 
        private System.Windows.Forms.TextBox txtCSVFolderPath; 
        private System.Windows.Forms.Button btnOpenFldrBwsr; 
        private System.Windows.Forms.FolderBrowserDialog fbdCSVFolder; 
        private System.Windows.Forms.TextBox txtCSVFilePath; 
        private System.Windows.Forms.Button btnOpenFileDlg; 
        private System.Windows.Forms.OpenFileDialog openFileDialogCSVFilePath; 
        private System.Windows.Forms.Button btnImport; 
        private System.Windows.Forms.DataGrid dGridCSVdata; 
        string strCSVFile = ""; 
        private System.Windows.Forms.GroupBox gbMainUploadData; 
        private System.Windows.Forms.Button btnUpload; 
        System.Data.Odbc.OdbcDataAdapter obj_oledb_da; 
        private bool bolColName = true; 
        string strFormat = "CSVDelimited"; 
        private System.Windows.Forms.Label lblFolderPath; 
        private System.Windows.Forms.Label lblFilePath; 
        /// <summary> 
        /// variable mored niaz baraye tarahi 
        /// </summary> 
        private System.ComponentModel.Container components = null;#endregion 
        #region Constructor 
        public frmMain() { 

            InitializeComponent(); 
   
            // 
            // TODO: ezafe kardan har code constructor bad az seda zadan InitializeComponent			
            // 
        }#endregion 
        #region Destructor 
        /// <summary> 
        /// pak kardan hameye manabe estefade shode
        /// </summary> 
        protected override void Dispose(bool disposing) { 
            if (disposing) { 
                if (components != null) { 
                    components.Dispose(); 
                } 
            } 
            base.Dispose(disposing); 
        } 
        #endregion 
        #region Windows Form Designer generated code 
        /// <summary> 
		///methode mored niaz baraye tarahi,mohtavaye in method ra dar code editor taghir nadahid
        /// </summary> 
        private void InitializeComponent() { 
            this.gbMain = new System.Windows.Forms.GroupBox(); 
            this.lblFilePath = new System.Windows.Forms.Label(); 
            this.lblFolderPath = new System.Windows.Forms.Label(); 
            this.dGridCSVdata = new System.Windows.Forms.DataGrid(); 
            this.btnImport = new System.Windows.Forms.Button(); 
            this.btnOpenFileDlg = new System.Windows.Forms.Button(); 
            this.txtCSVFilePath = new System.Windows.Forms.TextBox(); 
            this.btnOpenFldrBwsr = new System.Windows.Forms.Button(); 
            this.txtCSVFolderPath = new System.Windows.Forms.TextBox(); 
            this.fbdCSVFolder = new System.Windows.Forms.FolderBrowserDialog(); 
            this.openFileDialogCSVFilePath = new System.Windows.Forms.OpenFileDialog(); 
            this.gbMainUploadData = new System.Windows.Forms.GroupBox(); 
            this.btnUpload = new System.Windows.Forms.Button(); 
            this.gbMain.SuspendLayout(); 
            ((System.ComponentModel.ISupportInitialize)(this.dGridCSVdata)).BeginInit(); 
            this.gbMainUploadData.SuspendLayout(); 
            this.SuspendLayout(); 
            //  
            // gbMain 
            //  
            this.gbMain.BackColor = System.Drawing.SystemColors.InactiveCaptionText; 
            this.gbMain.Controls.Add(this.lblFilePath); 
            this.gbMain.Controls.Add(this.lblFolderPath); 
            this.gbMain.Controls.Add(this.dGridCSVdata); 
            this.gbMain.Controls.Add(this.btnImport); 
            this.gbMain.Controls.Add(this.btnOpenFileDlg); 
            this.gbMain.Controls.Add(this.txtCSVFilePath); 
            this.gbMain.Controls.Add(this.btnOpenFldrBwsr); 
            this.gbMain.Controls.Add(this.txtCSVFolderPath); 
            this.gbMain.FlatStyle = System.Windows.Forms.FlatStyle.System; 
            this.gbMain.Location = new System.Drawing.Point(16, 8); 
            this.gbMain.Name = "gbMain"; 
            this.gbMain.Size = new System.Drawing.Size(504, 416); 
            this.gbMain.TabIndex = 0; 
            this.gbMain.TabStop = false; 
            this.gbMain.Text = "Import CSV Data"; 
            //  
            // lblFilePath 
            //  
            this.lblFilePath.Location = new System.Drawing.Point(32, 47); 
            this.lblFilePath.Name = "lblFilePath"; 
            this.lblFilePath.Size = new System.Drawing.Size(72, 20); 
            this.lblFilePath.TabIndex = 12; 
            this.lblFilePath.Text = "File Path:"; 
            this.lblFilePath.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; 
            //  
            // lblFolderPath 
            //  
            this.lblFolderPath.Location = new System.Drawing.Point(32, 23); 
            this.lblFolderPath.Name = "lblFolderPath"; 
            this.lblFolderPath.Size = new System.Drawing.Size(72, 20); 
            this.lblFolderPath.TabIndex = 11; 
            this.lblFolderPath.Text = "Folder Path:"; 
            this.lblFolderPath.TextAlign = System.Drawing.ContentAlignment.MiddleLeft; 
            //  
            // dGridCSVdata 
            //  
            this.dGridCSVdata.AlternatingBackColor = System.Drawing.SystemColors.ControlLightLight; 
            this.dGridCSVdata.CaptionForeColor = System.Drawing.Color.AliceBlue; 
            this.dGridCSVdata.CaptionText = "Imported CSV Data"; 
            this.dGridCSVdata.DataMember = ""; 
            this.dGridCSVdata.ForeColor = System.Drawing.Color.YellowGreen; 
            this.dGridCSVdata.HeaderBackColor = System.Drawing.Color.BlanchedAlmond; 
            this.dGridCSVdata.HeaderForeColor = System.Drawing.Color.Black; 
            this.dGridCSVdata.Location = new System.Drawing.Point(8, 124); 
            this.dGridCSVdata.Name = "dGridCSVdata"; 
            this.dGridCSVdata.ParentRowsForeColor = System.Drawing.Color.Yellow; 
            this.dGridCSVdata.ReadOnly = true; 
            this.dGridCSVdata.SelectionForeColor = System.Drawing.SystemColors.ControlLight; 
            this.dGridCSVdata.Size = new System.Drawing.Size(488, 276); 
            this.dGridCSVdata.TabIndex = 5; 
            //  
            // btnImport 
            //  
            this.btnImport.Cursor = System.Windows.Forms.Cursors.Hand; 
            this.btnImport.FlatStyle = System.Windows.Forms.FlatStyle.System; 
            this.btnImport.Location = new System.Drawing.Point(112, 92); 
            this.btnImport.Name = "btnImport"; 
            this.btnImport.Size = new System.Drawing.Size(280, 26); 
            this.btnImport.TabIndex = 4; 
            this.btnImport.Text = "Import CSV Data"; 
            this.btnImport.Click += new System.EventHandler(this.btnImport_Click); 
            //  
            // btnOpenFileDlg 
            //  
            this.btnOpenFileDlg.Cursor = System.Windows.Forms.Cursors.Hand; 
            this.btnOpenFileDlg.FlatStyle = System.Windows.Forms.FlatStyle.System; 
            this.btnOpenFileDlg.Location = new System.Drawing.Point(368, 47); 
            this.btnOpenFileDlg.Name = "btnOpenFileDlg"; 
            this.btnOpenFileDlg.Size = new System.Drawing.Size(24, 23); 
            this.btnOpenFileDlg.TabIndex = 3; 
            this.btnOpenFileDlg.Click += new System.EventHandler(this.btnOpenFileDlg_Click); 
            //  
            // txtCSVFilePath 
            //  
            this.txtCSVFilePath.BackColor = System.Drawing.SystemColors.Info; 
            this.txtCSVFilePath.Location = new System.Drawing.Point(112, 48); 
            this.txtCSVFilePath.Name = "txtCSVFilePath"; 
            this.txtCSVFilePath.ReadOnly = true; 
            this.txtCSVFilePath.Size = new System.Drawing.Size(240, 20); 
            this.txtCSVFilePath.TabIndex = 2; 
            this.txtCSVFilePath.Text = "D:\\Test\\Test.csv"; 
            //  
            // btnOpenFldrBwsr 
            //  
            this.btnOpenFldrBwsr.Cursor = System.Windows.Forms.Cursors.Hand; 
            this.btnOpenFldrBwsr.FlatStyle = System.Windows.Forms.FlatStyle.System; 
            this.btnOpenFldrBwsr.Location = new System.Drawing.Point(368, 24); 
            this.btnOpenFldrBwsr.Name = "btnOpenFldrBwsr"; 
            this.btnOpenFldrBwsr.Size = new System.Drawing.Size(24, 23); 
            this.btnOpenFldrBwsr.TabIndex = 1; 
            this.btnOpenFldrBwsr.Click += new System.EventHandler(this.btnOpenFldrBwsr_Click); 
            //  
            // txtCSVFolderPath 
            //  
            this.txtCSVFolderPath.BackColor = System.Drawing.SystemColors.Info; 
            this.txtCSVFolderPath.Location = new System.Drawing.Point(112, 24); 
            this.txtCSVFolderPath.Name = "txtCSVFolderPath"; 
            this.txtCSVFolderPath.ReadOnly = true; 
            this.txtCSVFolderPath.Size = new System.Drawing.Size(240, 20); 
            this.txtCSVFolderPath.TabIndex = 1; 
            this.txtCSVFolderPath.Text = "D:\\Test"; 
            //  
            // openFileDialogCSVFilePath 
            //  
            this.openFileDialogCSVFilePath.Filter = "CSV Files (*.csv)|*.csv|DAT Files (*.dat)|*.dat"; 
            this.openFileDialogCSVFilePath.Title = "Select the CSV file for importing"; 
            this.openFileDialogCSVFilePath.FileOk += new System.ComponentModel.CancelEventHandler(this.openFileDialogCSVFilePath_FileOk); 
            //  
            // gbMainUploadData 
            //  
            this.gbMainUploadData.BackColor = System.Drawing.SystemColors.InactiveCaptionText; 
            this.gbMainUploadData.Controls.Add(this.btnUpload); 
            this.gbMainUploadData.FlatStyle = System.Windows.Forms.FlatStyle.System; 
            this.gbMainUploadData.Location = new System.Drawing.Point(16, 432); 
            this.gbMainUploadData.Name = "gbMainUploadData"; 
            this.gbMainUploadData.Size = new System.Drawing.Size(504, 56); 
            this.gbMainUploadData.TabIndex = 1; 
            this.gbMainUploadData.TabStop = false; 
            this.gbMainUploadData.Text = "Save Data in Table"; 
            //  
            // btnUpload 
            //  
            this.btnUpload.Cursor = System.Windows.Forms.Cursors.Hand; 
            this.btnUpload.Enabled = false; 
            this.btnUpload.FlatStyle = System.Windows.Forms.FlatStyle.System; 
            this.btnUpload.Location = new System.Drawing.Point(112, 24); 
            this.btnUpload.Name = "btnUpload"; 
            this.btnUpload.Size = new System.Drawing.Size(280, 23); 
            this.btnUpload.TabIndex = 1; 
            this.btnUpload.Text = "Save"; 
            this.btnUpload.Click += new System.EventHandler(this.btnUpload_Click); 
            //  
            // frmMain 
            //  
            this.AcceptButton = this.btnImport; 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); 
            this.BackColor = System.Drawing.SystemColors.ActiveCaptionText; 
            this.ClientSize = new System.Drawing.Size(536, 494); 
            this.Controls.Add(this.gbMainUploadData); 
            this.Controls.Add(this.gbMain); 
            this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D; 
            this.MaximizeBox = false; 
            this.Name = "frmMain"; 
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; 
            this.Text = "CSV Reader"; 
            this.Closing += new System.ComponentModel.CancelEventHandler(this.frmMain_Closing); 
            this.Load += new System.EventHandler(this.frmMain_Load); 
            this.gbMain.ResumeLayout(false); 
            this.gbMain.PerformLayout(); 
            ((System.ComponentModel.ISupportInitialize)(this.dGridCSVdata)).EndInit(); 
            this.gbMainUploadData.ResumeLayout(false); 
            this.ResumeLayout(false); 
        }#endregion 
        #region Main() Method 
        /// <summary> 
        /// نقطه ورودی اصلی برنامه 
        /// </summary> 
        [STAThread] 
        static void Main() { 
            Application.EnableVisualStyles(); 
            Application.DoEvents(); 
            Application.Run(new frmMain()); 
        }#endregion 
        #region Form Load 
        private void frmMain_Load(object sender, System.EventArgs e) { 
            try { 
            } catch (Exception ex) { 
                MessageBox.Show(ex.Message); 
            } finally {} 
        }#endregion 
        #region Open Folder Browser Button 
        //ba click rooye in dokme FOLDERBROWSERDIALOG baz shode ta karbar betavanad adresse fooldere shamele file csv ra entekhab namayad     
        private void btnOpenFldrBwsr_Click(object sender, System.EventArgs e) { 
            try { 
                if (fbdCSVFolder.ShowDialog() == DialogResult.OK) { 
                    txtCSVFolderPath.Text = fbdCSVFolder.SelectedPath.Trim(); 
                } 
            } catch (Exception ex) { 
                MessageBox.Show(ex.Message); 
            } finally { 
            } 
        }#endregion 
        #region Open File Dialog Button 
        //ba click roye in dokme FOLDERBROWSERDIALOG baz shode ta karbar betavanad file csv ra entekhab konad
        private void btnOpenFileDlg_Click(object sender, System.EventArgs e) { 
            try { 
                openFileDialogCSVFilePath.InitialDirectory = txtCSVFolderPath.Text.Trim(); 
                if (openFileDialogCSVFilePath.ShowDialog() == DialogResult.OK) { 
                    txtCSVFilePath.Text = openFileDialogCSVFilePath.FileName.Trim(); 
                } 
            } catch (Exception ex) { 
                MessageBox.Show(ex.Message); 
            } finally {} 
        }#endregion 
        #region Function For Importing Data From CSV File 
        public DataSet ConnectCSV() { 
            DataSet ds = new DataSet(); 
            string fileName = openFileDialogCSVFilePath.FileName; 
            CsvReader reader = new CsvReader(fileName); 
            ds = reader.RowEnumerator; 
            dGridCSVdata.DataSource = ds; 
            dGridCSVdata.DataMember = "TheData"; 
            return ds; 
        } 
        #endregion 
        #region Button Import CSV Data 
        private void btnImport_Click(object sender, System.EventArgs e) { 
            try { 
                if (txtCSVFolderPath.Text == "") { 
                    MessageBox.Show("The Folder Path TextBox cannot be empty.", "Warning"); 
                    return; 
                } else if (txtCSVFilePath.Text == "") { 
                    MessageBox.Show("The File Path TextBox cannot be empty.", "Warning"); 
                    return; 
                } else { 
                    //int intLengthOfFileName = txtCSVFilePath.Text.Trim().Length; 
                    //int intLastIndex = txtCSVFilePath.Text.Trim().LastIndexOf("\\"); 
                    //strCSVFile = txtCSVFilePath.Text.Trim().Substring(intLastIndex, intLengthOfFileName - intLastIndex); 
                    //strCSVFile = strCSVFile.Remove(0, 1).Trim(); 
                    // writeSchema(); 
                    ConnectCSV(); 
                    btnUpload.Enabled = true; 
                } 
            } catch (Exception ex) { 
                MessageBox.Show(ex.Message); 
            } finally {} 
        }#endregion 
        #region Button Insert Data 
        //inja ma dadeh haye vared shode dar bank etelaati ra darj khahim kard
        private void btnUpload_Click(object sender, System.EventArgs e) { 
            try { 
                //sakhte connection sql
                //SqlConnection con1 = new SqlConnection(ReadConFile().Trim()); 
                OleDbConnection con1 = new OleDbConnection(); 
                con1.ConnectionString = @ 
                "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\\Users\\KML Surani\\Documents\\ImportCSV.accdb;Persist Security Info=False;"; 
                OleDbCommand cmd = new OleDbCommand(); 
                // sakhte Dataset  
                DataSet da = new DataSet();
                //
                // dar vaghe dataset ra por mikonad,methode importcsv ra seda zade va ekhtesase datasete bazgashti be datasete jadid motabegh zir
                da = this.ConnectCSV(); 
				//hala az data table dadeha ra jamavari karde va tak be tak dar database darj mikonim
				//dar ebteda dadeh i dar database nakhahad bod banabarin dade ra dar 2 sotoone aval darj karde sepas dadeh hara dar haman radif baraye
				//sotoon haye baghimande beroozresani mikonim
				//mantegh sadeh ast.'i' neshan dahande radif va 'j' neshandahande sotoon mibashad
                con1.Open(); 
                cmd.Connection = con1; 
                cmd.CommandType = CommandType.Text; 
                for (int i = 0; i <= da.Tables["TheData"].Rows.Count - 1; i++) { 
                    cmd.CommandText = "Insert into tblImportCSV (Name,City) values('" + da.Tables["TheData"].Rows[i]["Name"] + "','" + da.Tables["TheData"].Rows[i]["City"] + "')"; 
					//baraye beroozresanie statement , dar in band shoma niaz be yek shenasehe radif monhaser be fard darid
					//ma dar WHERE az ‘srno’ estefade mikonim. 
                    //cmd1.CommandText = "Update Test set " + da.Tables["Stocks"].Columns[j].ColumnName.Trim() + " = '" + da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j) + "' where srno =" + (i + 1); 
                    cmd.ExecuteNonQuery(); 
                    //cmd1.ExecuteNonQuery(); 
                } 
                con1.Close(); 
            } catch (Exception ex) { 
                MessageBox.Show(ex.Message); 
            } finally { 
                btnUpload.Enabled = false; 
            } 
        }#endregion 
        #region Form Closing 
        private void frmMain_Closing(object sender, System.ComponentModel.CancelEventArgs e) { 
            try { 
                Application.Exit(); 
            } catch (Exception ex) { 
                MessageBox.Show(ex.Message); 
            } finally {} 
        }#endregion 
        private void openFileDialogCSVFilePath_FileOk(object sender, CancelEventArgs e) { 
        } 
    } 
}

به همین طریق شما همچنین می توانید در برنامه های تحت ویندوز فایل های اکسل به فرمت .xlsx را آپلود نمایید.