I don't have a logical explanation on why it's behaving the way it is, but, well, it is working And even weirder, if I add a space after the double-dash, the query works fine as well! This made the weird behavior disappear. The strange behavior also disappears when a space is added between the double-dash and the INTO keyword.
Uh, computers can be so much fun, right? This explains some of the issues shown above. But in the wrong setting it can be quite cumbersome to get to work. I would recommend this method only for one-off quick imports, such as when you as a developer are given a bunch of data in a spreadsheet and need to get it into the database, one way or another.
I would not use it for an automated import process. For that we've got a more interesting alternative which I'll cover in an upcoming article. Have fun! Please click one before you go! Microsoft SQL Server. Ask a related question. Most Valuable Expert This award recognizes tech experts who passionately share their knowledge with the community and go the extra mile with helpful contributions.
Top Expert This award recognizes someone who has achieved high tech and professional accomplishments as an expert in a specific topic. Commented: I'll take a look. Yes, this process is repeated once every month, but I think I can handle the "dynamic" nature of the file and sheet names by building the SQL in Access and passing it to SQL Server in a pass-through query. ValentinoV BI Consultant.
Author Commented: I'm not that familiar with Access so I'm not sure what you mean with "pass-through" query. But dynamic SQL in the stored proc wouldn't really have to be complicated. Jones Bravo. Most complete and integrated solutions that enable you to leverage a wide range of information to operate more efficiently and make better decisions. Gain insight into every aspect of the business and discover new ways to strategize, plan, and optimize your business Mark Hurd Oracle …..
Luan Nguyen. Is there any way to do this using Openrowset with JET or something like that? Thanks Philippe. Saturday, February 23, AM. However, you cannot: Delete an entire record at once or you receive the following error message: Deleting data in a linked table is not supported by this ISAM. You can only delete a record by blanking out the contents of each individual field. Saturday, February 23, PM. Wednesday, February 27, PM. Sekaran 0. Manivannan, Somehow, that does not seem to be working for me.
I am getting the following error: Code Snippet. Yep same problem here. I tried this: Code Snippet. Jonathan, You are correct, this is not what I am after.
I need to keep the rest of the workbook intact. Hi Zulfi, I have two questions: 1 Do have to have Excel app loaded on the server? Thanks for the great post. I am trying to use the following think to make a row blank in excel Code Snippet. OK So I need only the dll.
No licence and Office install which would not be an option. After that it looks like it is pretty much like Automation. Hi Philippe Just Microsoft. Your help would be appreciated. Zulfi, I finally got it to work, but no like I need. I HAD to install Excel to get it to work, some dependencies were missing. Thursday, February 28, AM.
I ran depends. Hi Philipee , Thanks for pointing about the prodution issues, i have this concerns as well, I will try to raise this on SQL bits 2 and if i got any work around will let you know. Hi Manivannan, As I investigated select, insert, and update operations could be performed over excel spread sheet from sql taks or sql server through linked servers.
Apparently, none of the above would remove the rows from excel expect automation and the procduction issues with them is still a stumbling block. For now, I keep a strict minimum install of Excel on the test server. This application will probably never be deployed to production because of this. We are effectively reading a filename different each time and then using that filename opening an Excel table with different data each time.
Very dynamic - so a linked server won't work. SSIS would be the way to go for this. Easiest way is to use Cozyroc add-on which can address dynamic files and columns fairly easily.
I custom-built one that holds file and field information in database tables without Cozyroc but it's pretty complex and include C scripting. I've found python to be pretty easy and you may be able to utilize that.
Keep in mind bulk import is the fastest way to ingest, which is what SSIS mostly uses fast load. Good luck! Thanks so much for the advice - It's not what I wanted to hear, but I still appreciate the help! Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Tried several security options folder permissions for the OS User running SQL found on other posts but that hasn't helped. Hi Diana, thank you very much for these tips. I'm attempting this on a test db on my local machine before trying it on the server.
This is a very nice and informative tip! Stepping through each layer of frustration and whacking it with a great big smart-sword was a good way to present the information. I am bookmarking this one for sure! I do recall someone found a way to install the 64bit driver, however, it is not recommended for a production server. Sorry, I don't have the link, but I'm sure you can find it, as well as other import suggestions, with a web search.
If the machine has 32bit Office installed which is the MS recommended version , then the 64bit ACE driver can't be installed. I have nothing ggood to say about importing data from Microsoft Excel as Microsoft in their infinite wisdom decides for the user what the format of the data is and often gets it wrong. That data type decision is made base on the first few rows of data, yes it can be change to sample more rows but it does not solve the problem.
One can include a quote in the data to make numerical data appear as text because that is the way you the user wants it, not the best solution. Myself I wrote a C program that adds a row of 'A' to the spreadsheet after the header row which causes the translation on import to correctly identify the data as text, then I remove the row of 'A' from the table where I imported the data, also not the best solution but it works and is simple.
Microsoft needs to bring back the ability to create a data template that defines the correct data types for a file import. Excellent article!! Wish I had this a few years ago when I had to set it up.
0コメント