A question that I’ve gotten several times in the last few months is:

I see that SmartConnect supports output to CSV or TAB delimited formats – but can’t it output to a fixed length file?

Well on the face of it – the answer is no. If it could do that out-of-the-box, it would be listed as an option and currently we see that csv/tab are the only formats that we can choose.

But what about a non-out-of-the-box solution?

Thinking about it a bit, the answer would seem to be yes there is.

What? How?

If we think about a fixed length file, how many “columns” does it have? Just one.

So if we extend that idea, could we not have a comma or tab delimited file with one column? And if we only have one column – would it really be a “delimited file” if there isn’t a second column?

So in this case, the solution to our issue is to create one formatted field that would then be output into the one destination column. Since we don’t have two columns in our output map, it wouldn’t matter our choice of tab or comma delimited since they won’t be exported.

In this example, the goal is to export some payroll information out of the GP UPR30100 table from the TWO database.

After creating a new map, we use an ODBC DataSource and the SQL query to pull the source data.

 

SELECT top 10 [dbo].[UPR30100].[CHEKNMBR], [dbo].[UPR30100].[CHEKDATE], [dbo].[UPR30100].[EMPLOYID], [dbo].[UPR30100].[EMPLNAME], [dbo].[UPR30100].[GRWGPRN], [dbo].[UPR30100].[FDWDGPRN], [dbo].[UPR30100].[FICAMWPR], [dbo].[UPR30100].[FCASWPR], [dbo].[UPR30100].[TTLDDTNS], [dbo].[UPR30100].[TTLBNFTS], [dbo].[UPR30100].[NTWPYRN], [dbo].[UPR30100].[EFICASSWH], [dbo].[UPR30100].[EFICAMWPR] FROM [dbo].[UPR30100]

 

where CHEKNMBR not like ‘DD%’

Figure 1: Creating the DataSource

 

Now that we have the source query, our destination will be an Export to File destination. The File Type shouldn’t matter since we only have the one column but I chose Csv file.

In the calculation we will return one big string with our data fields formatted as fixed length with either leading or trailing characters for our fields.

Let’s look at the calculation:

Figure 2: Fixed Length field calculation

return _

_CHEKNMBR.Trim().PadLeft(21,“0”) +

_EMPLOYID.Trim().PadRight(15)+

_EMPLNAME.Trim().PadRight(41)+

_CHEKDATE.ToString(“MM/dd/yyyy”)+

CDbl(_GRWGPRN).ToString(“0”).PadLeft(15, “0”)+

CDbl(_FDWDGPRN*100).ToString(“0”).PadLeft(15, “0”)

If we look at the calculation, we can see that it uses PadLeft() and PadRight() functions in order to pad the data field.

The parameters of PadLeft() or PadRight() are:

Param 1: Pad length
Param 2: Optional, character to pad with. If not passed, a space will be used.

So looking at the examples above, the “number” fields including the check number will all have leading zeroes on them.

The date field didn’t need padding at all – it is 10 characters formatted as MM/dd/yyyy.

Lastly, the two string name fields will be padded right with spaces. I also used the Trim() function to make sure there aren’t any extra spaces before/after the field name. Since the data is coming from GP SQL data, the field would have trailing spaces on it which wouldn’t matter in our case since we do want to pad with spaces. But trimming the field ensures that the PadRight/PadLeft comes out to the correct number of spaces.

Looking at the Mapping window, we only need to map our calculation.

 

Figure 3: Mapping the calculation the output

We don’t have to remove the extra columns two through seven, SmartConnect will do that for us.

We press the OK button to save our mapping and then run the map and view the results.

Figure 4: Output

I’ve highlighted every other column so we can more easily see how the output file matches up to the fields exported and verify it is correct.

The number fields do have the leading zeroes added to them like we wanted. We also see that the two string fields are also padded with spaces at the end as we desire. Lastly, we note that there is no trailing comma which is as expected because there is no second column of data being output.

So the export file does appear to be according to specification and we’ve proven that SmartConnect can export to a fixed length output file.

You can download a zip file containing the map definition and a sample export file CLICK HERE TO DOWNLOAD ZIP FILE.

Best Regards,

Patrick Roth
eOne