34300

how to manage the data I insert into a table using BULK INSERT

Question:

I have a <strong>C# application</strong> which captures data from a <strong>csv</strong> file to a <strong>SQL table</strong>

the text file looks like this

<strong>students.csv</strong>

Header 201501 id code amount 1 ab11 5000 2 ab11 6000 3 ab11 8000 3 wx34 2500 3 df21 1000 4 ab11 7000 4 zx54 3500

each item is in it own cell in the csv file. "I just spaced this so that you can see what is in the file".

basically I have to insert this data to two table but as you can see in the <strong>id column</strong> the are elements with the same number and <strong>id is the primary key</strong>.

the tables should look as follows:

<strong>tblStudents</strong>

id code amount 1 ab11 5000 2 ab11 6000 3 ab11 8000 4 ab11 7000

and the other table should have all data with data only with the <strong>code that isn't "ab11"</strong> and should have the <strong>month which appears in csv file next to Header.</strong> And look as follows

<strong>tblPaid</strong>

Header 201501 id code amount month 3 wx34 2500 201501 3 df21 1000 201501 4 zx54 3500 201501

This is what I have at the moment but I can't seem to get a output

private void btnSelect_Click(object sender, EventArgs e) { btnNext.Enabled = true; openFileDialog1.Filter = ".csv file|*.csv*"; openFileDialog1.FilterIndex = 1; DialogResult result = openFileDialog1.ShowDialog(); if (result == DialogResult.Yes) { String file = openFileDialog1.FileName; btnNext.Enabled = true; try { string connect = "Data Source=BRIAN-PC\\SQLEXPRESS; Initial Catalog=PSAHoldings; user id =sa; Password=kagiso"; string table = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='t_original' AND xtype='U')" + "CREATE TABLE t_original (" + "empId varChar(10) NOT NULL PRIMARY KEY," + "paycode varChar(10) NOT NULL," + "amount int NOT NULL," + ")"; SqlConnection con = new SqlConnection(connect); con.Open(); SqlCommand createTable = new SqlCommand(table, con); createTable.ExecuteNonQuery(); String BulkInsert = "BULK INSERT t_original FROM_" + file + "_WITH (--FIRSTROW = 3," + "FIELDTERMINATOR = '|'," + "ROWTERMINATOR = '\\n')"; SqlCommand bulkCmd = new SqlCommand(BulkInsert, con); bulkCmd.ExecuteNonQuery(); } catch (SqlException ex) { MessageBox.Show(ex.ToString(), "Exception Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } }

<strong>I can't get anything into the table, My table is empty</strong>

<strong>t_original</strong> is the table I have to insert the <strong>students.csv</strong> file into and I have to skip the first to lines

Any help and advise will be appreciated

Thank you

Answer1:

You took the comment of @StuartLC too literal; as he said, the underscores in his comment represent spaces. The effect of the added underscores is that it now reads: BULK INSERT t_original FROM_{file} (notice lack of space between from and file). The same goes for the underscore in front of WITH.

Furthermore, the double dash (--) represent a comment, so everything after it is not executed.

If you debug, and get the actual value of BulkInsert, and execute that statement I would be very surprised if it executes, it will probably make the errors a whole lot easier to find.

Recommend

  • Insert a File to MySQL database
  • How to delete file and that file is used by another process using C#
  • Using C# to populate a DataGridView with CSV file, and update Access database with results
  • Select Area of Image
  • Where is the proper place to dispose an image
  • How to know if the file I'm opening is a .txt file or not in VB.net
  • OpenFileDialog.AutoUpgradeEnabled doesn't work under Vista or 7?
  • C# SaveFileDialog
  • Barcode Printing Sato LM408e c#
  • Pass value to Child Window form Parent Page
  • watershed function provided by EmguCv
  • Limit image size
  • “Value cannot be null or empty. Parameter name: contentPath” on a most unexpected line on postback w
  • C# OpenFileDialog Thread start but dialog not shown
  • UltraWinGrid Auto Refresh
  • Passing Data between VB.NET forms
  • Pass data to a existing form
  • C#, “Object reference not set to an instance of an object.” error
  • .net security exception
  • How to fallback to entirely different index page if user has javascript disable?
  • How to get google-services.json from Developer console?
  • How to check disabled jobs with Jenkins server?
  • How to protect an asp:textbox from user input?
  • how to upload multiple files in c# windows application
  • dc-js disable selecting slices on click for pie chart
  • Spring security and special characters
  • Can Jackson SerializationFeature be overridden per field or class?
  • Numpy divide by zero. Why?
  • php design question - will a Helper help here?
  • retrieve vertices with no linked edge in arangodb
  • AngularJs get employee from factory
  • IndexOutOfRangeException on multidimensional array despite using GetLength check
  • Linking SubReports Without LinkChild/LinkMaster
  • Authorize attributes not working in MVC 4
  • Bitwise OR returns boolean when one of operands is nil
  • sending mail using smtp is too slow
  • Busy indicator not showing up in wpf window [duplicate]
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • Python/Django TangoWithDjango Models and Databases