4

The column names looks something like: "Ab. (Cd)"

It has , '.' as well as ( ) in the column name.

I have tried square brackets [ ] around the column name and have also tried ' ' and " " without much success. Please help.

I am using SQL Server Import and Export Wizard to import some data. The query looks like: 

Select 'Trans. Z4 (St 85)' from `'Monthly Prices$'`

The full SQL statement used is:

Here's the query: Select F1, HH, AECO, Sumas, Stanfield, Malin, [PG&E], Opal, SoCal, SJ, wTX, sTX, HSC, FGTZ3, [Trans. Z4 (St 85)], Dom, [Tetco M3], 'Trans. Z6 (NY)', AGT, Dawn, Chi, Midcon from 'Monthly Prices$'

Please note that the table 'Monthly Prices$' is a sheet in an Excel workbook that I am trying to import.

Riley Major
  • 1,904
  • 23
  • 36
JackOfAll
  • 51
  • 1
  • 1
  • 5
  • 2
    As you are currently discovering, it vastly helps to have simple, descriptive names for things devoid of any special characters. My first solution would be to rename the columns. – Ellesedil Aug 08 '14 at 18:12
  • Square brackets will work, what happens when you try that? – DavidG Aug 08 '14 at 18:16
  • @Ellesedil - Renaming columns in not an option as it was done by someone else and has other implications. I am not new to SQL and wouldn't have done such a boo-boo myself :) – JackOfAll Aug 08 '14 at 18:22
  • @DavidG when I use square brackets I get error: "The SQL statement is not a query". The square bracket works well with names that have just , but square bracket is not working when the column name has ( ). – JackOfAll Aug 08 '14 at 18:23
  • @JackOfAll Can you post the exact query you used? I've just tried it on my local database with a column called `[Alpha. (Beta)]` – DavidG Aug 08 '14 at 18:26
  • @DavidG - Here's the query: Select F1, HH, AECO, Sumas, Stanfield, Malin, [PG&E], Opal, SoCal, SJ, wTX, sTX, HSC, FGTZ3, [Trans. Z4 (St 85)], Dom, [Tetco M3], 'Trans. Z6 (NY)', AGT, Dawn, Chi, Midcon from `'Monthly Prices$'` – JackOfAll Aug 08 '14 at 18:27
  • Please note that I am trying to use the wizard to import from an excel sheet. Other columns are importing fine, but it gets stuck when it hits the columns with ( ) in them. – JackOfAll Aug 08 '14 at 18:28
  • So the "column" names are actually column headers in a spreadsheet, right? Can you make a copy of the spreadsheet, rename the column headers there, and retry your import process? – Dave Mason Aug 08 '14 at 18:34
  • wrap `'Trans. Z6 (NY)'` in square brackets too – Christian Phillips Aug 08 '14 at 18:48
  • @Dmason - I mentioned already - renaming columns isn't an option. I would have done it already if I could rename. – JackOfAll Aug 08 '14 at 18:53
  • @christiandev - square brackets around both Trans. Z4 (St 85) and Trans. Z6 (NY) is the first thing I tried. It doesn't work and gives me an error - "The SQL statement is not a query". – JackOfAll Aug 08 '14 at 18:54
  • @JackOfAll, you still can't rename, even in the context of my suggestion? If not, why? – Dave Mason Aug 08 '14 at 18:57
  • Have you thought about querying and addressing it via the ordinals? http://stackoverflow.com/questions/18256562/ssis-import-excel-document-using-column-position-not-name/18256704#18256704 – billinkc Aug 08 '14 at 20:21

3 Answers3

2

Your query:

Select 'Trans. Z4 (St 85)' from 'Monthly Prices$'

You cannot SELECT from a string. You need to use square brackets around the table name:

Select 'Trans. Z4 (St 85)' from [Monthly Prices$]

But that's only half of the problem. If you run this, you will get the same string, "Trans. Z4 (St 85)" on every row. You need to use square brackets for that column name as well:

Select [Trans. Z4 (St 85)] from [Monthly Prices$]
Riley Major
  • 1,904
  • 23
  • 36
  • No. The problem is not with the syntax of table selection. Either of those table selection syntax works. The issue is with the column selection. The query I posted above gets all the columns correctly up to the point when it hits the columns with ( ). – JackOfAll Aug 08 '14 at 18:36
  • I'm sorry, but that can't be true. If you run the first statement I reproduced from your question, you will get a syntax error: "Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'Monthly Prices$'." Why don't you post your full SQL statement in the question? – Riley Major Aug 08 '14 at 18:37
  • Please check the modified question above. I have included the full SQL statement and the fact that I am trying to import from excel using SQL Server import export wizard. – JackOfAll Aug 08 '14 at 18:47
1

I think it is the wizard which is your problem. Often the wizards will not have as robust an understanding of SQL as writing the statement in SSMS. I was able to create a spreadsheet with your column names but was only able to import it with the wizard if I imported the whole table not using a sql statement. Is this a possibility for you? I could query the table using brackets properly in SSMs afterwards with these names.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
0

To whomever next comes here for the answer, here it is:

The Excel SQL interface is terrible with column names. In your case the issue is with the dot . not the parentheses (). You must replace it with a sharp # for Excel to parse, but since it considers words with sharps in them to be (date) formatting you must also bind it with back-ticks ` or brackets [].

The correct query is:

Select 'Trans# Z4 (St 85)' from [Monthly Prices$]