I am working on importing a .csv file, but having problems with the csv file data fields and table fields. The csv file fields DO NOT match exactly with my table .....is there a workaround ??? How can i ensure that the right values are placed in the right columns ... How can i create a "format file" so that the all the table fields get the right values !!
Thank you MAK for your reply .
Well, i am not using DTS . I am creating a web based Aplication where i need to Give a Import Button on one of the Screens which will take the file name as an input and should be able to import that CSV file into my database table.
Another issue that i am thinking would be the csv file location, as i the file supplied by the user will always be existing on his hard disk, not on the server and utilities like BULK INSERT look for the file on the server .. Pls help if you have answers to my questions.
Also, pls tell me if there is a way to create this "format file" manually so that i am implicitly attach the rite fields to right columns .
DTS package does all that , but i cannot use the DTS package at runtime on a website !!
1. Create a DTS package which can accept different source files (with same format) using GLobal varible and dynamic parameter.
2. When the User click the button for import ftp the file (internally) to your web box (rename the file to a unique filename) and then run a DTS package to import it (passing the new filename as the parameter prefic it with your webserver path)
Yes you can execute a DTS package at run time from a website.
Thanks for all your help , but let me tell you that i am totally new to DTS package etc and would like to know a little bit more on how to do this ...Its been a while since i am trying to dig in info to implement this.
Can you suggest some sample code for the same?? I have been reading your articles about the DTS package. Somehow, i feel there is something more to it which i need to implement that in my application.
Though i have a package ready no w, but another question that i want to ask you is - how do i make the (csv)file available at the server ?? Or is it possible for the SQL Server to read the file from clients' Hard disk as i will otherwise have to copy the file from client's HD to the SQL SERVER !!!
Also, while exporting the data from the table to a CSV file , ho wcan i store the file on the client's Hard Disk (as per the destination path specified by the client) !!
1. use UNC Path if you are executing the DTS package on the server. Make sure the login used in SQL Server Agent service (if you are scheduling the job in SQL Server) has access to the UNC path.
I get syntax errors . Also, i want to know , how can i make the web client use this as the file(.csv) needs to be present on the SQL SERVER in order for it to run the DTS package. Pls help !!