473,320 Members | 1,865 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Inconsistent behaviour while importing tables

I need to import data programatically from spreadsheet file into database table. There are 30 such tables. User can upload multiple files at a time. For a table(which does not have any referential integrity relation) the alogorithm is as follow:
For each file, do the following operations :
a) Read the excel file
b) Begin Transaction
c) Delete all records from the tables
d) INSERT all of the record from excel into the table
e) If some error, then rollback the transaction for that table and repeat the above steps for the next table
f) If no error, then commit transaction and repeat the above steps for the next table
g) Show message specifying number of successfully uploaded table and names of unsuccessful files


All files are getting uploaded except one(Let's say ABC). This ABC file has 50 thousands records. All other tables have less than 5 thousand record. So I guess the problem is the huge data in the ABC table. The bigger problem is that the behaviour is inconsistent everytime I try to upload. It takes 50 mins to complete the upload of ABC and after that sometime it uploads only 12 thousand records, sometime 45 thousand and sometime 30 thousand. This is making me more worried. If there is some problem in logic of algorithm or of there is some technical limitation then alteast it should give the same output everytime I try to upload.

Any pointer why a program can give different output for different run?

In a Lighter Vein, if this problem is not solved I would submit it as a random number generator. I understand that there is no perfect random number generator :-))
Sep 27 '07 #1
5 1205
QVeen72
1,445 Expert 1GB
Hi,

Do u run this Upload Program in a Multi-User Envi.
Say, 2 people are trying to upload same file at same time..?
If yes, then u will have to do some kind of Table-Locking..
And to what Backend DB u r exporting?
How did u open the Excel? Excel Object or using ADO?
What creiteria u use to check if it is End of File /Range in Excel..?
Can u paste the code here?

Regards
Veena
Sep 27 '07 #2
Hi,

Do u run this Upload Program in a Multi-User Envi.
Say, 2 people are trying to upload same file at same time..?
If yes, then u will have to do some kind of Table-Locking..
And to what Backend DB u r exporting?
How did u open the Excel? Excel Object or using ADO?
What creiteria u use to check if it is End of File /Range in Excel..?
Can u paste the code here?

Regards
Veena
It's a single user env.
Backend DB is SQL server.
I am opening Excel obejct.
I am using mObjWorkSheet.Cells(1, 1).CurrentRegion.ROWS.count
to find out the number of rows and then inserting each record one by one.

The actual code is very big so I am pasting the main method here
Private Sub UploadNormalTables()
'Objects to store the count of non-RI tables
Dim intTableCount As Integer
'Object to store the table name
Dim strTableName As String
'Object to store the filepath of the table
Dim strFilePath As String
'Object to store the number of rows present in the SQL table
Dim intNumOfRows As Long

'objects used as counters
Dim intCounter1 As Long
Dim intCounter2 As Long
'Object to store the error message
Dim strErrorMsg As String
'Flag to check whether the table is empty
Dim blnTableEmpty As Boolean
'Object to store the Table names not uploaded successfully
Dim strTablesNotUploaded As String
'Flag to check for error condition if any
Dim intIsError As Integer
'Flag to check if the column names of XLS and the database are in the
'same sequence
Dim blnColumnNamesInSeq As Boolean

intIsError = 0
strTablesNotUploaded = ""

For intCounter1 = 0 To mIntNormalTablesCount - 1
mIntErrorInLine = 0
On Error GoTo ErrorInXLS1
strTableName = ""
strTableName = mStrNormalTableList(intCounter1)
mStrQuery = ""
intNumOfRows = 0

'-------------------Logic to open the xls file selected by the user--------------
strFilePath = mStrNormalTablePath(intCounter1)
Set mObjExel = CreateObject("Excel.Application")
Set mObjWorkBook = mObjExel.Workbooks.Open _
(FileName:=strFilePath, ReadOnly:=True)
If intIsError <> 0 Then
intIsError = 0
GoTo GetNextWorkBook1
End If

Set mObjWorkSheet = mObjWorkBook.Worksheets(mObjWorkBook.ActiveSheet.N ame)
GoTo ProcessFurther1

ErrorInXLS1:
mIntNumOfTblNotUploaded = mIntNumOfTblNotUploaded + 1
mStrErrorMessage = mIntNumOfTblNotUploaded & ". " & _
mStrErrorMessage & mStrNormalTableList(intCounter1) & _
gStrErrMsg129 & vbCrLf & "Error in opening Excel File"
intIsError = 1
Resume Next

ProcessFurther1:
On Error GoTo ErrorInNormalTblProcessing
'Starting the Transaction to upload the table
gConn.BeginTrans

'To fetch the number of rows and columns in the Excel Sheet
mIntNumOfRows = mObjWorkSheet.Cells(1, 1).CurrentRegion.ROWS.count
mIntNumOfCols = mObjWorkSheet.Cells(1, 1).CurrentRegion.Columns.count

'If there is no data in the table
If mIntNumOfRows < 1 Then
strErrorMsg = strTableName & gStrErrMsg132
GoTo ErrorInNormalTblProcessing
End If

'function to fetch the data type for all the column from the DB n store
'it in rs4 recordset
Call FetchAllColumnDataType(strTableName)

blnColumnNamesInSeq = False
'Function to check if the column names of XLS and SQL table are in
'the same sequence or not
blnColumnNamesInSeq = CheckColumnNamesInXLS
If blnColumnNamesInSeq = False Then
strErrorMsg = strTableName & gStrErrMsg130
GoTo ErrorInNormalTblProcessing
End If

'function to delete all the rows from the table
Call TruncateTable(strTableName)

'function to insert the rows into SQL database
For intCounter2 = 0 To mIntNumOfRows - 2
Call InsertRowInDB(intCounter2 + 2, strTableName)
Next intCounter2

mIntTablesUploaded = mIntTablesUploaded + 1

'Commit Transaction in case of no error in processing
GoTo CommitTransaction1

ErrorInNormalTblProcessing:
'Rollback uploading of data in case of error
gConn.RollbackTrans
mIntNumOfTblNotUploaded = mIntNumOfTblNotUploaded + 1
'Store the error message in the string
If mStrErrorMessage <> "" Then
mStrErrorMessage = mStrErrorMessage & vbCrLf & vbCrLf & _
mIntNumOfTblNotUploaded & ". "
Else
mStrErrorMessage = mIntNumOfTblNotUploaded & ". "
End If

mStrErrorMessage = mStrErrorMessage & strTableName & _
gStrErrMsg129 & vbCrLf

If strErrorMsg = "" Then
If mIntErrorInLine <> 0 Then
mStrErrorMessage = mStrErrorMessage & " Error in row : " & _
mIntErrorInLine & " " & Err.Description
Else
mStrErrorMessage = mStrErrorMessage & Err.Description
End If
Else
mStrErrorMessage = mStrErrorMessage & strErrorMsg
End If
If Err.Number = 0 Then
GoTo GetNextWorkBook1
Else
Resume GetNextWorkBook1
End If

CommitTransaction1:
'If no error while uploading, then commit transaction
gConn.CommitTrans
GetNextWorkBook1:
'------------------Closing all the recordsets---------------------------------
If rs.State = 1 Then
rs.Close
End If
If rs2.State = 1 Then
rs2.Close
End If
If rs3.State = 1 Then
rs3.Close
End If
If rs4.State = 1 Then
rs4.Close
End If
If rs5.State = 1 Then
rs5.Close
End If
'------------------- Closing to the Excel Sheet----------------------------------
mObjExel.ActiveWorkbook.Close
mObjExel.Quit
Next intCounter1
End Sub
Sep 27 '07 #3
QVeen72
1,445 Expert 1GB
Hi,

Why u r calling another Procedure to Insert Rows.here....

For intCounter2 = 0 To mIntNumOfRows - 2
Call InsertRowInDB(intCounter2 + 2, strTableName)
Next intCounter2

U can open recset locally and add rows there itself..
If u r opening a Recordset everytime in procedure "InsertRowInDB" to add a row, Performance will be hugely affected.. try to do this part locally...

Regards
Veena
Sep 27 '07 #4
How would be it differnt whether I call another method or I do it locally? Actually we have to use strored procedure for all kind of database interaction.
Please find below the code for insert


Private Sub InsertRowInDB(ByVal rowIndex As Long, ByVal strTableName As String)
Dim strTempString As String
Dim intCounter1 As Long
Dim cmd5 As New ADODB.Command

'string used to prepare the insert query
strTempString = "("
For intCounter1 = 1 To mIntNumOfCols
'function to find the data type of the column and prepare
'the query string accordingly
Call FindDataTypeGlobal(strTempString, intCounter1, rowIndex)
Next intCounter1
strTempString = strTempString & ")"
'---------Logic to call the stored procedure to execute the insert query---------

cmd5.ActiveConnection = gConn
cmd5.CommandType = adCmdStoredProc
cmd5.CommandText = "Lydo_spc_InsertIntoTable"


cmd5.Parameters.Refresh
'To delete parameters, if any
For intCounter1 = 0 To cmd5.Parameters.count - 1
cmd5.Parameters.Delete (0)
Next intCounter1

mIntErrorInLine = rowIndex - 1
cmd5.Parameters.Append cmd5.CreateParameter _
("tblName6", adVarChar, adParamInput, 50, strTableName)
cmd5.Parameters.Append cmd5.CreateParameter _
("strQuery6", adVarChar, adParamInput, 500, strTempString)
Set rs2 = cmd5.Execute

Set cmd5.ActiveConnection = Nothing
Set cmd5 = Nothing

Exit Sub
End Sub

You r right. Performance is very bad. It takes 50 mins for 50 thousand records.
Sep 27 '07 #5
QVeen72
1,445 Expert 1GB
Hi,

In ur procedure, First u find the DataType, then Declare a
New Command and Then Do the Parameters.. and Then Call Stored Procedure..
Means, for 50000 Rows, U loop thru all these declaration,Checking Deleting and then Destroying the object.. It will naturally take long time..
U also have to check , whether Error Handling in Stored Procedure is done properly or not.. may be, that's the reason for Inconsistent Behaviour..

Why not Simply Declare RecordSet and use AddNew ..?
I'am sure, it will not take more than 10 mins for 50000 recs..

But cant help if u have to follow company's procedure..

REgards
Veena
Sep 27 '07 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Simon Wittber | last post by:
Greetings fellow Python People! I am using the latest debian-unstable Apache 1.3 and mod-python 2.7 with python 2.3.3 Yesterday I wrote a small mod-python script which returned the URL...
2
by: Matt | last post by:
I'm new to Java but experienced with PL/SQL. I've found what appears to be strange behaviour (a bug?) when attempting to create java stored objects using the UNIX version of Oracle SQL*PLUS...
2
by: Sara | last post by:
Hi - I've been reading the posts for a solution to my query, and realize that I should ask an "approch" question as well. We receive our production data from a third party, so my uers import...
5
by: dixie | last post by:
If I sent a user an empty database container - dB with no tables and I needed them to import their tables into it and one of their tables was a hidden table with the prefix Usys, is there any way...
3
by: codeman | last post by:
Hi all Lets say we have two tables: Customer: Customer_number : Decimal(15) Name : Char(30) Purchase: Purchase_number : Decimal(15)
12
by: JMO | last post by:
I can import a csv file with no problem. I can also add columns to the datagrid upon import. I want to be able to start importing at the 3rd row. This will pick up the headers necessary for the...
1
by: gollumullog | last post by:
Good Day, I have been having this issue for quite a long time, and have yet to find an easy/elegant solution. I am trying to create tables in an Access database. I have these tables as CSV...
4
by: Chloe C | last post by:
Hi I've got an Ingres database of some 200 tables which I need to import every night into SQL Server 2005 for use by Reporting Services. Most of the tables will come across unchanged (a few need...
3
by: rs387 | last post by:
Hi, I've found the following behaviour on importing a variable from a module somewhat odd. The behaviour is identical in Python 2.5 and 3.0b2. In summary, here's what happens. I have a...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.