Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday : SmartConnect – Changing the Column Names on a Text File Destination


One feature that has been asked for by customers is the ability to specify column names when using the Export to File feature.

By default, as of the latest SmartConnect 20.16.0.31, when exporting to a file is that the source file column names will be used as the output column names. This issue is potentially compounded by the fact that SmartConnect will replace “odd” characters in the source file column names with underscores.

So for example, if we use an Excel source file formatted this way:

Figure 1: Header row naming issues for SC

Notice spaces in Column B, the pound symbol in C, and the ampersand in D.

When we preview the data in SmartConnect, we can see that SmartConnect replaces those characters.

Figure 2:Spaces removed and characters replaced with underscores

And as anticipated, if we use Export To File, the names we see in the Preview Data window are used as the output column header names.

Figure 3: Output file with “wrong” headers

While that usually isn’t an issue, if this file has to be brought into an external system as-is, now the columns aren’t named what we need them to be in the output file.

Because we cannot directly configure the column names, the easiest way to make this work is to use a “Tasks that runs if the map succeeds” task script.

In the below screenshot, we see the code window that has the code to do make this happen.

Figure 4: Map success script

Looking at the code it:

  1. Sets the input file path and the output file path. There aren’t variables in SmartConnect to let us know the output file pathname so we have to hard code it in the map.
    In this example, I have chosen to timestamp the file with the current datetime.
  2. Next, we define the column names that we want to use. Note that the names are the same ones from the original Excel source file.
  3. Then it reads all the lines from the original source file. Doing it this way might take up a big chunk of memory if your original output file is very large – but it is a very easy line of code to write to do this.
  4. From there, we write the Header line to the file that we want to be the new output file. If we use the filepath variable we would overwrite the existing source file (which is fine if you want it named that way).
  5. Next once we have that header piece written, then write all of the data lines into that same file.
  6. Lastly, we delete the original source file so we just have the new output file.

At this point, the new output file with the correct column headers will exist and the original file will have been removed.

Figure 5: New output file with the correct headers

I would encourage my readers to vote for the suggestion to let us set the column names in SmartConnect. But until then, this method does work nicely and is pretty easy to do – especially since I did the hard work already.

Now you might notice that I didn’t address the situation if the Export To File option is set to “Excel 20XX”. That also comes up from time to time and is an interesting problem since we can’t just “read/write” like we can with the flat text file as this example uses.

You can download a zip file with the files used for this map HERE.

But that will have to be a topic for another day!

Patrick
eOne

1 Comment

  1. Tim on May 20, 2021 at 3:53 pm

    i can’t believe that changing the Column Names out of the box still isn’t an option. 🙁

Leave a Comment





RECENT POSTS


Join Us in Chicago at the Zendesk Showcase!
Tech Tuesday: Popdock - Joining the tale of two States
Meet eOne Solutions at Directions EMEA 2023!
Connect with eOne Solutions at SuiteWorld 2023!
What is a Data Lake/Data Lake Management?

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

TAGS

Business Central CRM D365 Business Central 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 Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships Partner All Hands Call Popdock promotions release Salesforce Salesforce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training Zendesk

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.