Best way of updating a database from a text-file?

Results 1 to 2 of 2

Thread: Best way of updating a database from a text-file?

  1. #1
    Join Date
    Dec 1969

    Default Best way of updating a database from a text-file?

    I&#039;m creating an ASP.NET app that regularly will import data from several text-files (usually tab-separated) into an Access database. The challenge here is to find the best way of updating the db efficiently. <BR><BR>The files I&#039;m importing always contain all the data (exported from a third-party&#039;s product db), so when I&#039;m importing a file for the n&#039;th time my db will already contain most of the info. But there might be slight changes, such as a change in the product&#039;s price or delivery date, or even just a fixed typo. There might be completely new products, deleted products, or perhaps no changes whatsoever. <BR><BR>The app will go throught several files (for now probably about 6-7, but eventually it might be several hundred), all containing thousands of products. Comparing every single product read from the file with the info in the db would make the routine very slow, so I don&#039;t think that&#039;s an option. <BR><BR>What I&#039;m considering is, for each file, simply deleting everything in the database and inserting everything from the file as new lines. I imagine this would be the quickest, but is it the best? <BR><BR>Any recommendations? <BR><BR><BR>Thanks in advance, <BR><BR>-Frode <BR>

  2. #2
    Join Date
    Dec 1969

    Default Makes some sense...

    You read the file into an array of lines. You run through the array of lines converting each line to an array of fields. In the process, you gather up all the PK values. You generate on delete for all of those values. Then you run through the array again doing inserts.<BR><BR>BUT...<BR><BR>But there&#039;s probably a better way.<BR><BR>If you can treat the text file *as* a database (and you can, using the JET text-file driver!), then you could do it in just two SQL statements.<BR><BR>Hmmm...I&#039;d probably open a connection to the text file and treat the MDB file as the "external" database.<BR><BR>&#060;%<BR>Set conn = Server.CreateObject("ADODB.Connection")<BR>conn.Op en "...jet, text driver, see comments..."<BR><BR>SQL1 = "DELETE FROM table IN &#039;c:pathyourdb.mdb&#039; WHERE pk IN (&#039; _<BR> & " SELECT pk FROM textfile.txt ) "<BR>conn.Execute SQL1<BR><BR>SQL2 = "INSERT INTO table IN &#039;c:pathyourdb.mdb&#039; " _<BR> & " SELECT pk, fld2, fld3, fld4 FROM textfile.txt"<BR>conn.Execute SQL2<BR>%&#062;<BR><BR>Done.<BR><BR>Now, the trick will be making that connection to the text file work right.<BR><BR><B R><BR>Scroll down to "You can also open a Text file using the JET OLE DB Provider".<BR><BR>The problem is that even the Knowledge Base link only shows how to use a *comma* delimited text file. I have no idea if it would be smart enough to adapt to a tab-delimited file. I *suspect* that if you could find the docs for it, you&#039;d discover that in addition to "FMT=DELIMITED" you can also specify something like "DELIMITER=??". But I certainly don&#039;t know that. You&#039;ll just have to experiment.<BR><BR>There *IS* an alternative here.<BR><BR>It&#039;s certainly true that Access, itself, is smart enough to be able to import tab-delimited files. So maybe the solution is to import each such file into Access as a *separate* table and then use the queries noted above, except now you don&#039;t have to use the "IN" clause to specify an external DB since both tables will be in the same DB.<BR><BR>I think this is the superior way, but you&#039;ll have to figure out if you can automate the import of those tab-delimited files, instead of having to do one at a time manually.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts