|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
January 25th, 2005, 01:19 PM
|
||||
|
||||
|
Job: Need estimates and brief description
Give me an estimate on how long you think it would take to do the following, with brief explanation of how you would do it.
1) Import 5 text files into staging tables in a SQL Server database. 2) Compare data in staging tables to 5 other corresponding tables. 3) If record exists in its corresponding table, update the record, If not add a new record. I get updates every month in txt format, some comma delimited some tab delimited, that I need an application to import these files into my staging tables and then update any existing records or add any new records.
|
|
#2
January 25th, 2005, 01:59 PM
|
||||
|
||||
|
(thought I'd never live to see you ask something...
 )1. I would make function that gets file path and import it into the database. this way it does not matter if you have 5 files or 1000 files. that function would: a) use FSO to read file b) decide if it's comma or tab delimeted by analyzing the text. c) iterate the file line by line and import each line as new record such function is rather simple task, given already-working connection stuff of course, no more than 3 hours to make it. 2. again, function that would get as arguments name of two tables and would make the needed updates in such algorithm: a) open two recordsets, one for each table. b) create Dictionary object, let's call it objDic. (  )c) iterate through the staging records, for each record insert such items into the dictionary: Code:
objDic(rs("id")&"_insert")="Insert Into TableName (field1, field2, ...) Values ('"&rs("field1")&"', '""&rs("field2")&"', ...)"
objDic(rs("id")&"_update")="Update TableName Set field1='"&rs("field1")&"', field2='""&rs("field2")&"', ..."
d) iterate through the second recordset and for each record have such code: Code:
If objDic.Exists(rs2("id")&"_insert") Then
strSQL=objDic(rs2("id")&"_update")
objConn.Execute(strSQL)
objDic.Remove(rs2("id")&"_insert")
objDic.Remove(rs2("id")&"_update")
End If
this would update the table if the record exist, and remove the item from dictionary. e) the items left in the dictionary are the records that does not exist in the other table... iterate throgh those items and using objDic(key&"_insert") insert new records to the table. time for this is also about 3 hours if no problems arise, but such code is prone to bugs and problems - 2 more hours may be needed. total time would then be 6-8 hours. not so much...  Last edited by Shadow Wizard : January 25th, 2005 at 02:03 PM. Reason: added code tags around code.
|
|
#4
January 26th, 2005, 02:30 AM
|
||||
|
||||
|
lol
I just love challanges...
|
|
#6
March 2nd, 2005, 05:07 AM
|
|||
|
|||
|
hello
Yes
I am interested, add me in MSN : praf22@hotmail.com AIM : joypat123 Quote:
|
|
#7
March 2nd, 2005, 08:56 AM
|
||||
|
||||
|
Quote:
Read the post thoroughly before replying please. Quote:
Send me an email or a PM with the information above.
|
|
#9
March 17th, 2005, 12:52 AM
|
|||
|
|||
|
Export Text Files
Dear Sir,
I am interested for doing this job..... as i have exeprience in bulk insert as well as the exporting text files into sql server DB. i done this type of job in many projects..... If u r interested for doing this by me mail me on svgharmode@gmail.com Thanking You Saenjeev
|
|
#10
March 17th, 2005, 08:30 AM
|
||||
|
||||
|
Please read my initial post thoroughly before responding
Quote:
You can provide this information to me either by sending me an email, a private message or just posting it in this thread. Bump... Still looking!
|
|
#11
March 17th, 2005, 11:22 PM
|
|||
|
|||
|
I'd probably use VB, and there isn't enough information to come up with an accurate time estimate.
If the text files were on the computer where the program runs, and there are no difficulties connecting to sql server, and if the format of the text files is consistent, it shouldn't take more than a few hours to get a working prototype going on the one development computer. Unless there is some performance issue, the code would create a staging table for each text file, use the FSO to open the text file, read a line, I'd do the comparison against the production table here and flag the staging table entry as needing update or insert (or just skip the staging tables if you can). After all the text files are processed, then process each staging table and code inserts or updates as required for to the production tables. This is just off the top without a lot of thought though
__________________
====== Doug G ====== I didn't attend the funeral, but I sent a nice letter saying I approved of it. --Mark Twain
|
|
#12
March 18th, 2005, 08:19 AM
|
||||
|
||||
|
Okay, more details then...
1 text file is in Tab delimited format, the other 4 are in comma delimited format. The staging tables range from having 28 - 185 columns. After updating the "Production" tables, you also have to update a "Search" table, which indexes the "Important" search information for each record. Should be done in .NET to allow any .NET developer to maintain or extend it in the future.
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
