Read each line in Text file and update Table

Posted on 2007-11-29
Medium Priority
Last Modified: 2013-11-19
Hi Experts
I need a good code that reads each line in a given TXT file and then update a table in the database
Anyone can help getting through this!
Question by:humer2000
  • 3
LVL 13

Expert Comment

ID: 20373181
something like this -

dim Conn
dim fs
dim f

set Conn = Server.createobject("ADODB.Connection")
Conn.Open "<connection string here>"

Set fs=Server.CreateObject("Scripting.FileSystemObject")
Set f=fs.OpenTextFile(Server.MapPath("testread.txt"), 1)

do while f.AtEndOfStream = false
    Conn.Execute "insert into <table> values(" & f.ReadLine & ")"

Set f=Nothing
Set fs=Nothing

set conn = nothing


LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20373267
This is a vbscript that will do the same

Set MyConn = CreateObject("ADODB.Connection")
      MyConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dss.mdb"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.GetFile("C:\12345.txt")
If objFile.Size > 0 Then
    Set objReadFile = objFSO.OpenTextFile("C:\12345.txt", 1)

Do While objReadFile.AtEndOfStream = False
strContents= objReadFile.ReadLine
if strcontents <>"" then
      strContents = Replace(strContents, "'", "")
               MyConn.Execute "insert into table1 values ('" & strcontents &"')"
end if
    Wscript.Echo "The file is empty."
End If

Accepted Solution

humer2000 earned 0 total points
ID: 20373798
well while waiting for a response, i discovered a code on EE forum. it looks ok but i have an error.

Sub readAndUpdate(file)
Dim dbtable
if file=StorePathFileST then dbtable="products"
if file=StorePathFileEX then dbtable="orders"
Response.write "file : " & file & "<br>" & vbcrlf
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOpenedFile = objFSO.OpenTextFile(file, ForReading, True)
Do While objOpenedFile.AtEndOfStream <> True
      lines = lines & objOpenedFile.ReadLine
      response.write "lines = " & lines & "<br>"

Set regEx = New RegExp
regEx.Global = True
regEx.IgnoreCase = True
regEx.MultiLine = True
regEx.Pattern = "\""(.*?)L(.*?)\"",\"".*?\"",\"".*?(\d+.tif)\"""
Set Matches = regEx.Execute(lines)
Response.write "Matches : " & Matches & "<br>"

' insert your db object/connection string code here
Dim Numero,      Code_Article, Designation_Arti, Stock, isql
For Each Match in Matches
      Numero                         = Match.SubMatches(0)
      Code_Article             = Match.SubMatches(1)
      Designation_Arti       = Match.SubMatches(2)
      Stock                          = Match.SubMatches(3)
      Response.write "Numero : "                         & Numero & "<br>" & vbcrlf
      Response.write "Code_Article : "             & Code_Article & "<br>" & vbcrlf
      Response.write "Designation_Arti : "       & Designation_Arti & "<br>" & vbcrlf
      Response.write "Stock : "                         & Stock & "<br>" & vbcrlf
      ' isql = "INSERT INTO table (image,book,section,lock,processed) VALUES ('" & image & "','" & book & "','" & section & "','0','0')"
      isql = "update products set cstock="& Stock &" where ccode='"& Code_Article &"')"
      response.write "<pre>" & isql & "<br>"
end sub


the error i have is in line :
Set Matches = regEx.Execute(lines)

Microsoft VBScript runtime error '800a01c2'

Wrong number of arguments or invalid property assignment

/shopa_ImportAstelem.asp, line 135
LVL 13

Expert Comment

ID: 20374071
Odds are, there is some kind of buffer limit, probably 64kb, that you can write to a text file at any given time. If that is the case, then the simple solution to your problem is writing your data is smaller chunks, rather than all at once. You can split your data into fixed-sized chunks using a simple regular expression, then you can write your data chunk by chunk as you loop through the matches collection.
LVL 13

Expert Comment

ID: 20374081
Please ignore last post - I was helping with someone write out to a file using the regEx function

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Custom Android App Development is the need of an hour for several businesses. If you go through the right firm for Android Application Development, it can make a huge difference to the ways in which customers interact with your business.
Magento is the best technology for eCommerce start-ups as it offers the technical expertise and visual appeal to create a store that pulls sales and earns high ROI (Return on investment).
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question