[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can you change the data type in Crystal from say Number to String?

Posted on 2011-03-15
22
Medium Priority
?
2,891 Views
1 Endorsement
Last Modified: 2012-05-11
Hello

I'm converting a Crystal 8.5 report into Crystal 11, and in the process I've had to change the database from a csv file to an excel file.

After doing this, the data map screen came up and a few of the fields have changed data type ie from String to Number etc

Is there a way I can change the data type back to what it was? ie via Add Command or something?

1
Comment
Question by:spectrum17
  • 11
  • 6
  • 5
22 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 35143371
Not that I am aware of.

I assume they are numeric fields but that you want them to be strings.

One way to do it in the report is to use formulas for them like

ToText({YourField})
or
CStr({YourField})

You can control the number of decimals and the thousand separator as

CStr({YourField},0,"")

Another idea would be to modify the Excel file so the first row or 2 have dummy values that are definitely strings like xxxxx in those fields.  Crystal looks at the first few rows to determine the datatype.

I don't know if just putting ' in front of the numbers in Excel if Crystal would treat them as strings or not.

mlmcc
0
 

Author Comment

by:spectrum17
ID: 35143528
I've tried totext and cstr in the formulas but Crystal doesn't seem to be recognising any of the converted text.

I can't edit the file as it's huge and it get recreated each week.

Any other ideas?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35143559
ow does it get created?

Can that program be changed to insert a dummy record or 2?

What do you mean it doesn't recognize the converted text?

mlmcc
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 

Author Comment

by:spectrum17
ID: 35143593
The data file is created by a payroll program.

The program can't be changed to insert a dummy record.

Re the converted text isn't recognised an example is:

When this formula is used and the value in the field deduct.WCODE is "S1" Crystal is returning deduct.WCODED instead of "SUPERANNUATION FUND"

if  cstr({deduct.WCODE}) = "S1" then "SUPERANNUATION FUND"
else {deduct.WCODED}

0
 

Author Comment

by:spectrum17
ID: 35143697

PS I saw something on google that said you could change the data type via Add Command, using something like this:

SELECT deduct.WCODE
 FROM deduct
CONVERT (STRING,WCODE)

I'm getting a syntax error when I try it.

I also saw a Crystal solution that JAMES0628 said you could change the data type via SQL. he didn't post the syntax though.

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35144096
I suspect it will more like

SELECT CONVERT(VarChar,Deduct.WCode) as WCode
FROM Deduct

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 35154634
CR decides what type each column in an Excel file is by looking at the values in some of the first rows.  I'm not sure exactly how many, but it seems to be somewhere around 15 to 25.  If it decides that a column is numeric, any string values in that column will simply be ignored/dropped.  So, your CStr ({deduct.WCODE}) = "S1" test simply can't work, because CR has decided that WCODE is numeric, so it can never be "S1".

 I handle this kind of thing by adding around 20 dummy rows to the beginning of the Excel file with the correct type of value in each column, like 0 in the numeric columns and "A" in the string columns, to get CR to interpret the columns the way that I want.

 I've never tried to write a CR Command for an Excel file.  It's an interesting idea.  I just tried it and I was able to use a Command to read an Excel file, but I don't know if that query language includes any kind of conversion function.  The MS SQL CAST and CONVERT functions don't seem to be recognized.  Either that, or the syntax for them is different for Excel and I just didn't happen to try the right syntax.

 It might be possible to use a Command to force the data type for the columns in a different way.  I'm not entirely sure.  I tried adding a SELECT (before the main SELECT) that put values of the desired type in each column (similar, in concept, to adding dummy rows to the file).  I took one of the columns that CR had decided was numeric in the file, and made it a string in the new SELECT.  But I got an error when I tried to save that Command, apparently because the first SELECT was just using literal values and not actually reading the file (or anything else).  So then I changed the first SELECT so that it was still using the literal values, but also reading the file, and I added a WHERE that would never be true, so that that first SELECT wouldn't actually produce any rows.  CR let me save that Command, and it made that one column a string instead of numeric.  I didn't do any testing beyond that, but my guess is that CR was getting the data type for each column from the first SELECT, even though it wasn't actually producing any data, and then using the same types for the columns in the second SELECT, where it was actually reading the file.  So, that _seemed_ to work, as far as it went, but whether or not the same thing would work for you, I really don't know.  And keep in mind that if you do that, the first SELECT is (presumably) going to be reading through the whole Excel file (even though it's not producing any data), so the report would be reading the file twice.  If it's a really large file, that could take some time.

 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 35154671
Another thought:

 If you happen to have MS SQL Server, another possibility would be read the Excel file through MS SQL Server (eg. using a stored procedure or a CR command).  You could create a linked server that pointed to that Excel file, or use the OPENROWSET function.  If you read the Excel file in MS SQL, you can use the CONVERT/CAST functions to change the data type for the columns.

 If you have some other db, like Access, you may be able to do the same kind of thing in that db.

 James
0
 

Author Comment

by:spectrum17
ID: 35155201
Hi mlmcc

I've tried your solution first, and inserted the following command:

SELECT CONVERT(VarChar,deduct.WCODE) as WCODE
FROM "deduct"

I get the following syntax error:

Database Connector Error: 'DAO Error Code 0xd7a
Source: DAO.database
Description: Syntax Error in Query. Incomplete Query Clause'

Any idea how to get rid of this error?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35156917
The problem is probably the Convert syntax.  I don't know how to get rid of it since that is database dependent.  Check how convert is supposed to be written.

I always thought it was

Convert(Field,format)  yours is reversed.

mlmcc
0
 

Author Comment

by:spectrum17
ID: 35161662
No luck with that one either mlmcc. I've had another thought....The 8.5 report (that I am trying to convert to 11) didn't have this problem, probably because it was a csv file and was run using a text driver.

I've attached a document which shows how I set this up in 8.5. I can't see the option to set the same connection up in 11. Do either of you know how to do this? createdatasource.doc
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 35162336
They moved the TEXT option to the ACCESS/EXCEL driver

Select that then change the file type from Access to TEXT

mlmcc
0
 

Author Comment

by:spectrum17
ID: 35162768
I would so have loved this to have worked!! I used the TEXT option under the ACCESS/EXCEL at DAO and didn't get any data when I refreshed the screen. All gone.

Any ideas why this would be?
0
 
LVL 35

Expert Comment

by:James0628
ID: 35164043
First of all, as far as the CONVERT syntax goes, in MS SQL, it is data type and then value, as you had it, although with a type like varchar, you would probably also include a length (not sure if that's required), so it would be something like CONVERT(VarChar (10), deduct.WCODE).  However, as I said, CONVERT and CAST don't seem to work when accessing an Excel file.  I tried various parameters in various arrangements and couldn't get them to work.  This isn't MS SQL.  It's a SQL-like language being used to read an Excel file.  I don't know if it has any kind of conversion function.

 As for the text option, as mlmcc said, it's under Access and it does work.  What did you do exactly?  You said that you "used the TEXT option" and "refreshed the screen".  Was this in the report that was reading the Excel file, or the old report that read the text file?  Were you trying to change the datasource or add a new one?  etc., etc.

 Going back to your original post, you said that you "had to change the database from a csv file to an excel file".  Why?  Forgive the obvious, but did you try running the old report under CR XI?

 James
0
 

Author Comment

by:spectrum17
ID: 35164362
Hi James, answers to your questions as to what paragraph they were in are below:

Para1: The number of characters in the field could vary. Would this matter?

Para2: I went into Set Datasource Location, clicked on Create New Connection - Access/Excel DAO, Selected the csv file from the drop down list, changed the database type to 'Text', clicked Finish, then updated the datasource to read this new connection.

Para3: When I tried to run the old report it brought up the OLE DB ADO screen and asked to choose an ODBC DCN from a list.
0
 

Author Comment

by:spectrum17
ID: 35164395
PS Re Para1 : I changed the formula to:

SELECT CONVERT(VarChar(10),deduct.WCODE) as WCODE
FROM "deduct"

The same error was received. (Description: Syntax Error in Query. Incomplete Query Clause')
0
 

Author Comment

by:spectrum17
ID: 35164398
PSS

Re Para2:

I was trying to change the datasource location, as Crystal couldn't recognise the old one.
0
 
LVL 35

Expert Comment

by:James0628
ID: 35171167
 > The number of characters in the field could vary. Would this matter?

 That shouldn't be a problem.  That's what varchar is.  You set a maximum length (eg. varchar (10)), but the field only uses as many characters as are required for each value.  Or, you can use char (eg. char(10)), and the field will always be that length, with any "extra" characters filled with spaces.

 But that's really a moot point, since, as far as I can tell, there is no way to do a conversion like that in an Excel query.


 Looking back at your screenshots of how you set up the connection in CR 8.5, it appears that you used a file DSN, which means that the connection information is stored in a file somewhere.  You would have selected that DSN file in the last screenshot that you posted, and then gone on from there to enter the connection details that would be saved in that file.

 If that's the case, it kind of seems like the old report should have worked under CR XI, since it supports the same kind of connection, but maybe there are some differences between 8.5 and XI that prevented CR from just automatically making the connection (I haven't used 8.5, so I really don't know).

 If the old report is using a DSN file, you could try re-creating the connection under CR XI.

 Go to the Database Expert in CR XI and open up "Create New Connection" and then ODBC.
 Double-click "Make New Connection".
 Click the "Find File DSN" option and browse to the DSN file that the old report is using.


 If you like, you could also post your report here.  Maybe we can see something that might help.

 James
0
 

Author Comment

by:spectrum17
ID: 35173275
Hi James,

I've tried to do what you mentioned above, but I'm still not getting any results.

The 8.5 report was written so long ago, I can't remember if it used a File DSN or not. How can I check?

I would post the report on here, but it includes a lot of sensitive information, so instead I've attached a file below of the steps I followed to update the datasource location. I've also posted the code behind the file dsn.

Can you have a look at this and see if I have made any mistakes in re-establishing the connection?

Many Thanks!!
payslip.doc
0
 
LVL 35

Assisted Solution

by:James0628
James0628 earned 1000 total points
ID: 35187027
To see if the old report used a DSN file, all I can suggest is to go to "Set Datasource Location" and open up the "Properties" entries and see if it says anything there.  I don't use DSN files, so I don't know if it will say anything there or not, but if it's not there, I don't know where else to look.  OTOH, in your screenshots, you're using a payslips.dsn to create a new connection.  I was assuming that payslips.dsn came from the old report.

 Looking at your screenshots, the fact that CR listed some CSV files (I assume that they're the correct files) would seem to indicate that CR is getting the correct folder from the DSN file.  Beyond that, I don't know why the report is not getting any data, although one thing did just occur to me.

 If the report is reading multiple CSV files, I assume that they're linked.  I wonder if CR may be automatically relinking the files when you update the datasources for them?  If so, it may be doing it incorrectly.  It would probably try to link any fields with the same name.  I'd go to Database > "Database Expert" in the old report and check the links, and then check them in the new report with the new datasources and see if they've changed.  If the links are different, you could look for an "auto link" option, or something like that, and, if it's on, see if you can turn it off, and try changing the datasources again, or just change the links manually.

 If that doesn't pan out, have you tried creating a new report using that DSN file, to see if it can read anything from any of the CSV files?  I'd try it with just one of the CSV files first, to avoid any possible linking issues.

 James
0
 

Author Comment

by:spectrum17
ID: 35187945
Bingo, that helped find the problem! There were some selection formulas that weren't working with the report. I have since changed them (added Cstr) now they work.

Now I just have to solve some other conversion issues with the report!!
0
 
LVL 35

Expert Comment

by:James0628
ID: 35196747
Glad you were finally able to figure it out.

 James
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Screencast - Getting to Know the Pipeline

834 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