Appending Data files to Header files that are not transposed

Smiso

Forums

Hello everyone
Can somebody please assist if this is possible. I have over 100 files with data and 100 coresponding files with headers (column names of the data files). All 100  data and header files are in different names, but sharing names as pears. The header files have all the headers in one column (all headers are in rows). Thus, I was wondering if there is an IDEA script or way to traspose the header files first and then append each trasposed header file to its corresponding data file. Please help if possible.
Thank you. 

Brian Element Fri, 11/15/2019 - 08:22

HI Smiso,

I am not sure I fully understand what you are trying to do.  The problem with doing appends is that it is based on the header field names, so if your data is a different name you can not use the append for this.

If all your files use the same header names then I would suggest doing one by hand, that is rename each field and then go to the history, create a script and just change the name of the file, now this assumes that each of your 100 files uses the same field names are in the same order which I am not sure is true from your explanation.  

Maybe give an example or two of your files so I can better understand what is going on.

Thanks

Brian

Smiso Sat, 11/16/2019 - 16:14

Hi Brian
 
Thank you very much for your response.
 
Apologies for not being clearer. Unfortunately all header files have different field names listed in each of the 100 header files, on the first column. If I have 2 to 4 files (2 data files and corresponding 2 header files) i was going to do this manually, by transposing the headers files perhaps using the pivot function and export them. After that upload the data files and header files by not say take the first row as field names, that way COL_1 in data files will corresponde to COL_1 in header files. At this stage i can append both pears using the COL_ columns IDEA created. So, the challenge for me is that i have too many files to do this mannually.
 
Unfortunately the files are confidentially, so i would have liked to show you, however, i cannot. I hope the detail above provides more clarity.
 
Thank you.

scotchy33 Mon, 11/18/2019 - 09:22

Hi Smiso,
Please find the attached script, which may do what you are requesting.
Let me know how you make out.
Scott

Smiso Mon, 11/18/2019 - 12:10

Hi Scott
 
Thank you very much for your help.
 
I have tested the above attached script, however, I dont think that the script is going to work for my objective, due to the fact that the scripts only allows for you to change the data files one by one. So, the trouble is that, what if i have 1000 data and 1000 header files? Moreover, the script is breaking at line 222, complaining about an invalide name, Im not sure what is the issue.
 
Please dont be discouraged, i appreciate any assistance. Please fell free to assist with whatever solutions you may have or may think of.
 
Thank you.

scotchy33 Mon, 11/18/2019 - 13:47

Hi Smiso,
The script would have to be modified.  The revised macro would allow you to select the IDEA files (.IMD) that need to be renamed.  For each .IMD selected there would be an equivalent record def .IMD.  The naming of the files would have to be consistent (i.e. General Ledger.IMD > General Ledger RDF.IMD, Journal.IMD > Journal RDF.IMD, etc). It would be a fun project, but I don't have time to look at it now.  Also, it would help if you uploaded some sanitized files.
Scott

Smiso Tue, 11/19/2019 - 01:56

Hi Scott
 
Thank you very much for the interest and your help.
 
I would very much appreciate it if you were to find some time and modify the script to achieve the objectives outlined on the above correspondance. I understand that you may be very busy, so, whenever you get sometime you can look at it. I think another challenge that could come into play would be to make the data and header files to be in consistent names, beacuse again, what if i have a 1000 files, does that mean i will have to changes their names for the revised script to work? i have uploaded the screenshot of my actual data just for you to get a feel of what im dealing with. The screenshot is showing a few, but actually there are more files. You also requested for the sanitised files, thus, what i have done is that i have created dummy files similar to my situation. Please see the zip for the sanitised files.
 
I hope to hear from you soon. please let me know if you require more clarity.
 
Thank you.

scotchy33 Thu, 11/21/2019 - 18:40

Hi Smiso,
Try the attached macro.  Please follow naming as seen in attached screenshot. Let me know how you make out.
Scott

Smiso Sun, 11/24/2019 - 04:06

Hi Scott
 
I hope all is well.
 
Thank you very much for the help.
 
I have tested the script however, i was able to use it, because after setting everything up as you said, I get the attached error/massege uplon clicking on the "Filed Name Dictionary" folder. If I click on yes or no on the pop up box, the "Filed Name Dictionary" gets filled with IDEA folders as if it a diferent project now. Perhaps I am missing something, please assist and clarify.
 
Thank you.

scotchy33 Sun, 11/24/2019 - 13:57

Hi Smiso,
Don't set the working folder as "Field Name Dictionary". Just have the folder in the working folder.  The file explorer window should look like the picture I posted above.
 
Scott

Smiso Mon, 11/25/2019 - 01:21

Hi Scott
 
Thank you for your response.
 
I did exactly as per you screenshot. I opended the project called "Smiso" and then opened a folder called "Field Name Dictionary" inside the Smiso project. Under the Field Name Dictionary folder i put the header files and i put data files outside the Field Name Dictionary folder as per your screenshot. Upon running the script and following the dialog box, i had to pick the Field Name Dictionary folder where the header files reside, and thats is where i get the attached error and IDEA create another project inside the Field Name Dictionary folder.
 
Thank you.