Solved

Read each line in Text file and update Table

Posted on 2007-11-29
5
1,001 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
  • 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Developer portfolios can be a bit of an enigma—how do you present yourself to employers without burying them in lines of code?  A modern portfolio is more than just work samples, it’s also a statement of how you work.
Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.

810 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