ysk9818
asked on
Import Text File into SQL Server
I tried to import a text file into SQL Server Database. However, the applicaiton crashed when tried to insert 33,000 row into SQL server. The following Code. Please Help
strConnTuneup = "Provider=SQLOLEDB.1;Integ
Set connTuneUp = New ADODB.Connection
connTuneUp.Open strConnTuneup
Set rsTestingTable = New ADODB.Recordset
rsTestingTable.Open "Testingtable", connTuneUp, adOpenDynamic, adLockOptimistic, adCmdTable
Open "c:\PosImpt\AllNewIncoming
Dim testing As String
Do While Not EOF(1)
Line Input #1, testing
rsTestingTable.AddNew
If testing = "" Then
testing = "Empty"
End If
rsTestingTable.Fields(0) = testing
rsTestingTable.Update
Loop
Close #1
strConnTuneup = "Provider=SQLOLEDB.1;Integ
Set connTuneUp = New ADODB.Connection
connTuneUp.Open strConnTuneup
Set rsTestingTable = New ADODB.Recordset
rsTestingTable.Open "Testingtable", connTuneUp, adOpenDynamic, adLockOptimistic, adCmdTable
Open "c:\PosImpt\AllNewIncoming
Dim testing As String
Do While Not EOF(1)
Line Input #1, testing
rsTestingTable.AddNew
If testing = "" Then
testing = "Empty"
End If
rsTestingTable.Fields(0) = testing
rsTestingTable.Update
Loop
Close #1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
. I run the application over the network. and the app. crashed.
Any error message?
ASKER
I used to following code suggested by you.
connTuneUp.Execute "Insert into testingtable (field1) values (' " & testing & "')", , adCmdText
it give me no value for required value error msg
connTuneUp.Execute "Insert into testingtable (field1) values (' " & testing & "')", , adCmdText
it give me no value for required value error msg
Your table have other fields that can't be left empty.
ASKER
ok..i got the no value error fixed. and i run the application locally in the SQL server, and it able to insert 36979 rows within a reasonable time. But when i tried to run it from a network. it just crashed.
any idea
any idea
<But when i tried to run it from a network. it just crashed> What is the error message?
It can be a lot of different reason! Is this on the same SQL server?
If not the same server, it can be indexes, memory, TempDB, ...
If the problem is not the server, it can be network problems, try to have the input files at same location as the application is running.
It can be a lot of different reason! Is this on the same SQL server?
If not the same server, it can be indexes, memory, TempDB, ...
If the problem is not the server, it can be network problems, try to have the input files at same location as the application is running.
have u ever think of using bulk insert instead of row by row? it'll be faster & it's the appropriate way to handle bulk records.
U can refer to MSDN bulk insert & get more info on how to solve it. I'm sure it's the correct solution for your case.
just prepare a format (fmt) file, a data (txt) file - extracted from original text file. & u can use the bulk insert features offered by MSSQL.
I guess your problem occured coz u're inserting row by row into your MSSQL which it takes up the network bandwitdh coz u're keeping on the connection to the database server.
good luck.
U can refer to MSDN bulk insert & get more info on how to solve it. I'm sure it's the correct solution for your case.
just prepare a format (fmt) file, a data (txt) file - extracted from original text file. & u can use the bulk insert features offered by MSSQL.
I guess your problem occured coz u're inserting row by row into your MSSQL which it takes up the network bandwitdh coz u're keeping on the connection to the database server.
good luck.
peep...
Everything under control?
ASKER
Thank emoreau, i solve the problem by creating a store procedure at MSSQL server and used the execute command to pass the parameter to the stored procedured...thank all for the helps..
Will you accept one of the comment?
Hi ysk9818,
You've requested to delete this question, but its status remains as 'Pending Delete' because one or more comments have been added. Normally, the only way to fully delete such a Question is to post a message to Community Support and ask for assistance.
EE is making a one-time database sweep to purge the Pending Delete Questions automatically. During this sweep:
ysk9818 -- To allow the deletion to proceed: Do nothing.
EXPERTS -- Please DON'T POST a comment except to contest this deletion.
In the future, please refer to https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp#8 for instruction on deleting questions.
DanRollins -- EE database cleanup volunteer
You've requested to delete this question, but its status remains as 'Pending Delete' because one or more comments have been added. Normally, the only way to fully delete such a Question is to post a message to Community Support and ask for assistance.
EE is making a one-time database sweep to purge the Pending Delete Questions automatically. During this sweep:
ysk9818 -- To allow the deletion to proceed: Do nothing.
EXPERTS -- Please DON'T POST a comment except to contest this deletion.
In the future, please refer to https://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp#8 for instruction on deleting questions.
DanRollins -- EE database cleanup volunteer
I must be awarded on this!
in that case, I shall share a pie of it. :>
Lacking timely response from ysk9818 or contributing experts, but seeing that there is useful information here, I recommend:
Refund points and save as a 0-pt PAQ.
*** It certainly looks to me as if ysk9818 did not use any commenting expert's help in solving this problem.
DanRollins -- EE database cleanup volunteer
Refund points and save as a 0-pt PAQ.
*** It certainly looks to me as if ysk9818 did not use any commenting expert's help in solving this problem.
DanRollins -- EE database cleanup volunteer
This user appears to have a pattern of asking questions, getting responses, and then having the questions deleted, then asking more questions, which has the effect of creating a negative question point balance.
This deletion will not be allowed, and the user has been reported to Admin.
Force-accepted, and points for ianouii are at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20315288
Netminder
CS Moderator
This deletion will not be allowed, and the user has been reported to Admin.
Force-accepted, and points for ianouii are at https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=20315288
Netminder
CS Moderator
ASKER