Prev: CFPDF and DDX instructions
Next: CFExchangeCalendar Get Recurring Events
From: apocalipsis19 on 15 Apr 2008 14:25
I was just stuck on how to treat the records that my query returns. I got a
better idea now. In the past I created an export so I could generate CSV and
HTML Spreadsheet output. I was making the fixed width format more complicated
than it has to be.

The second piece is that I want to create a function that I can pass any query
to and it dynamically grab the query and turn it to a fixed width output. I
have 7 queries in my sandbox that need to be turned to fixed width output
files. I know this definitely makes things more complicated because I'd need to
figure a way to find out the length of each field (which in fact I know
beforehand thanks to the spec he,he). I know this is doable I just might ignore
the complexity of it.

Ian, I am sorry about not giving the details where I was stuck in but I think
this post is more detailed!

Thanks in advanced for you thoughts!

From: Ian Skinner on 15 Apr 2008 14:32
apocalipsis19 wrote:
>
> The second piece is that I want to create a function that I can pass any query
> to and it dynamically grab the query and turn it to a fixed width output. I
> have 7 queries in my sandbox that need to be turned to fixed width output
> files. I know this definitely makes things more complicated because I'd need to
> figure a way to find out the length of each field (which in fact I know
> beforehand thanks to the spec he,he). I know this is doable I just might ignore
> the complexity of it.

That is a good place to start. With fixed width, the spec is the king.
You can't really inspect data from a query and auto generate a fix
length record unless a lot a work was done up front to make sure the
database always returns what is required to match the spec.

I would always be concerned that improper data in the database could
easily blow up the fixed length spec with an interface like that.

I think the easiest would be to build a special purpose function that
generates one specific type of fix length data according to the spec.
Once you have one ironed out, you can consider ways that you may be able
to re-factor this code to make it more universal and reusable for
different types of records.

For the latter case you will need to consider some mechinism to letting
your function know what the different fix length specifications are, so
that it can match the data to the specification and probably report
exceptions somehow if and when the data does not fit the specification.
From: apocalipsis19 on 15 Apr 2008 15:30
Ian, it makes sense to me. I have the spec for each of the seven files my
interface needs to generate. Some of my colleagues in the software development
department told me they haven't had a situation where a client needs this kind
of export for a while, I guess it's an old thing. Anyways, I really appreciate
your help and I will go one step at a time so I can meet the deadline of this
project and don't fall behind in other stuff.

I will create my first function to work with the first file, then as I become
familiar I will twist it around and around and see what happens. I will let you
know of the outcome. Thanks for your help and patience, I deeply appreciate it!

From: Ian Skinner on 15 Apr 2008 16:13
apocalipsis19 wrote:
> Some of my colleagues in the software development
> department told me they haven't had a situation where a client needs this kind
> of export for a while, I guess it's an old thing.

Yeah. The fixed length aka column delimited data file dates from the
days when bandwidth, memory and storage space was expensive. All those
commas in a CSV format add up when one is dealing with tens and hundreds
of thousands of records or more.
From: tmschmitt on 15 Apr 2008 16:53
I have a similar scenario where I create billing files each month. They are
fixed width formatted.
I kill 2 birds with one stone...
1. First I create a permanent storage table in the db for each extract file.
(There may be years of data in each table, as there are identifiers for each
month) These tables serve 2 purposes.
a. It keeps the data for historical purposes and...
b. Each column in the table is a char data type set to the proper width that
is required in the extracted file.
2. I now run the query to collect the data and insert it into the storage
table.
3. I then run a second query that pulls the data from the storage table to be
written to the file. You don't have to worry about the width of the column at
this point in your CFML, as the data is padded to the proper width due to the
char data type. Simple. All you have to worry about is getting the columns in
the correct order.

Steps 2 and 3 can be run consecutively or step 2 could be a cron job and step
3 could be on-demand. It doesn't really matter. The main point is utilizing the
char data type.


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: CFPDF and DDX instructions
Next: CFExchangeCalendar Get Recurring Events