Data Truncation from csv and xls file

Posted by: Adam Delaney

Data Truncation from csv and xls file - 08/21/09 12:43 PM

I am having an issue where there is a field in my data file that contains a block of text. I have tried saving this file out as an xls and csv file and get similar results where data is truncated at 260 char.? When I copy or cut that same data and paste into a new excel doc, save out as an xls and open this data file in psm, I can see more than 260 char?? Why is this happening?
Posted by: Raphael Lalonde Lefebvre

Re: Data Truncation from csv and xls file - 08/21/09 02:45 PM

Adam,

You say you tried to save the file as an xls and csv, but from what software did you do that? Is it only truncated when you look at it in PSM, or it's already truncated in the file itself?

Regards,
Rapha
Posted by: Adam Delaney

Re: Data Truncation from csv and xls file - 08/21/09 02:48 PM

I can see all the data within excel 2007 application. When exported to csv and opened within a text editor, the entire field with all the text is visible, this issue appears like it is bound to printshop mail processing.

If I copy a record that I am having issues with and paste it into a new excel workbook, save as a xls 2003 file and open this in psm, it works, if I copy my entire workbook into a new excel 2003 file and open it in printshop mail it doesn't work?
Posted by: Anonymous

Re: Data Truncation from csv and xls file - 08/24/09 07:17 AM

Hi Adam,

CSV data is handled by the Microsoft text database driver (as opposed to XLS files). By default it will recognize text fields (limit at 255 chars).

To contain more data, the field needs to be detected as a memo field. This can be enforced by using a schema.ini file that resides in the same folder as the database.

You can create it using a text editor, and should be formatted like this:

[test.csv]
ColNameHeader=True
Format=Delimited( wink
DecimalSymbol=,
CharacterSet=ANSI
Col1=myfieldname Memo

Where 'test.csv' is the database name (multiple entries can be made in the same file)

In this sample, Col1 states that the first column in the database has the name 'myfieldname' and is a memo field.

As you can see, other options can be forced, like ANSI encoding and the text delimiter ( ';' in the sample)
Posted by: Adam Delaney

Re: Data Truncation from csv and xls file - 08/24/09 08:55 AM

I tried this and received an error,
"In the text file specification 'jit_081909.csv', the format option is invlid."

this file was saved from xls to a csv file, any ideas?
Posted by: Anonymous

Re: Data Truncation from csv and xls file - 08/24/09 10:58 AM

Hi Adam,

Can you send your data file (or a sample) to support@printshopmail.com with a reference to Floris Dansen?

Thanks,
Floris
Posted by: Anonymous

Re: Data Truncation from csv and xls file - 08/26/09 04:38 AM

I sent you the file by e-mail, but to not keep the solution from other visitors, I'm posting it here as well.

A schema.ini file would work, but needs to be built very specifically - every field name needs to be manually assigned. (I 'anonimized the info below)

[data.csv]
ColNameHeader=True
Format=CSVDelimited
DecimalSymbol=.
Col1="Fieldname 1" Text
Col2="Fieldname 2" Text
Col3="Fieldname 3" Long
Col4="Fieldname 4" Text
Col5="Fieldname 5" Text
Col6="Fieldname 6" Text
Col7="Fieldname 7" Text
Col8="Large field 8" Memo
Col9="Fieldname 9" Text
Col10="Fieldname 10" Text
Col11="Fieldname 11" Text
Col12="Fieldname 12" Text