Sage 100

I have a Crystal Report that I need to export to Excel.  My issue is that the client needs any non-numeric or date column to show as a 'TEXT' formatted column in Excel.  Mine show as 'GENERAL'. These are text fields in Sage (ex. address, name, item code, etc.). I cannot find a way to export those columns and have Excel format them as 'TEXT'.  Before I tell him he has to manually format those columns, does anyone have any suggestions?  Thank you!!

------------------------------
Dawn Kulbacki
Capitol Computer Systems, Inc.
------------------------------

I thought Excel Power Query could be used to do this, instead of a Crystal report, since it provides for defining the data type of each column before loading the data to a worksheet. The data type doesn't hold into the resulting table though.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------

I have several of these and you can't let Excel think for you ... it will strip leading zeros, improperly format dates ... 

What I have done is export the data as text to a .txt file.  Usually I use a '|' separator so the commas don't give me grief.  Then when you import the .txt file into Excel, you get to specify the data type for each column as you need it to be.  A couple more steps to be sure, but a LOT more reliable.

------------------------------
Randy Marion
------------------------------

Did you try adding a single quote before the value?  Excel should interpret that as text.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

I usually export from Crystal using Tab Separated text so that Importing to Excel is formatted correctly.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

You can still use Power Query to do this and without opening Power Query. After retrieving the data one simply changes the format of the column to "Text" (in Excel, not Power Query), save, reopen, and refresh the file to see the column format retained. 

If you have never seen how easy it is to convert a Crystal query to Power Query, one can use Crystal's 

1. "Database" menu
2. "Show SQL Query" command
3. copy the displayed query to the clipboard

Then transfer the query to Power Query using Excel's

1. "Data" menu
2. "Get Data" ribbon button
3. "From Data Sources"
4. "From ODBC" command (for Premium "From SQL Server Database")
5. Paste the query from the clipboard into the Advanced option "SQL statement" field. 

This short video demonstrates this.

------------------------------
Dan Burleson
Software Consultant
Connex Software
------------------------------