Access & Concatenating Memo Field

[dlohan]

Hi,

I was wondering first of all if Kirix data reporting tools work with MS Access, especially 2003 and 2007?

I have some data in a table which is made up of numbers, text and memo fields. I need to produce an output file for upload to an electronic commerce system. This uses a tilde (~) separated file. For example the field structure is like this -

ID~Title~Price~Notes~1~The Prince~15~This is a great book with many useful insights.~2~Moby Dick~12~A much celebrated work of fiction.

In order to achieve this result I need to concatenate data in a record and introduce the tilde character between fields. In also need to concatenate the records with one another so that they occur, not separated by a carriage return, but on a single line as continuous text. In other words there needs to be a capability to concatenate records with one another.
Furthermore I need to be able to export Memo fields, now combined with text and number fields, without any truncation. Access does not facilitate this. Instead when you concatenate records of different types it atributes a Text type to the resulting field. When the resulting Text field is Exported the data contained in it is truncated to just 255 characters.

Can Kirix help me achibeve my objective?

[Ken]

Hello there,

Let's go through each of the issues:

1) Kirix Strata will be able to open up *.mdb tables or, if you get in a pinch, a csv file exported from access. Go to File > Open and browse for your *.mdb, then click OK. Strata will then import the table and should bring in the existing structure OK (though memo fields, I believe, will be changed to "character" fields).

2) You can export from Strata into a tilde-separated file without a problem. Go to File > Export; select the File tab and "text-delimited", then hit Next. There, enter a ~ into the "other delimiter" box and edit the qualifier as necessary (you'll want to see if the ecommerce system requires some type of qualifier like " " around the string). Your file will export with records on each line; unfortunately, you're not going to be able to get a string of records without line breaks. That said, I'd check with the ecommerce system requirements again. It seems odd that a system that requires a text-delimited file would require a file without something that enables it to know when a record is terminated. I'm not sure how it would otherwise do this (accept, I suppose, counting tildes). Anyway, though possible, it's seems quite out-of-the-ordinary.

3) If you need to concatenate multiple fields prior to export to get it into a single field, that is no problem using a calculated field (right-click on any field header and select Insert Calculated Field). More on using calculated fields are here: http://www.kirix.com/help/video-tutorials.html Once you're set, you'll have to make the calculated field static (so that it won't rely on the other fields any longer) and then delete the old fields prior to export -- so you can have a clean data file for uploading. If you need help on making calc fields static, just let us know.

4) Strata doesn't have a "memo" field for exporting (you would use a character field, which accepts numbers and symbols within it). However, that said, if you are using a tilde-separated table for uploading, there are no "structural" elements embedded in that type of file anyway -- so your ecommerce system should just be able to take the raw data and inject whatever structure it already has set up in it.

5) Strata won't limit the number of characters in a given field to 255. The max field width in Strata is theoretically around 4 billion characters (though, I wouldn't recommend trying this . Bottom line, you're not going to have an issue with truncation in your export.


So, Strata should deal with everything except with the record concatenation. Again, I'd check with these requirements to make sure it is something you actually need. As a workaround, if you're working with an IT department, you may also get the file to exactly where you want it and then see if they could write a quick script to remove the line breaks for you.

If you need further help, please let us know.

All the best,
ken