sql commands for excel data 1

[crystalvictim]

Hi,

does anybody happen to have expierence using sql commands in CR10 in combination with accessing data that is stored in an excel sheet?

What I'd like to do is to create an sql command that not only selects certain columns from the excel sheet but also does some initial conversion (i.e. stuff like retrieving portions of the field by using functions such as left/right etc.).
I've tried to figure out for myselft how to do this and found some hints in the documentation but it doesn't seem to work, so I'd appreciate somebody giving me a brief sample code on how to use sql commands with excel.

Trying statements such as:
select 'tablename'.'fieldname' from 'tablename' tablename results in an errormessage saying "DAO Error Code: 0xd7a" "Syntax error in query. Query is incomplete". Adding a WHERE-Clause doesn't help either :-(

Thanks in advance!

 

[bdreed35]

Your best bet is to create a report against the excel spreadsheet first, without using a SQL Command.
Once you have done that, go to Database, Show SQL Query and copy the SQL statement.
Next, create another report and paste that SQL into a Command.
This will get you a baseline working command.
As far as doing some of the initial conversion, I was able to use a left and a right function in the command so you can use some functions there.

~Brian

 

[synapsevampire]

How are you connecting to the spreadsheet?

If you've never heard this before, Excel is a HORRIBLE data source. Those trying to use it as a database are generally greatly disappointed and end up spending waaaaaaay too much time developing basic functionality.

I suggest that you consider linking (not importing) the spreadsheet into an Access database and provide an Access query doing whatever advanced functions you require as th data source for Crystal.

-k

 

[crystalvictim]

Thanks for your replies! (I have to admit, that I should have thought of this myselft, but somehow I missed it...)
Showing the sql-string and changing it according to our needs worked (Those '_' and '$' attached to the tablename prevented my former efforts from being successfull.