using database name as data in newly appended column

8 posts / 0 new
Last post
jszzzzz
Offline
Joined: 12/12/2017 - 02:19
using database name as data in newly appended column

hi bryan,
So I have this data sources that are generated on a monthly basis by the system and before I appended all these together, I need to differentiate them by months for analysis purposes. The problem is, the only way to identify the month this report is by their filename. In the past, I've manually added this identifier to the file one by one by appending a new column within each database and specify month of analysis in the equation editor, which I found to be tedious. So I'm now thinking of uploading all of them using the upload multiple file script and add this appending function into the script. However, I can't seem to get the equation right for the life of me. I realized this might be because the database name is at different level than the data and hence I can't call the database name in the field equation. Is there a workaround to this?

Images: 
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hello jszzzz and welcome to the site.

So is the equation you are having difficulty with the

Right(dbName, 6) ?

This will give you the right most 6 characters of the dbName but I see the length of your field is 150 characters which is too long if you only want 6.  

So I take it that the month is part of the filename?  Can you give me an example of the filename so I can see what function would best help you extract that information.  You can put a msgbox dbName before the equation so you can see what it looks like and maybe do a screen capture.

Brian

jszzzzz
Offline
Joined: 12/12/2017 - 02:19

Hi Brian, 
Thanks for your reply. Yeah that's the equation that I'm having a problem wit. I attached here the example of the file name.
Thanks so much for your help!

Images: 
Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi jszzzz,

The only thing I can think about is that the dbName also contains the extension .IMD so try this equation also I just realized that you need the double quotes as you are inserting the results of your equation.

Chr(34) & Mid(dbName, Len(dbName) - 9, 6) & Chr(34)

Hopefully this will fix the problem.

Brian

 

 

jszzzzz
Offline
Joined: 12/12/2017 - 02:19

Hi Bryan,
Yes, it did! Thanks!!

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Glad to help.

laura.garner
Offline
Joined: 09/04/2014 - 19:12

Thanks Brian! A few years on and these resources are still being used. This snippet was incredibly helpful to me today, thanks!!

Brian Element's picture
Brian Element
Offline
Joined: 07/11/2012 - 19:57

Hi Laura, thanks for letting me know, that is great to hear!!!