Fixed Width Export

tmschmitt, thanks for sharing your scenario. I already started working on the different exports. Yesterday right before I went home my boss told me that I should not worry about a fancy interface that I should get those exports out so they can be delivered to the client, I "ironed out" the first one now I am moving smoothly. tmschmitt, your thoughts are very useful, even though we don't have to it does not hurt to my company to have that small utility to handle this kind of exports. It was also a good experience for me because I had not worked with that data format before.

Ian, thanks for your feedback and expertise in this matter. I came up with a good solution and I will get the job done! I appreciate that!

Thanks a lot!

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.

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.

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!

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.

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!

apocalipsis19 wrote:
> I am still stuck in this one. I'd appreciate any example or further ideas. Ian's ideas were great to get me started!


Well how are you stuck? It is rather challenging to help, when one does
not really know what to help with.


The basic concept is not much more then this.

<cfset simpleDataLine = '01012008'>
<!--- month is 1:2
day is 3:4
year is 5:8 --->

<cfset month = mid(simpleDataLine ,1,2)>
<cfset day = mid(simpleDataLine ,3,2)>
<cfset year = mid(simpleDataLine ,5,4)>

I am still stuck in this one. I'd appreciate any example or further ideas. Ian's ideas were great to get me started!

Thanks Ian, I will start working on this and I will let you know what I come up with!

apocalipsis19 wrote:
> I need to write an interface that extracts data in fixed width format.
> Can somebody provide me with some feedback? Any ideas? Any example I can follow?
>
> Thanks a lot!
>


Fixed width is not that big a deal, once you have the map on where each
piece of data starts and stops.

You then just take one line of your data and use simple
mid(line,start,length) functions to grab each desired element from the line.

If you are writing out a fixed width or column delimitated data file,
you will want to make liberal use of the rJustify() and lJustify()
functions to ensure all the widths are placed properly.