June 24, 2010 at 12:49 pm
Howdy....
I have a pretty simple SQL statement that has been driving us crazy. Here it is:
SELECT * FROM OPENROWSET
('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;HDR=NO', 'SELECT * FROM MyFile.csv')
This CSV file has 33 rows and no header row. We have specified HDR=NO so the system will treat it as such. However, no matter what we do, the file query only select 32 rows. It treats the first row as a header no matter what. We have Ad Hoc Distributed Queries enabled. The only thing within the provider properties that is enabled Allow inprocess. We do use this syntax for other data loads, but I believe this is the only file that does NOT contain a header. Since we receive these files from an outside client, we haven't had much say (and hopefully this changes) over the exact format of the file. We tend to be more reactive than proactive.
I have tried using the Jet provider as well without success. I do think that was another issue, though. Has anyone out there seen this issue before? It's really mind-boggling. I searched Yahoo/Google with every term I could possibly think of, and I literally got one useful hit.
I don't know if MSDASQL is the same or similar to the Excel ODBC drivers in what it does under the covers, but this link has not been any help (although it is interesting).
Thanks....
Pete
June 24, 2010 at 2:19 pm
Never mind, I was able to figure this out, after looking at as many possible examples I could:
SELECT *
FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Text;Database=C:\;HDR=NO;','SELECT * FROM [test.csv]')
I wasn't able to locate it yet, but does anyone know of a link that gives details about the various parameters for the various providers? That is what really threw me for a loop on this.
thanks....
June 24, 2010 at 3:18 pm
Why not simply use SSIS? It would save you many headaches and errors.
Andrew SQLDBA
June 25, 2010 at 6:28 am
AndrewSQLDBA (6/24/2010)
Why not simply use SSIS? It would save you many headaches and errors.Andrew SQLDBA
Because that would be the easy thing to do......
Seriously, there are 2 things at play here. First, this is existing production code that had an issue identified and needed to be fixed immediately. Rewriting in SSIS, including all the testing, would have taken too long. Second, outside of me, no on in my company has any experience with SSIS. At least that's what they tell me.
If you look at my posts on this forum (there's only about 5 or 6, so it won't take you long), I posted something about setting standards and best practices here. One of the things I would love to implement is SSIS for all ETL, outside of our data warehouse (we use SAP-BO Data Integrator for that). The issues are no one knows SSIS and it's change. Plus, as someone pointed out in the thread, there's more than one way to accomplish the same task. I'm not 100% sure this one is worth the battle, especially given some things I know are coming up in the near future.
June 25, 2010 at 9:37 am
Sounds like you spend a very large part of your job "putting out fires" instead of making sure that a fire cannot be started. What a waste of time and money.
Stand up to the people that are above you, and tell them. That is why they hired you, for your expertise and knowledge. Not for you to be a machine and do the same thing as others have done incorrectly. Come up with new ideas that will be better than what they have currently.
Andrew SQLDBA
June 25, 2010 at 11:38 am
AndrewSQLDBA (6/25/2010)
Sounds like you spend a very large part of your job "putting out fires" instead of making sure that a fire cannot be started. What a waste of time and money.Stand up to the people that are above you, and tell them. That is why they hired you, for your expertise and knowledge. Not for you to be a machine and do the same thing as others have done incorrectly. Come up with new ideas that will be better than what they have currently.
Andrew SQLDBA
I don't spend a ton of time putting out fires, but our developers do. With regard to that and the ideas, policies, procedures, etc. that I want to put in place, the company is in a major re-structuring, particularly in the IS/IT area. Much of what I want to do will be done. The right people know what I want to do and want to do it. There are just some hurdles we need to get by before they can happen. Until then, we sort of have to deal with what we have. It's nasty, but someone has to do it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply