6/13/2023 0 Comments Syncovery filename variables![]() ![]() We will use xp_DirTree to accomplish this. The first thing we will need to do is scan that directory for all the files that are living in it. You know the directory where the files are going to be living. This example will utilize various temp tables to help achieve everything covered in the above scenario. If it does, you will not want to duplicate data, so you will just exit out and move to the next file.īeing in the world of custom programming, I rely a lot on temp tables throughout the day. You will also need to check to see if the file already has a corresponding table, and if does, you will need to see if the data that you are importing already exists in the table. The scenario that we will be using is as follows: As a developer, you are asked to read the CSV files from a directory, and then import those files into the database. That is when I decided that I could incorporate the question into my presentation, enhance it a little, and do it all Dynamically.īefore we go any further, for this to work you will have to know the columns that are in the CSV file that you will be loading, and the type of data each column contains. That was a straight forward question that had a straight forward answer, however, imagine if you didn’t know the name of the file you were going to import, or if there were multiple files in the directory, how would you handle loading those files. A few days later he came back and said that everything was working great. I wrote some sample code, checked it twice and then emailed it off to him. I took the individuals name and contact information and told him I would research it and get back with him.Īfter digging through different Microsoft articles, I found a way to load a file from disk using BULK INSERT and creating a simple Format file in XML. When I was asked this question, I did not know the answer to it. ![]() A lot of times, companies will lock down access to xp_CmdShell, so as a developer, you must find an alternate way to achieve your goal. ![]() How to load a CSV File from a directory without using xp_CmdShell. Recently, I was asked a question that I did not know the answer to. If the question is unique, and if I am able to adapt it to my talk over Dynamic SQL, I will add it to my presentation the next time that I speak. Sometimes I know the answer right away, but there are times where I do not know the answer and I have to go home and do research in order to answer it. Speaking at SQL Saturdays, I always get excited when I get asked a question that has never been asked. ![]()
0 Comments
Leave a Reply. |