Solved

Read each line in Text file and update Table

Posted on 2007-11-29
5
1,005 Views
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!
thx
0
Comment
Question by:humer2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 13

Expert Comment

by:samic400
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 & ")"
loop

f.Close
Set f=Nothing
Set fs=Nothing

conn.Close
set conn = nothing

%>

0
 
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
loop
    objReadFile.Close
            MyConn.Close
Else
    Wscript.Echo "The file is empty."
End If
0
 
LVL 1

Accepted Solution

by:
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>"
Loop

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>"
        xdbc.execute(isql)
Next
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
0
 
LVL 13

Expert Comment

by:samic400
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.
0
 
LVL 13

Expert Comment

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

Featured Post

Congratulations! You’re Certified – Now What?

Starting a new career can be overwhelming. Becoming certified in your field of expertise is a great start, but where do you go from here?  Here are some tips to help you on your career journey.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

FAQ pages provide a simple way for you to supply and for customers to find answers to the most common questions about your company. Here are six reasons why your company website should have a FAQ page
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

635 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