Solved

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

Posted on 2015-02-04
7
268 Views
Last Modified: 2015-02-05
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
Comment
Question by:Becky Edwards
7 Comments
 
LVL 1

Expert Comment

by:maqskywalker
ID: 40589162
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
 
LVL 18

Assisted Solution

by:vasto
vasto earned 250 total points
ID: 40589175
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 40589235
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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 

Author Comment

by:Becky Edwards
ID: 40589432
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 40589452
If you need help just ask.

mlmcc
0
 

Author Closing Comment

by:Becky Edwards
ID: 40589775
Both answers were very helpful in determining the best solution.  Thank you all for your fast response!!!!!
0
 
LVL 34

Expert Comment

by:James0628
ID: 40590816
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

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Crystal Reports 9 and Subreports 3 66
Excel 2007 Formula to Remove Null/#Value Results from Cell 1 60
Calculation - Mini/Max Removed then average 8 25
Excel 2013 13 51
1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Viewers will learn how to create a PivotTable and make basic changes to it in Excel 2013.
Viewers will learn a basic relationship technique in Power Pivot for Excel 2013.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question