Introduction

This blog is useful for uploading a CSV file in desktop application(C#) and store data in MS-Access Database. Also you can modify connection string and query useful for stored data in SQL Server.

Using The Code

Step 1: Create a class to upload CSV file.

CSVUpload.cs

  1. using System.IO;
  2. using System.Text.RegularExpressions;
  3. using System.Data;
  4. public sealed class CsvReader: System.IDisposable {
  5. public CsvReader(string fileName): this(new FileStream(fileName, FileMode.Open, FileAccess.Read)) {}
  6. public CsvReader(Stream stream) {
  7. __reader = new StreamReader(stream);
  8. }
  9. public DataSet RowEnumerator {
  10. get {
  11. if (null == __reader) throw new System.ApplicationException("I can't start reading without CSV input.");
  12. __rowno = 0;
  13. string sLine;
  14. string sNextLine;
  15. DataSet ds = new DataSet();
  16. DataTable dt = ds.Tables.Add("TheData");
  17. while (null != (sLine = __reader.ReadLine())) {
  18. while (rexRunOnLine.IsMatch(sLine) && null != (sNextLine = __reader.ReadLine()))
  19. sLine += "\n" + sNextLine;
  20. __rowno++;
  21. DataRow dr = dt.NewRow();
  22. string[] values = rexCsvSplitter.Split(sLine);
  23. for (int i = 0; i < values.Length; i++) {
  24. values[i] = Csv.Unescape(values[i]);
  25. if (__rowno == 1) {
  26. dt.Columns.Add(values[i].Trim());
  27. } else {
  28. if (Csv.CharNotAllowes(values[i])) {
  29. dr[i] = values[i].Trim();
  30. }
  31. }
  32. }
  33. if (__rowno != 1) {
  34. dt.Rows.Add(dr);
  35. }
  36. //yield return values;
  37. }
  38. __reader.Close();
  39. return ds;
  40. }
  41. }
  42. public long RowIndex {
  43. get {
  44. return __rowno;
  45. }
  46. }
  47. public void Dispose() {
  48. if (null != __reader) __reader.Dispose();
  49. }
  50. //============================================
  51. private long __rowno = 0;
  52. private TextReader __reader;
  53. private static Regex rexCsvSplitter = new Regex(@
  54. ",(?=(?:[^"
  55. "]*"
  56. "[^"
  57. "]*"
  58. ")*(?![^"
  59. "]*"
  60. "))");
  61. private static Regex rexRunOnLine = new Regex(@
  62. "^[^"
  63. "]*(?:"
  64. "[^"
  65. "]*"
  66. "[^"
  67. "]*)*"
  68. "[^"
  69. "]*$");
  70. }
  71. public static class Csv {
  72. public static string Escape(string s) {
  73. if (s.Contains(QUOTE)) s = s.Replace(QUOTE, ESCAPED_QUOTE);
  74. if (s.IndexOfAny(CHARACTERS_THAT_MUST_BE_QUOTED) > -1) s = QUOTE + s + QUOTE;
  75. return s;
  76. }
  77. public static string Unescape(string s) {
  78. if (s.StartsWith(QUOTE) && s.EndsWith(QUOTE)) {
  79. s = s.Substring(1, s.Length - 2);
  80. if (s.Contains(ESCAPED_QUOTE)) s = s.Replace(ESCAPED_QUOTE, QUOTE);
  81. }
  82. return s;
  83. }
  84. public static bool CharNotAllowes(string s) {
  85. if (s.IndexOfAny(CHARACTERS_THAT_NOT_ALLOWED) > -1) {
  86. return false;
  87. } else {
  88. return true;
  89. }
  90. }
  91. private const string QUOTE = "\"";
  92. private const string ESCAPED_QUOTE = "\"\"";
  93. private static char[] CHARACTERS_THAT_MUST_BE_QUOTED = {
  94. ',', '"', '\n'
  95. };
  96. private static char[] CHARACTERS_THAT_NOT_ALLOWED = {
  97. '?', '!', '^', '*', '~', 'Ñ', '½', 'Ð', '', '»', 'µ', 'º', 'Ñ', '´'
  98. };
  99. }
Step 2: Create Design


Figure 1: CSV Reader

Step 3: Write Code
  1. using System;
  2. using System.Drawing;
  3. using System.Collections;
  4. using System.ComponentModel;
  5. using System.Windows.Forms;
  6. using System.Data;
  7. using System.Data.SqlClient;
  8. using System.Data.Odbc;
  9. using System.IO;
  10. using System.Data.OleDb;
  11. namespace Upload_Stock_Data {
  12. /// <summary>
  13. /// Summary description for frmMain.
  14. /// </summary>
  15. public class frmMain: System.Windows.Forms.Form
  16. {
  17. #region Declarations
  18. private System.Windows.Forms.GroupBox gbMain;
  19. private System.Windows.Forms.TextBox txtCSVFolderPath;
  20. private System.Windows.Forms.Button btnOpenFldrBwsr;
  21. private System.Windows.Forms.FolderBrowserDialog fbdCSVFolder;
  22. private System.Windows.Forms.TextBox txtCSVFilePath;
  23. private System.Windows.Forms.Button btnOpenFileDlg;
  24. private System.Windows.Forms.OpenFileDialog openFileDialogCSVFilePath;
  25. private System.Windows.Forms.Button btnImport;
  26. private System.Windows.Forms.DataGrid dGridCSVdata;
  27. string strCSVFile = "";
  28. private System.Windows.Forms.GroupBox gbMainUploadData;
  29. private System.Windows.Forms.Button btnUpload;
  30. System.Data.Odbc.OdbcDataAdapter obj_oledb_da;
  31. private bool bolColName = true;
  32. string strFormat = "CSVDelimited";
  33. private System.Windows.Forms.Label lblFolderPath;
  34. private System.Windows.Forms.Label lblFilePath;
  35. /// <summary>
  36. /// Required designer variable.
  37. /// </summary>
  38. private System.ComponentModel.Container components = null;#endregion
  39. #region Constructor
  40. public frmMain() {
  41. //
  42. // Required for Windows Form Designer support
  43. //
  44. InitializeComponent();
  46. //
  47. // TODO: Add any constructor code after InitializeComponent call
  48. //
  49. }#endregion
  50. #region Destructor
  51. /// <summary>
  52. /// Clean up any resources being used.
  53. /// </summary>
  54. protected override void Dispose(bool disposing) {
  55. if (disposing) {
  56. if (components != null) {
  57. components.Dispose();
  58. }
  59. }
  60. base.Dispose(disposing);
  61. }
  62. #endregion
  63. #region Windows Form Designer generated code
  64. /// <summary>
  65. /// Required method for Designer support - do not modify
  66. /// the contents of this method with the code editor.
  67. /// </summary>
  68. private void InitializeComponent() {
  69. this.gbMain = new System.Windows.Forms.GroupBox();
  70. this.lblFilePath = new System.Windows.Forms.Label();
  71. this.lblFolderPath = new System.Windows.Forms.Label();
  72. this.dGridCSVdata = new System.Windows.Forms.DataGrid();
  73. this.btnImport = new System.Windows.Forms.Button();
  74. this.btnOpenFileDlg = new System.Windows.Forms.Button();
  75. this.txtCSVFilePath = new System.Windows.Forms.TextBox();
  76. this.btnOpenFldrBwsr = new System.Windows.Forms.Button();
  77. this.txtCSVFolderPath = new System.Windows.Forms.TextBox();
  78. this.fbdCSVFolder = new System.Windows.Forms.FolderBrowserDialog();
  79. this.openFileDialogCSVFilePath = new System.Windows.Forms.OpenFileDialog();
  80. this.gbMainUploadData = new System.Windows.Forms.GroupBox();
  81. this.btnUpload = new System.Windows.Forms.Button();
  82. this.gbMain.SuspendLayout();
  83. ((System.ComponentModel.ISupportInitialize)(this.dGridCSVdata)).BeginInit();
  84. this.gbMainUploadData.SuspendLayout();
  85. this.SuspendLayout();
  86. //
  87. // gbMain
  88. //
  89. this.gbMain.BackColor = System.Drawing.SystemColors.InactiveCaptionText;
  90. this.gbMain.Controls.Add(this.lblFilePath);
  91. this.gbMain.Controls.Add(this.lblFolderPath);
  92. this.gbMain.Controls.Add(this.dGridCSVdata);
  93. this.gbMain.Controls.Add(this.btnImport);
  94. this.gbMain.Controls.Add(this.btnOpenFileDlg);
  95. this.gbMain.Controls.Add(this.txtCSVFilePath);
  96. this.gbMain.Controls.Add(this.btnOpenFldrBwsr);
  97. this.gbMain.Controls.Add(this.txtCSVFolderPath);
  98. this.gbMain.FlatStyle = System.Windows.Forms.FlatStyle.System;
  99. this.gbMain.Location = new System.Drawing.Point(16, 8);
  100. this.gbMain.Name = "gbMain";
  101. this.gbMain.Size = new System.Drawing.Size(504, 416);
  102. this.gbMain.TabIndex = 0;
  103. this.gbMain.TabStop = false;
  104. this.gbMain.Text = "Import CSV Data";
  105. //
  106. // lblFilePath
  107. //
  108. this.lblFilePath.Location = new System.Drawing.Point(32, 47);
  109. this.lblFilePath.Name = "lblFilePath";
  110. this.lblFilePath.Size = new System.Drawing.Size(72, 20);
  111. this.lblFilePath.TabIndex = 12;
  112. this.lblFilePath.Text = "File Path:";
  113. this.lblFilePath.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
  114. //
  115. // lblFolderPath
  116. //
  117. this.lblFolderPath.Location = new System.Drawing.Point(32, 23);
  118. this.lblFolderPath.Name = "lblFolderPath";
  119. this.lblFolderPath.Size = new System.Drawing.Size(72, 20);
  120. this.lblFolderPath.TabIndex = 11;
  121. this.lblFolderPath.Text = "Folder Path:";
  122. this.lblFolderPath.TextAlign = System.Drawing.ContentAlignment.MiddleLeft;
  123. //
  124. // dGridCSVdata
  125. //
  126. this.dGridCSVdata.AlternatingBackColor = System.Drawing.SystemColors.ControlLightLight;
  127. this.dGridCSVdata.CaptionForeColor = System.Drawing.Color.AliceBlue;
  128. this.dGridCSVdata.CaptionText = "Imported CSV Data";
  129. this.dGridCSVdata.DataMember = "";
  130. this.dGridCSVdata.ForeColor = System.Drawing.Color.YellowGreen;
  131. this.dGridCSVdata.HeaderBackColor = System.Drawing.Color.BlanchedAlmond;
  132. this.dGridCSVdata.HeaderForeColor = System.Drawing.Color.Black;
  133. this.dGridCSVdata.Location = new System.Drawing.Point(8, 124);
  134. this.dGridCSVdata.Name = "dGridCSVdata";
  135. this.dGridCSVdata.ParentRowsForeColor = System.Drawing.Color.Yellow;
  136. this.dGridCSVdata.ReadOnly = true;
  137. this.dGridCSVdata.SelectionForeColor = System.Drawing.SystemColors.ControlLight;
  138. this.dGridCSVdata.Size = new System.Drawing.Size(488, 276);
  139. this.dGridCSVdata.TabIndex = 5;
  140. //
  141. // btnImport
  142. //
  143. this.btnImport.Cursor = System.Windows.Forms.Cursors.Hand;
  144. this.btnImport.FlatStyle = System.Windows.Forms.FlatStyle.System;
  145. this.btnImport.Location = new System.Drawing.Point(112, 92);
  146. this.btnImport.Name = "btnImport";
  147. this.btnImport.Size = new System.Drawing.Size(280, 26);
  148. this.btnImport.TabIndex = 4;
  149. this.btnImport.Text = "Import CSV Data";
  150. this.btnImport.Click += new System.EventHandler(this.btnImport_Click);
  151. //
  152. // btnOpenFileDlg
  153. //
  154. this.btnOpenFileDlg.Cursor = System.Windows.Forms.Cursors.Hand;
  155. this.btnOpenFileDlg.FlatStyle = System.Windows.Forms.FlatStyle.System;
  156. this.btnOpenFileDlg.Location = new System.Drawing.Point(368, 47);
  157. this.btnOpenFileDlg.Name = "btnOpenFileDlg";
  158. this.btnOpenFileDlg.Size = new System.Drawing.Size(24, 23);
  159. this.btnOpenFileDlg.TabIndex = 3;
  160. this.btnOpenFileDlg.Click += new System.EventHandler(this.btnOpenFileDlg_Click);
  161. //
  162. // txtCSVFilePath
  163. //
  164. this.txtCSVFilePath.BackColor = System.Drawing.SystemColors.Info;
  165. this.txtCSVFilePath.Location = new System.Drawing.Point(112, 48);
  166. this.txtCSVFilePath.Name = "txtCSVFilePath";
  167. this.txtCSVFilePath.ReadOnly = true;
  168. this.txtCSVFilePath.Size = new System.Drawing.Size(240, 20);
  169. this.txtCSVFilePath.TabIndex = 2;
  170. this.txtCSVFilePath.Text = "D:\\Test\\Test.csv";
  171. //
  172. // btnOpenFldrBwsr
  173. //
  174. this.btnOpenFldrBwsr.Cursor = System.Windows.Forms.Cursors.Hand;
  175. this.btnOpenFldrBwsr.FlatStyle = System.Windows.Forms.FlatStyle.System;
  176. this.btnOpenFldrBwsr.Location = new System.Drawing.Point(368, 24);
  177. this.btnOpenFldrBwsr.Name = "btnOpenFldrBwsr";
  178. this.btnOpenFldrBwsr.Size = new System.Drawing.Size(24, 23);
  179. this.btnOpenFldrBwsr.TabIndex = 1;
  180. this.btnOpenFldrBwsr.Click += new System.EventHandler(this.btnOpenFldrBwsr_Click);
  181. //
  182. // txtCSVFolderPath
  183. //
  184. this.txtCSVFolderPath.BackColor = System.Drawing.SystemColors.Info;
  185. this.txtCSVFolderPath.Location = new System.Drawing.Point(112, 24);
  186. this.txtCSVFolderPath.Name = "txtCSVFolderPath";
  187. this.txtCSVFolderPath.ReadOnly = true;
  188. this.txtCSVFolderPath.Size = new System.Drawing.Size(240, 20);
  189. this.txtCSVFolderPath.TabIndex = 1;
  190. this.txtCSVFolderPath.Text = "D:\\Test";
  191. //
  192. // openFileDialogCSVFilePath
  193. //
  194. this.openFileDialogCSVFilePath.Filter = "CSV Files (*.csv)|*.csv|DAT Files (*.dat)|*.dat";
  195. this.openFileDialogCSVFilePath.Title = "Select the CSV file for importing";
  196. this.openFileDialogCSVFilePath.FileOk += new System.ComponentModel.CancelEventHandler(this.openFileDialogCSVFilePath_FileOk);
  197. //
  198. // gbMainUploadData
  199. //
  200. this.gbMainUploadData.BackColor = System.Drawing.SystemColors.InactiveCaptionText;
  201. this.gbMainUploadData.Controls.Add(this.btnUpload);
  202. this.gbMainUploadData.FlatStyle = System.Windows.Forms.FlatStyle.System;
  203. this.gbMainUploadData.Location = new System.Drawing.Point(16, 432);
  204. this.gbMainUploadData.Name = "gbMainUploadData";
  205. this.gbMainUploadData.Size = new System.Drawing.Size(504, 56);
  206. this.gbMainUploadData.TabIndex = 1;
  207. this.gbMainUploadData.TabStop = false;
  208. this.gbMainUploadData.Text = "Save Data in Table";
  209. //
  210. // btnUpload
  211. //
  212. this.btnUpload.Cursor = System.Windows.Forms.Cursors.Hand;
  213. this.btnUpload.Enabled = false;
  214. this.btnUpload.FlatStyle = System.Windows.Forms.FlatStyle.System;
  215. this.btnUpload.Location = new System.Drawing.Point(112, 24);
  216. this.btnUpload.Name = "btnUpload";
  217. this.btnUpload.Size = new System.Drawing.Size(280, 23);
  218. this.btnUpload.TabIndex = 1;
  219. this.btnUpload.Text = "Save";
  220. this.btnUpload.Click += new System.EventHandler(this.btnUpload_Click);
  221. //
  222. // frmMain
  223. //
  224. this.AcceptButton = this.btnImport;
  225. this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
  226. this.BackColor = System.Drawing.SystemColors.ActiveCaptionText;
  227. this.ClientSize = new System.Drawing.Size(536, 494);
  228. this.Controls.Add(this.gbMainUploadData);
  229. this.Controls.Add(this.gbMain);
  230. this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
  231. this.MaximizeBox = false;
  232. this.Name = "frmMain";
  233. this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
  234. this.Text = "CSV Reader";
  235. this.Closing += new System.ComponentModel.CancelEventHandler(this.frmMain_Closing);
  236. this.Load += new System.EventHandler(this.frmMain_Load);
  237. this.gbMain.ResumeLayout(false);
  238. this.gbMain.PerformLayout();
  239. ((System.ComponentModel.ISupportInitialize)(this.dGridCSVdata)).EndInit();
  240. this.gbMainUploadData.ResumeLayout(false);
  241. this.ResumeLayout(false);
  242. }#endregion
  243. #region Main() Method
  244. /// <summary>
  245. /// The main entry point for the application.
  246. /// </summary>
  247. [STAThread]
  248. static void Main() {
  249. Application.EnableVisualStyles();
  250. Application.DoEvents();
  251. Application.Run(new frmMain());
  252. }#endregion
  253. #region Form Load
  254. private void frmMain_Load(object sender, System.EventArgs e) {
  255. try {
  256. } catch (Exception ex) {
  257. MessageBox.Show(ex.Message);
  258. } finally {}
  259. }#endregion
  260. #region Open Folder Browser Button
  261. // On click of this button, the FOLDERBROWSERDIALOG opens where user can select the path of the folder
  262. // containing .csv files
  263. private void btnOpenFldrBwsr_Click(object sender, System.EventArgs e) {
  264. try {
  265. if (fbdCSVFolder.ShowDialog() == DialogResult.OK) {
  266. txtCSVFolderPath.Text = fbdCSVFolder.SelectedPath.Trim();
  267. }
  268. } catch (Exception ex) {
  269. MessageBox.Show(ex.Message);
  270. } finally {
  271. }
  272. }#endregion
  273. #region Open File Dialog Button
  274. // On click of this button, the openfiledialog opens where user can select .csv file
  275. private void btnOpenFileDlg_Click(object sender, System.EventArgs e) {
  276. try {
  277. openFileDialogCSVFilePath.InitialDirectory = txtCSVFolderPath.Text.Trim();
  278. if (openFileDialogCSVFilePath.ShowDialog() == DialogResult.OK) {
  279. txtCSVFilePath.Text = openFileDialogCSVFilePath.FileName.Trim();
  280. }
  281. } catch (Exception ex) {
  282. MessageBox.Show(ex.Message);
  283. } finally {}
  284. }#endregion
  285. #region Function For Importing Data From CSV File
  286. public DataSet ConnectCSV() {
  287. DataSet ds = new DataSet();
  288. string fileName = openFileDialogCSVFilePath.FileName;
  289. CsvReader reader = new CsvReader(fileName);
  290. ds = reader.RowEnumerator;
  291. dGridCSVdata.DataSource = ds;
  292. dGridCSVdata.DataMember = "TheData";
  293. return ds;
  294. }
  295. #endregion
  296. #region Button Import CSV Data
  297. private void btnImport_Click(object sender, System.EventArgs e) {
  298. try {
  299. if (txtCSVFolderPath.Text == "") {
  300. MessageBox.Show("The Folder Path TextBox cannot be empty.", "Warning");
  301. return;
  302. } else if (txtCSVFilePath.Text == "") {
  303. MessageBox.Show("The File Path TextBox cannot be empty.", "Warning");
  304. return;
  305. } else {
  306. //int intLengthOfFileName = txtCSVFilePath.Text.Trim().Length;
  307. //int intLastIndex = txtCSVFilePath.Text.Trim().LastIndexOf("\\");
  308. //strCSVFile = txtCSVFilePath.Text.Trim().Substring(intLastIndex, intLengthOfFileName - intLastIndex);
  309. //strCSVFile = strCSVFile.Remove(0, 1).Trim();
  310. // writeSchema();
  311. ConnectCSV();
  312. btnUpload.Enabled = true;
  313. }
  314. } catch (Exception ex) {
  315. MessageBox.Show(ex.Message);
  316. } finally {}
  317. }#endregion
  318. #region Button Insert Data
  319. // Here we will insert the imported data in our database
  320. private void btnUpload_Click(object sender, System.EventArgs e) {
  321. try {
  322. // Create an SQL Connection
  323. //SqlConnection con1 = new SqlConnection(ReadConFile().Trim());
  324. OleDbConnection con1 = new OleDbConnection();
  325. con1.ConnectionString = @
  326. "Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\\Users\\KML Surani\\Documents\\ImportCSV.accdb;Persist Security Info=False;";
  327. OleDbCommand cmd = new OleDbCommand();
  328. // Create Dataset
  329. DataSet da = new DataSet();
  330. // To actually fill the dataset, Call the function ImportCSV and assign the returned
  331. // dataset to new dataset as below
  332. da = this.ConnectCSV();
  333. // Now we will collect data from data table and insert it into database one by one
  334. // Initially there will be no data in database so we will insert data in first two columns
  335. // and after that we will update data in same row for remaining columns
  336. // The logic is simple. 'i' represents rows while 'j' represents columns
  337. con1.Open();
  338. cmd.Connection = con1;
  339. cmd.CommandType = CommandType.Text;
  340. for (int i = 0; i <= da.Tables["TheData"].Rows.Count - 1; i++) {
  341. cmd.CommandText = "Insert into tblImportCSV (Name,City) values('" + da.Tables["TheData"].Rows[i]["Name"] + "','" + da.Tables["TheData"].Rows[i]["City"] + "')";
  342. // For UPDATE statement, in where clause you need some unique row
  343. //identifier. We are using ‘srno’ in WHERE clause.
  344. //cmd1.CommandText = "Update Test set " + da.Tables["Stocks"].Columns[j].ColumnName.Trim() + " = '" + da.Tables["Stocks"].Rows[i].ItemArray.GetValue(j) + "' where srno =" + (i + 1);
  345. cmd.ExecuteNonQuery();
  346. //cmd1.ExecuteNonQuery();
  347. }
  348. con1.Close();
  349. } catch (Exception ex) {
  350. MessageBox.Show(ex.Message);
  351. } finally {
  352. btnUpload.Enabled = false;
  353. }
  354. }#endregion
  355. #region Form Closing
  356. private void frmMain_Closing(object sender, System.ComponentModel.CancelEventArgs e) {
  357. try {
  358. Application.Exit();
  359. } catch (Exception ex) {
  360. MessageBox.Show(ex.Message);
  361. } finally {}
  362. }#endregion
  363. private void openFileDialogCSVFilePath_FileOk(object sender, CancelEventArgs e) {
  364. }
  365. }
  366. }
In the same way, you can also upload .xlsx file in desktop application.