Access Crystal Report Using Excel Data

Using Crystal Report with Excel Data !!!

1. Name the Excel Sheet and Set the Headers with the Field Names highlighted , Bold Letters .
2. Make sure you format field i.e Currency, date, Text, General, etc.
3. Now open Crystal Reports , It opens up Database expert or you can click Database expert on the menu bar.
   
4. In the DataSources , open a Create a New Connection ->  select Access/Excel (DAO).
5. You can see a new window to input excel file. Database Name is your file path and Database Type is Excel 8.0 or other (version try it to be latest versions  to eliminate bugs) & click finish
6. Here on the right side of the window you can see Path Name and the Excel Sheet Name.
7. Note: If you dont find your Excel Sheet , you may want to check right click on the file name in the database expert -> select option and make sure Table name or owner like is empty. As you are creating a new report .
8. once you see you table in the field browser, then you have table data which is ready to use.
9. And Imp is to check the Database -> Log on or off server. -> create a new connection and add in file .

Extract date from timestamp in crystal report 2008

 Using Crystal Report 2008 :

Found a way to extract Date from field which contains Timestamp.

for ex: userevent table there is a table.field which contains timestamp. And i want to convert into a date
while eliminating time .

you can see here;

for example timestamp field contains 20130128145651 this date and time . now i wanted to convert into
01/28/2013 format.

you can try using this formula:


Go to formula -> create a new formula -> type in the below formula

date(tonumber("20" & mid({table.field},3,2)),tonumber(mid({table.field},5,2)),tonumber(mid({table.field},7,2)))

check for errors by clicking x+2 -> save and close.

place the formula in the details section and  preview.


or you check this formula too..


//stringvar dt;
//
//dt:=(totext(Year(currentdate),'0000')&totext(Month(currentdate),"00")&totext(day(currentdate),'00'));
//
//tonumber(dt);

First one worked for me in this case and second one is also similar