Solved

Read each line in Text file and update Table

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
original content 7 123
ASP get fieds value 2 40
Exchange OWA website Redirection 7 81
SP inserts data with order number, then push the rest one order up 11 45
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
Australian government abolished Visa 457 earlier this April and this article describes how this decision might affect Australian IT scene and IT experts.
This video teaches users how to migrate an existing Wordpress website to a new domain.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

752 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