I have an Excel sheet which contain 1

[BettyJ]

I have an Excel sheet which contains two columns patient name and date. I need to read this data and pull other data from an Oracle database for only the patients listed in the Excel sheet. How can this be done?

Thanks in advance.

 

[IanWaterman]

Very slowly
Just create two sources of data and link, crystal will complain but it sholud allow you too.
All filtering will be done in the report, oracle will deliver all records and then crystal will filter against excel list.

Ideally you should import the excel list into Oracle.

Ian

 

[BettyJ]

Thank you so much, IanWaterman. It works perfect.

 

[BettyJ]

Hi Ian or anyone else,
I am having a small issue-some patients are missing in the report. I knew that the names were not exactly matching in the Excel and the database. So I thought that is the reason. But then, I took just a few patients and made sure the names are same, still I'm not getting all the patients. Any suggestions?

 

[IanWaterman]

Try using trim() you might have leading or following spaces.
Is case the same Oracle is case sensitive

Ian

 

[BettyJ]

Thank you Ian for your reply.

I tried trim(). Names are in uppercase in both excel & oracle. I used the function too just to make sure. Still not working.

 

[BettyJ]

Just an update. I took patient_ids of 10 patients and placed it in an Excel file. Then everything works fine. It pulled the name and DOB of all the 10 patients. Then I did the same with those patients' name, it is not pulling all 10 patients.

 

[CoSpringsGuy]

sounds like case? or trim as Ian mentioned

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[pmax9999]

The only other thing I can think of is one of the data sources having additional spaces between the words within the strings that aren't obvious, or line feeds / carriage returns you cant see.

A simple test on the spreadsheet would be to do a Find/Replace on " " (double spaces), and to copy some of the name data cells to Notepad or similar to enable easier idenification or anomalies.

For a test on the Oracle data you could write a quick query or report to return records with double spaces, CHR(10), CHR(13) etc in the name field.

Also, so you can join two data sources, any manipulation of the data to ensure they match will need to be done within the raw data (probably easiest within the spreadsheet) or you will need to use a Command to so you can correct the data issues within the query, otherwise the two data will never match.

Hope this helps.

Cheers
Pete

 

[BettyJ]

Thank you all for your replies. I am still stuck with this issue.

When I copied the names from Crystal report to Word I couldn't find any extra characters.

Pete, I am not exactly sure how to write the query you suggested. I ran a query to find names with double spaces, but how do I search for CHR(10) and CHR(13) in the name field.


Also,
When I use TRIM(UPPERCASE({PERSON.SEARCHNAME})) in {Sheet1_.PatientName} it is not displaying all the records.

Then, I copied and pasted the names from the Excel cells into the record selection criteria and it works
When I use TRIM(UPPERCASE({PERSON.SEARCHNAME})) in ("LASTNAME1, FIRSTNAME1", "LASTNAME2, FIRSTNAME2"......)
all the records are displayed.

I am really confused.

 

[CoSpringsGuy]

You mentioned above that you had it working with paiteint_id.

Run it like that again then create two formulas...

len({oracledb.patientname})

and the next

len({excel.patientname})

place those in detail section and run.. if they are not the same then you have an extra character in there ... to search for chr(10)

if instr({excel.patientname},chr(10)) > 1 then true else false

you can replace chr(10) for other suspicious characters....

good luck


_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[BettyJ]

Hi Ian,
Your initial reply on 1 Mar 13 9:48 has helped me a lot. I have been using this a lot for one of my projects. For that project, when connecting excel file to the Oracle database, I was working from within Crystal reports and pulling the data (and the excel file is located on in my machine). Thank you again. The other part, since name was giving issues (I still couldn't figure out what the problem is) and weren't matching, I ended up using the ID instead.

Now, I have to run a report that connects to an excel file. It has to be run from within the EMR application.The excel file is on my machine. It gives an error that Sheet_1 cannot be found. My question is how can I get the EMR application to look for this excel file (where it should be placed and how it can be accessed)

I have already posted this question, anyone else who can help also, please reply.

Thank you in advance.