[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Need to build a Crystal Reports or SQL select statement from a set of numbers in EXCEL.

How Can I combine a column of numbers in EXCEL to one cell, comma separated?

I have to build a select statement in Crystal that includes over 100 sets of numbers, in this format
and {RPTOBS.HDID} in [53,54,9836,2540]. They are currently sitting in an Excel document in one column, 1746 rows long.

If someone knows a better way to ask the database for these values, I am open to suggestions.
0
Becky Edwards
Asked:
Becky Edwards
2 Solutions
 
maqskywalkerCommented:
Do you use your crystal report as a stand alone crystal report? or within a Windows Forms? or Web Application?

Also, do you plan to use a database like Sql Server , Oracle or MySQL or do you just want to read the data straight from the excel spreadsheet?
0
 
vastoCommented:
0
 
mlmccCommented:
I don't believe you will be able to do it with the IN statement.  I believe there is a limit to the number of values in a the list.  

Crystal will treat the list of values inside the [ ] as an array so you are limited to 1000 values.

You could add a data source for the Excel sheet to the report then INNER JOIN it to the report datasource on the appropriate field.

mlmcc
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Do you use your crystal report as a stand alone crystal report? YES

you just want to read the data straight from the excel spreadsheet? YES, I will try to work on adding the data source and see if I can figure that out.
0
 
mlmccCommented:
If you need help just ask.

mlmcc
0
 
Becky EdwardsEpic Clarity DeveloperAuthor Commented:
Both answers were very helpful in determining the best solution.  Thank you all for your fast response!!!!!
0
 
James0628Commented:
FWIW ...

 If you're going to link an Excel file to a db table, the performance could be an issue.  Trying to do random reads on an Excel file can be really slow.  You might be able to avoid that problem by using a query in the db to read the Excel file into an indexed temporary table or table variable, and then Join the main table with that, instead of the Excel file.  If, by chance, the values in the Excel file are static, or don't change often, you could copy them into a "permanent" table, and then update that table as necessary.

 James
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now