Number Formatting - Keeping it as-is

[Blackbird]

I am trying to keep the formatting of the data in my CSV file intact
when enter it into my document. For example, I want the number to
come in as "16-18" as it reads in my data and it keeps placing it as
01/01/1900 16:18:00. My content object name looks like [D]SEAT_DESC
which is supposed to be telling my end user which seats they have
purchased. What does the [D] stand for? Date?

As always, Thank You!

[Guillaume Besnier]

Hello,

"D" indeed stands for Date (while "T" should stand for Text and "#" for
numeric - not sure that boolean exists). It is the type of a Data Source
field (while Content Object types are Text, Graphic, Visibility, Text
File, and Style).

As far as I know, it is currently not possible to change the field type:
uDirect reads some first lines in your data source, and automatically
sets the field type, according to the type of the values found in the
data source.

In your case, I understand it could be good, that the field type would
be text (thus allowing you to preserve the original values, without any
unexpected conversion).

I think you could "help" uDirect a bit, by creating a test data source
with a single record (which you may mainly copy/paste from your own data
source). Change 16-18 to abc (or to "16-18", with double quotes), so
that uDirect sets the field types as you like.

Once the test data source is opened in uDirect, just review your field
types (by having a look at one of your ADORs), save the InDesign file,
open your official data source: the field types will be preserved, as
when you "educated" uDirect.

-------- Message original --------
Sujet : [xmpie-users] Number Formatting - Keeping it as-is
De : Blackbird <blackbirds...@gmail.com>
Pour : XMPie Interest Group <xmpie...@googlegroups.com>
Date : 15/02/2010 23:37

[Joseph Caldwell]

Hi
I think it also depends on your datasource. You really should explain
where your data originates and how you plug it into XMPie.

SQL Server is strongly typed, so if that was a 'text' type of field
then it would be so in XMPie.

I would guess that you are using Excel, in which case you need to
place an apostrophe (') in the first row in front of your data. This
would tell the ODBC engine reading your Excel file that you are
working with a text file.

I have not tested this with XMPie, but I noticed with some other ODBC
applications that the first x number of records would be read and
using the majority would determine the field type.

For example if I had 20 records for a column {Zip Code(5)}. If 9 of
the records had an apostrophe, then the field would be imported as
numeric. If 11 of the records had an apostrophe then the field would
be imported as text.

If the field was imported at text then the rows with no apostrophe
would be read as null in the zip code. If it was imported as numeric
then the text values would be read as null. To solve this we needed
an apostrophe in each record for that field.

Importing to Access first will give greater control over the import
and allow you to import it as text and XMPie will recognize that. I
believe you would need to use uPlan to use Access and SQL.

[Joseph Caldwell]

Guillaume Besniers' idea would work as well as using Access, but both
require extra steps.

On Feb 15, 5:37 pm, Blackbird <blackbirdsbrokenwi...@gmail.com> wrote:

[Dan Sideen]

Mixed data types causing problems is a well known issue in Excel, at least amoung programmers. There is a registry hack that forces Excel to look at more than 20 records, but this slows things down and isn't bullet proof.
A trick that we use is to copy the first Excel row (the header row), and paste it as the second record in the file. This forces Excel to read every field as text, which is usually a good thing.
Of course, you end up with an extra output record, but we tell everyone this is to prove that our field mapping is correct!

--
You received this message because you are subscribed to the Google Groups "XMPie Interest Group" group.
To post to this group, send email to xmpie...@googlegroups.com.
To unsubscribe from this group, send email to xmpie-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/xmpie-users?hl=en.

This message is intended only for the addressee, it may contain privileged or confidential information. Any unauthorized disclosure is strictly prohibited. If you have received this message in error, please notify us immediately so that we may correct our internal records. Please then delete the original message. Thank you.

Ce message est une communication confidentielle et protégée et l'information qu'il contient est réservée à l'usage exclusif du destinataire. Si vous n'êtes pas le destinataire visé, vous n'avez aucun droit de divulguer cette information. Si cette communication vous a été transmise par erreur, veuillez la détruire et nous en aviser immédiatement. Merci.

[couch]

Blackbird indicates that the datasource is CSV...

Depending on how the CSV was created (IE if you did it with Excel, it
does not properly quote text columns, something that Microsoft even
admits to: http://support.microsoft.com/kb/291296 ).

As everyone has indicated, when the datasource does not specify the
data type of each column, XMPie (or the MS Jet Engine) takes a guess
at what the data is based on what is in the column.

Also many people don't want to transfer the data to other formats for
wories about mistakes/errors etc, so here is a trick to get XMPie to
treat the problem CSV column as text:

Open the CSV file with a text editor - eg Notepad.
In the first row of data, put double quote marks (") around the
problem field eg:

from this:
ID,NAME,SEAT_DESC
1,Couch,16-18
2,Blackbird,12-14

to this:
ID,NAME,SEAT_DESC
1,Couch,"16-18"
2,Blackbird,12-14

Now in Notepad, choose "save as", give the file a new name, and under
"Encoding" select either "unicode" or "UTF-8".

Then link this new file up to InDesign with uDirect/uCreate and all
should be OK.

NOTE: This trick also works for leading zeros which disappear from
number columns. (Where 00234 becomes 234 because the data gets treated
as a number/integer.)

The real tricks here are:

1) that data fields should be correctly typed (Access/SQL are the best
if you have it available). Putting quote marks around the data in CSV
or TXT files makes the reading application treat the column as text/
string value, so there will be no attempt at interpreting the data as
a date or number.

2) saving the text file with unicode encoding is a "hidden" trick.
When the text file is ANSI encoded, XMPie will use the MS Jet Engine
to determine how each column should be treated. The MS Jet Engine will
look at many rows to determine this (something like 64 rows for TXT/
CSV, and something like 24 for XLS - sorry I don't remember the exact
number) this means you would have to put quotes around a lot of rows
to get the desired result. However, with Unicode encoded text files,
XMPie uses its own text processor, which looks only at the first row
of data to determine how to treat the data in each row.