Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Read each line in Text file and update Table

Posted on 2007-11-29
5
Medium Priority
?
1,007 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

Implementing Azure Infrastructure Exam 70-533

This course is designed to familiarize and instruct students in the content that is covered by Microsoft Exam 70-533, Implementing Microsoft Azure Solutions. It focuses on all the November 2016 objective domain topics.

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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…
This video teaches users how to migrate an existing Wordpress website to a new domain.
The viewer will learn how to dynamically set the form action using jQuery.

722 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