-
Nov 2nd, 2014, 04:35 AM
#1
Thread Starter
Hyperactive Member
How to upload datatable into SQL row by row and field by field ?
I used sqlbulcopy to upload datatable to sql but got an error said that "string convert to decimal error".
Since table has about 300 fields and the error don't indicate where the error is.
How to upload datatable into SQL row by row and field by field so that I could check the error?
Or, use another way instead of datatable?
-
Nov 2nd, 2014, 04:37 AM
#2
Re: How to upload datatable into SQL row by row and field by field ?
Search up on Insert statements for SQL. It will help a lot.
-
Nov 2nd, 2014, 06:41 AM
#3
Re: How to upload datatable into SQL row by row and field by field ?
Hello,
* Create a connection to the database
* Create a command
* Create the SQL INSERT with parameters
* Create parameters for the command
* Open connection
* In a for/next or for/each of the DataTable, for each iteration set the parameter values from the params created above.
* ExecuteNonQuery and check the results. You may get an error as before, let it throw an exception,
examine the row to figure out which field is the issue or wrap the ExecuteNonQuery in a Try/Catch and place a break point there
then when hit examine the row data
Simple creation of a parameter
SqlClient.SqlParameter("@inputone", SqlDbType.Int)
Also, please consider your table design, 300 fields is excessive. Usually in most cases multiple tables with relations setup in the database or in your SQL will be best.
-
Nov 2nd, 2014, 11:02 AM
#4
Thread Starter
Hyperactive Member
Does sqlbulkcopy class have index to indicate where is error existing?
I used code below to upload datatable (from CSV) using sqlbulkcopy but got an error said "can't conversion string to decimal ..."
There are about 2 million row in which there are 290 columns to upload.
Does sqlbulkcopy class have index to indicate where is error existing?
Dim bc As SqlBulkCopy = New SqlBulkCopy(dbConn, SqlBulkCopyOptions.TableLock, Nothing)
bc.DestinationTableName = "test"
dbConn.Open()
bc.WriteToServer(dt) // error appended here
-
Nov 2nd, 2014, 12:52 PM
#5
Re: Does sqlbulkcopy class have index to indicate where is error existing?
If you are filling a DataTable, then its schema should match that of the destination Table.
You can load the schema into an empty DataTable like this:
Code:
Dim dt As New DataTable
Dim conn As New SqlClient.SqlConnection("Your ConnectionString")
Dim da As New SqlClient.SqlDataAdapter("Select * From TableName", conn)
da.FillSchema(dt, SchemaType.Source)
Now you can load the DataTable and it will throw an error when you add records to it if the data types are wrong. The DataRow can do implicit conversions from a String to the proper data type if you load it something like this:
Code:
Dim r As DataRow = dt.NewRow
' simulate a line read from the CSV file
' assume the datatypes are: Int32, Int32, String, String, DateTime
Dim line As String = "1,23,some string,another string,1/31/2014"
Dim parsedCSVLine As String() = line.Split(","c)
r.ItemArray = parsedCSVLine
dt.Rows.Add(r)
A more proper technique would be to covert each piece from the split string using something like this pattern as you will know where the conversion failed and can take appropriate action (skip record, log it, etc.).
Code:
Dim r As DataRow = dt.NewRow
Dim tmp As Int32
If Int32.TryParse(parsedCSVLine(0), tmp) Then
r(0) = tmp
Else
' fail
End If
If Int32.TryParse(parsedCSVLine(1), tmp) Then
r(1) = tmp
Else
' fail
End If
...
-
Nov 2nd, 2014, 05:16 PM
#6
Re: Does sqlbulkcopy class have index to indicate where is error existing?
When I'm doing bulk copies, here's how I do it:
1) Create a table in the database with all of the fields from the source file. Depending on how dirty the data is, I'll type what I can (numbers, dates, etc) otherwise, everything is a raw string
2) create a datatable that mirrors the table from step 1
3) Suck the data in and stuff it into the data table
4) Feed the data table to the SQLBulkCopy object
5) Once all of the data is in the table, I run validations against it - as part of the table def, I'll also include a field on the end that lets me know if it's bad or not
6) After scrubbing the data and validating it, I then select any records that pass and insert them into their final table(s).
7) Optionally I'll either flush out all the data, or at least flush out the ones that passed validation and were moved.
But the key is that your datatable you send to the BulkCopy HAS TO MATCH FIELD FOR FIELD IN ORDER the fields of the destination table.
-tg
-
Nov 2nd, 2014, 05:23 PM
#7
Re: How to upload datatable into SQL row by row and field by field ?
Step 1 - Don't post duplicate threads - http://www.vbforums.com/showthread.p...error-existing all it does is create confusion and fractures the responses you get.
-tg
-
Nov 2nd, 2014, 05:24 PM
#8
Re: Does sqlbulkcopy class have index to indicate where is error existing?
-
Nov 3rd, 2014, 03:03 AM
#9
Re: How to upload datatable into SQL row by row and field by field ?
I've merged the two threads. Try not to create duplicates. It's better to keep a single thread going so all the information can be in one place.
Cheers
FD
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|