Append files

Stedemi

Forums

Dear Brian,
quick question about appending.  I have  four databases with 178 columns each (same headers) from 4 different systems. I have tried to append them but keep getting the message that fields are not in the same format. You can only imagine the pain of checking all fields on all dbases. I wonder why Idea's guys don't provide more infos on ALL inconsistent fields.  I had to use excel and then import
is there a way to  take one Dbase as base and have idea compare and change/adjust the fields of other dbases? Or at least provide a list of not-identical fields?
cheers
 
Stefano

Brian Element Fri, 04/11/2014 - 07:13

Hi Stefano, I think anyone who has ever had to append multiple files has come across this problem.  It is quite frustrating when you have many files to append and the task stops because of the same filenames but different field types.  I do have this on my todo list to create a script to help this out, unfortunately I haven't had the time to work on it much.  Maybe after this post I will bump it up the ladder and try to complete it sooner than later.

So to answer your question right now there is no way.  IDEA would have to update their product or someone create a script to do this.

Stedemi Fri, 04/11/2014 - 09:07

Hi Brian, 
indeed it would be  a nice feature to have. Hopefully with v10 coming next year
cheers
 
stefano 

Steven Luciani Fri, 06/19/2015 - 11:06

Hey Brian,

Did you ever get to this part of your tod0 list? I've imported 640 XML files from a POS and have many field names with different field types across many files. Your script would sure come in handy right now.

Brian Element Sun, 06/21/2015 - 15:57

Hi Steve,

Do you want to test this one out. What it does is change all the fields (except for virtual and editable fields) to character and then does the append.  You can then change them back to their original field type.

I tested it on a small number of files with no problems, let me know how it works on your 640 XML files.

Thanks

Brian

Steven Luciani Mon, 06/22/2015 - 09:45

Thanks for the quick reply Brian. The script is hanging at line 259 right where it is trying to populate the list of files from the folder I chose and display them in the dialogue box you created for the script. I'm only running IDEA 9.1.1.152 if that makes a difference.

My first run was on all 640 files so I copied 20 of them to a new folder I created in the project just in case it was a file amount limitation. However, I received the same error on just the 20 files.

Suggestions??

Brian Element Mon, 06/22/2015 - 09:57

Not off hand as the error seems to be showing in the dialog.  Any chance you can share the files so I can test them out or have example files that give the same error?  You can send them to my email.

subangh Wed, 06/07/2017 - 01:41

Hello Brian,
This is Subangh from India and i'm quite new to IDEA. Can you help me with a script that would open a dialog box to select Multiple Excel files and append them together and store it in the database? I tried stitching together the import multiple files and append files code but to no luck. I am using IDEA 10.2. Thanks in advance.

Brian Element Fri, 06/09/2017 - 13:50

Hi Subangh,

I just tried your script and it runs as advertised so that is great!!!  A few things I might suggest:

  • Will your Excel spreadsheets have more than one worksheet you want to bring in?  If they only have one they you don't need the section on reading the Excel worksheets as IDEA will bring in the first one automatically.
  • Instead of creating an append each time you do an import, import all the files, store the names in an array and then append them all together in one pass.

Nice work.

Brian