EONE BLOG

Creating a fixed length output file in SmartConnect


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

Leave a Comment





RECENT POSTS


Tech Tuesday: Making sure GP is active for SmartPost
Throwback Thursday: Troubleshooting Scheduled Maps
Employee Spotlight: Sandy Westman, Senior Consultant
eOne Offices Closed for Labor Day - Monday, September 7th
eOne Presenting at DynamicsCon 2020 - Free Virtual Conference

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

ARCHIVES

open all | close all

TAGS

Business Central CRM Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartView SOP SQL Support Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.