Validating meta data of xls file

Question

Validating meta data of xls file

Viewing 14 posts - 46 through 58 (of 58 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Phil Parkin wrote:

Phil... where do you hail from? I ask because I'm giving the "Automating "Excel Hell"" presentation at the Cleveland SQL Saturday on the 1st of February 2020 and would love to meet you in person. It would also be my pleasure to meet anyone else reading this thread whether or not they've posted on it.
I'd like that. Currently working in Grand Cayman (it's a hard life), but I'll see whether my boss is open to it.

Just had a check of travel logistics ... the journey is a long one, so this is not going to work out, I'm afraid.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

Phil Parkin wrote:

Phil Parkin wrote:
Phil... where do you hail from? I ask because I'm giving the "Automating "Excel Hell"" presentation at the Cleveland SQL Saturday on the 1st of February 2020 and would love to meet you in person. It would also be my pleasure to meet anyone else reading this thread whether or not they've posted on it.
I'd like that. Currently working in Grand Cayman (it's a hard life), but I'll see whether my boss is open to it.
Just had a check of travel logistics ... the journey is a long one, so this is not going to work out, I'm afraid.

It's one of those colder days here in S.E. Michigan. I should dig out my old PADI card and come see you, instead! 😀 Can't get the time off to make it worthwhile though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 3

Jeff Moden wrote:

David Burrows wrote:
Phil Parkin wrote:
Unzipping files is natively available in .NET, so there should be no need to install additional software.
See here for more info.
I was thinking the same and there are XML/JSON additions as well.
Heh... there are two things I like about JSON... I don't use it but I hate it less than XML when others do. 😀

I have to work more now with Web APIs so with JSON as well :-/

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Getting kind of back on topic, has anyone tried the demo I provided?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Jeff Moden wrote:

Getting kind of back on topic, has anyone tried the demo I provided?

Not yet. I'm not back in the office until Monday 🙂

Far away is close at hand in the images of elsewhere.
Anon.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 6

Jeff Moden wrote:

Getting kind of back on topic, has anyone tried the demo I provided?

I have, brilliant work my friend

😎

But as I'm ACE'phobic and I do not trust the black-box logic of that kinds of drivers, I'll do it in proper SQL 😉

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Eirikur Eiriksson wrote:

Jeff Moden wrote:
Getting kind of back on topic, has anyone tried the demo I provided?
I have, brilliant work my friend
😎
But as I'm ACE'phobic and I do not trust the black-box logic of that kinds of drivers, I'll do it in proper SQL 😉

Thanks for the feedback Eirikur. The same logic would world on an Excel spreadsheet that was exported to a TSV or CSV. I just used the ACE drivers to skip that step.

You also said you'll "do it in proper SQL". Since the only part of my code that involved the ACE drivers was the initial import and so the only thing keeping it from qualifying as "proper SQL" (which I agree with, BTW), what would you personally use for the original import of the spreadsheet data?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 8

Jeff Moden wrote:

Eirikur Eiriksson wrote:
Jeff Moden wrote:
Getting kind of back on topic, has anyone tried the demo I provided?
I have, brilliant work my friend
😎
But as I'm ACE'phobic and I do not trust the black-box logic of that kinds of drivers, I'll do it in proper SQL 😉
Thanks for the feedback Eirikur. The same logic would world on an Excel spreadsheet that was exported to a TSV or CSV. I just used the ACE drivers to skip that step.
You also said you'll "do it in proper SQL". Since the only part of my code that involved the ACE drivers was the initial import and so the only thing keeping it from qualifying as "proper SQL" (which I agree with, BTW), what would you personally use for the original import of the spreadsheet data?

I import the spreadsheet by unzipping the files and read / load the content directly. The nice thing is that this method works for all the 20+ different OOXML spreadsheet programs regardless of the OS used on the originating system.

😎

This reply was modified 4 years, 10 months ago by Eirikur Eiriksson.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

Hi Jeff, I've had some trouble getting your demo working on my home PC.

I installed 32- and 64- bit ACE drivers and then ran this:

sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO

After that, I created the stored proc in a test database and moved the demo spreadsheets into a local folder.

When I try to run the first import, I get the following error:

Msg 50000, Level 16, State 1, Procedure dbo.ImportSpreadsheet, Line 377 [Batch Start Line 0]
ERROR: [dbo].[ImportSpreadsheet]: [(7350:16:2) at [dbo].[ImportSpreadsheet](2). Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". (STEP 1: Import the Whole Spreadsheet)]
Msg 208, Level 16, State 0, Line 2
Invalid object name '##SSFinal'.

Any ideas? Permissions, perhaps?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 10

most likely permissions.

as this is on your PC first thing to try is to run SSMS as administrator - just to confirm that it is indeed permissions of your user

and have a read of the following links

https://visakhm.blogspot.com/2013/12/how-to-solve-microsoftaceoledb120-error.html

https://dba.stackexchange.com/questions/204081/how-do-i-add-read-write-permissions-on-a-directory-for-an-sql-server-account

you may also need to set this registry setting manually - there used to be a bug on SQL where sometimes it would show as set on the oledb provider window but registry key would not be present - adapt the key to your SQL instance detail (MSSQL10.SQLADV needs to change)

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLADV\Providers\Microsoft.ACE.OLEDB.12.0]

"AllowInProcess"=dword:00000001

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

Phil Parkin wrote:

Hi Jeff, I've had some trouble getting your demo working on my home PC.
I installed 32- and 64- bit ACE drivers and then ran this:
sp_configure 'show advanced options', 1;  
RECONFIGURE;
GO 
sp_configure 'Ad Hoc Distributed Queries', 1;  
RECONFIGURE;  
GO
After that, I created the stored proc in a test database and moved the demo spreadsheets into a local folder.
When I try to run the first import, I get the following error:
Msg 50000, Level 16, State 1, Procedure dbo.ImportSpreadsheet, Line 377 [Batch Start Line 0]
ERROR: [dbo].[ImportSpreadsheet]: [(7350:16:2) at [dbo].[ImportSpreadsheet](2). Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". (STEP 1: Import the Whole Spreadsheet)]
Msg 208, Level 16, State 0, Line 2
Invalid object name '##SSFinal'.
Any ideas? Permissions, perhaps?

I have to first ask the most obvious question... did you change the file path in the call to the stored procedure? And, yes... it could be a permissions issue that SQL Server is having seeing the local directory.

It could be a couple of other settings and I'll try to get back to you tonight when I get home... although I do have a deployment at 10PM that I'm prepping for today.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 12

Hi all, I've (finally!) tried to get this working again, this time from my office PC. Unfortunately, with exactly the same outcome.

The SQL Server service is running under the NT Service\MSSQLServer account. I have given this account read/write permissions to the local folder containing the demo spreadsheets.

I can't run ProcMon on my machine, because I am not a local admin, so tracking down exactly which other folder may be causing issues is not easy. Nor (obviously) can I run SSMS as admin.

I have changed the path in the EXEC dbo.ImportSpreadsheet to match the path which I've used on my PC. I think I need to try again from my home PC, where I am the admin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

frederico_fonseca SSCoach Points: 16154 More actions · Answer 13

did you grant permissions (read/write/list) to everyone to the temp folder of the user that the instance is running under? - this as per my second link above.

For network accounts, folder is

:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

and for local system account its :\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

you need to give permissions to the user executing the proc to one of the folders above (and if it was a domain account it would be to the corresponding folder to that domain account)

you also need

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess', 1;

exec master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters', 1;

Phil Parkin SSC Guru Points: 246960 More actions · Answer 14

Thank you, Frederico. I'm at work again. Without local admin access, it seems I cannot adjust perms for those folders.

(I had already run the sp_MSset_oledb_prop commands though.)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin