Importing a Long named Excel Sheet with Spaces

Question

Importing a Long named Excel Sheet with Spaces

Thom A

SSC Guru

Points: 99133
More actions
August 17, 2017 at 7:47 am

#344079
I feel like I'm missing something incredibly simple here. I'm trying to import data from a file we received from another company. The worksheet I want to import from is named is named "KRS_144D Monthly Cumulative Cla". The data in the Worksheet is located in the Range B9:AD. I'm using the SQL Server Import and Export Wizard right now, rather than SSDT, but did the usual steps:
1. Right clicked the database and selected Tasks -> Import Data...
2. Clicked Next >
3. Select Microsoft Excel as the source, and selected the saved document in the Browse... window. Checked that Microsoft Excel 97-2003 is selected (as it's a .xls file) and ticked First row has column names
4. Clicked Next >
5. Selected Microsoft OLE DB provider for SQL Server. Double checked the Server, Authentication and Database settings. Clicked Next >.
6. As the data does not start in Cell A1, selected Write a query to specific the data to transfer
7. Clicked Next >.
8. Entered the SQL statement: SELECT * FROM [KRS_144D Monthly Cumulative Cla$B9:AD];
Upon clicking Parse/Next > I receive the error:
The statement could not be parsed.
------------------------------
ADDITIONAL INFORMATION:
The Microsoft Jet database engine could not find the object 'KRS_144D Monthly Cumulative Cla$B9:AD'. Make sure the object exists and that you spell its name and the path name correctly. (Microsoft JET Database Engine)
If I remove the B9:AD portion of the statement, the query passes fine.it doesn't like having the cell reference, for some reason. Not had this problem with any other sheet. Anyone got any ideas if I'm doing something "stupid"? I've tried the following permutations, but none seem to work with the cell reference:
[KRS_144D Monthly Cumulative Cla$B9:AD]
['KRS_144D Monthly Cumulative Cla$'B9:AD]
['KRS_144D Monthly Cumulative Cla$B9:AD']
['KRS_144D Monthly Cumulative Cla'$B9:AD]
I'd hazard a guess it's probably the one or a combination of the Max length Worksheet name, having spaces, and that the file is xls not xlsx (so uses the JET engine, rather than ACE). Anyone got any ideas on a work around?
Thanks all.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Don't you need to add a ending cell number as you cant go up to cell AD1 as you start in cell B9 in Excel, so you need to define the whole selection as a square?

SELECT * FROM [KRS_144D Monthly Cumulative Cla$B9:AD32456];

Or could you do
SELECT * FROM [KRS_144D Monthly Cumulative Cla$B:AD];
then delete the first 8 blank rows from the table?

Thom A SSC Guru Points: 99133 More actions · Answer 2

anthony.green - Thursday, August 17, 2017 8:11 AM
Don't you need to add a ending cell number as you cant go up to cell AD1 as you start in cell B9 in Excel, so you need to define the whole selection as a square?
SELECT * FROM [KRS_144D Monthly Cumulative Cla$B9:AD32456];
Or could you do
SELECT * FROM [KRS_144D Monthly Cumulative Cla$B:AD];
then delete the first 8 blank rows from the table?

With xlsx you don't need to declare the end of the file, no (as you may not know where the end of the file is). When you use it like I did, for example, ACE would load up to the lowest user editted row. I wasn't sure, however, if JET reacted in the same way. Gave it a go, but the same error returned.

Using B:AD doesn't work either, however, considering that the file contains numerics and dates and Excel has a habit of importing non-string values in odd manner when importing them as a string, that isn't a solution I'd want to pursue anyway. For example a sheet I was working with earlier imported the date 2013-04-29 as the string "41393"; that's 1900-01-01 + (41393 - 2) days (0 is 1899-12-30 in Excel...?).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99133 More actions · Answer 3

So, I felt like trying something "stupid". The file is definitely an xls (1997-2003) file, however, I set the import wizard to xlsx (2007-2010). Worked straight away.

I assume that the problem is with JET, and that the ACE drivers were happy to deal with the declared Cell Reference. in teh Worksheet name.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

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

Thom A - Thursday, August 17, 2017 8:33 AM
So, I felt like trying something "stupid". The file is definitely an xls (1997-2003) file, however, I set the import wizard to xlsx (2007-2010). Worked straight away.
I assume that the problem is with JET, and that the ACE drivers were happy to deal with the declared Cell Reference. in teh Worksheet name.

I'll also tell you that, with the ACE drivers and so long as there's nothing in the columns to the left or the rows above the starting cell, you don't need to tell it what the starting cell is. It figures it out auto-magically.

I guess I also ask, why in the world are you using that awful import wizard? And how often do you need to do this?

--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)

Thom A SSC Guru Points: 99133 More actions · Answer 5

Jeff Moden - Tuesday, August 22, 2017 8:39 PM
I'll also tell you that, with the ACE drivers and so long as there's nothing in the columns to the left or the rows above the starting cell, you don't need to tell it what the starting cell is. It figures it out auto-magically.
I guess I also ask, why in the world are you using that awful import wizard? And how often do you need to do this?

There was a value in cell A1, so hence the declared range. Even so, I don't really trust the ACE drivers half the time, so generally I prefer to tell it where the data I want is, even if it thinks it knows better (or is right).

I only, as a rule of thumb, use the importer when I first receive file for the first time. I might get a few new types a files a year. I'll run it through the import wizard quickly to see how it comes out and how well the data actually matches ours. After a trial, then I go to SSIS and do it all properly for automation, often storing the data in a staging table, and then going on to put it where it needs to go to properly (as, like we all know, ACE has its quirks).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

David Burrows SSC Guru Points: 65143 More actions · Answer 6

I hate trying to import Excel into SQL Server, for the reasons already stated, this is yet another area where MS does it so badly.
ACE really needs an option like IMEX to tell ACE to retrieve data as text with no formatting or conversion.
Fortunately at the moment my spreadsheets have standard layouts and no mixed data columns.
I have an old VB6 app that I wrote that extracted the first sheet as a text file with no formatting or conversion and then I would import the text file.

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