Need to transfer many DBF files, while editing them at the same time

[MGM]

Alright, so here's my issue. I have a bunch of .dbf files, about 40 or so by summer's end, I'd say. These dbf files somewhat differ from each other as far as number of columns and data is concerned. I need to transfer all of these dbf files over to a SQL Server database so that I may write ASP pages for pulling statistics from them.

My problem is, this needs to be done in as easy a manner as possible, because we'll be getting more and more dbf files as we go along, and who knows what I'll be doing next summer when we'll probably get another 50 or so of these dbf files, so I can't upload them myself every time.

My idea is, I want to write an ASP page that scans a directory, checks for new dbf files (or changed dbf files, based on creation date), then reads all of the columns. If a specific column exists, the ASP page will do some extra stuff (see below) then upload to the SQL Server, if the column doesn't exist, it should just be a simple read and upload.

Now, if that specific column exists, what needs to be done is that each row must be checked, if data exists for that column, the entire row must be copied and re-inserted again with the data in that column replacing the data in another column. I hope that isn't too confusing.

Now here's my question, is this at all viable? Is it going to be quick and painless or will it take forever? Will I run into timeout issues?

Is ASP even the right language for me to be writing this in? I have experience with vb.net so I could write it in that if necessary.

I need this to be as easy and idiot-proof as possible, so that anyone can simply copy the dbf file to a specific folder, visit the index page of my stats web app for the files, and then simply wait for a short while as the new dbf file is processed and uploaded. Can this be done?

MGM out

[space1000]

I don't get the idea exactly. You could mount the dbf's into sql server and then analyze it all there with some sp's. Thats an easier way I think.

I didn't get the thing about rewriting the record. Please provide more information

[MGM]

I don't get the idea exactly. You could mount the dbf's into sql server and then analyze it all there with some sp's. Thats an easier way I think.

I didn't get the thing about rewriting the record. Please provide more information

Basically, I have a table in the dbf with 3 columns. There's Column ID, Column A, and Column B. Column A is supposed to be there, and has X data. Column B is NOT supposed to be there, and has Y data. Now, each DBF file may or may not have Column B. If Column B DOESN'T exist, it's a simple matter of copying over the entire table to the SQL Server.

If Column B DOES exist, then we go through the entire table row by row. If the row has data (ie: Y data) in Column B, then we copy the entire row, give it a new ID, and replace whatever data was under Column A with Y data (the data that was under Column B), then remove Column B entirely.

I hope that explains it well...

Would it be fast if I first went though and checked for Column B, if it exists, to do the row adding as stated above, and THEN upload to SQL Server? Would I have to upload row by row or is there a quicker way?

MGM out

[ck9663]

Basically, I have a table in the dbf with 3 columns. There's Column ID, Column A, and Column B. Column A is supposed to be there, and has X data. Column B is NOT supposed to be there, and has Y data. Now, each DBF file may or may not have Column B. If Column B DOESN'T exist, it's a simple matter of copying over the entire table to the SQL Server.

If Column B DOES exist, then we go through the entire table row by row. If the row has data (ie: Y data) in Column B, then we copy the entire row, give it a new ID, and replace whatever data was under Column A with Y data (the data that was under Column B), then remove Column B entirely.

I hope that explains it well...

Would it be fast if I first went though and checked for Column B, if it exists, to do the row adding as stated above, and THEN upload to SQL Server? Would I have to upload row by row or is there a quicker way?

MGM out

how do you assign new ID?

[space1000]

I should use a temporary table ##table_name and insert there the A column, then the B column, truncate the original table and insert the data from the ##table_name back into the original table. (You will have to write a cursor to enforce the numbering)

[MGM]

how do you assign new ID?

I should use a temporary table ##table_name and insert there the A column, then the B column, truncate the original table and insert the data from the ##table_name back into the original table. (You will have to write a cursor to enforce the numbering)

I apologize, due to a case of misinformation among my fellow co-workers, I seem to have recieved the wrong information on this subject. Let me start over.

We have 4 columns. We have ColID, ColNameA, ColNameB, and ColQuantityA. Now, most dbf files won't have the ColQuantityA, in which case the entire table simply needs to be uploaded to SQL Server.

Some DBF files WILL have the ColQuantityA column. If it exists, what I need to do is to copy over what's in ColNameA into ColNameB to signify that more than one exists. That is to say, ColNameA represents, for example, a restaurant in a city. If the ColQuantityA column exists and there's a number more than 1, then that means more than one restaurant exists in that city. So what I need to do, is to copy the exact name over from ColNameA into ColNameB to show that more than one exists. ColID won't be edited as I stated before, it will always stay the same for that row, and no new rows will be added either.

I hope I wrote that clearly enough. With all that in mind, what would be the best way (ie: fastest, easiest) to upload the DBF files to SQL Server taking into account that many row edits may be needed for some DBF files.

MGM out

[MGM]

Has anyone come up with a solution yet? I would really like to get back to work on this project but cannot do so while this problem still exists...

MGM out