Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cmcilvoy
Starting Member
8 Posts |
 Posted - 2002-04-13 : 14:09:30
|
| Scenario:7-10,000 records in a .txt fileSQL Server 2000 (shared server space)Need to import file to SQL weekly and generate XML & CSV Extracts on a weekly basis. Also need to clean the records. Let me know what combination of ASP, Stored Procedure, etc. you think are the best for optimal performance and least amount of work. chris mcilvoy |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-13 : 14:58:48
|
| You can use either bcp, BULK INSERT, or DTS to import the CSV file into your SQL Server database. These actions can be put into a scheduled job to run once a week. All of these bolded items are described further in Books Online.bcp and BULK INSERT would require that the CSV import file to be placed on the SQL Server's local hard drive(s), or on a UNC path that it can access. If the file is to be uploaded, then you'll need to use some kind of ASP code to do so. DTS could work with a file on a client's hard drive without uploading, but that client computer would need to have DTS installed. After that, everything else can be accomplished using SQL Server stored procedures.To clean the data, I would recommend importing it into a staging table. This table has no constraints, indexes, or anything else that might prevent data from being imported due to validation issues. Once in the staging table, you can use regular SQL statements to SELECT valid rows, DELETE invalid rows, or UPDATE rows so that the data is valid or transformed into the proper format. Once the data is completely clean, you can INSERT it into the regular table.CSV extracts can be done using DTS or bcp, again as part of a job. You could combine the import, cleansing, and export routines into one job, assuming that the cleanup can be done entirely automatically.XML exports can also be added to this job, or done separately. If you need an XML file, you can use the techniques listed here to generate one from the job/stored procedure:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336Edited by - robvolk on 04/13/2002 15:09:23 |
 |
|
|
|
|
|
|
|
Import CSV File Weekly; Best Procedure