Importing multiple text/csv files without using the RDF file.

Smiso

Forums

Hello Everyone
 
I hope all is well.
 
Is it possible to import many text or csv files at once without using the RDF file?
 
The resean im asking this qeustion is because, I have been trying to import multiple text files into IDEA, however, they come out incorrectlly, due to each file having a different number of fields. For an example, I have  different text files with 10, 6, 12. 3, 20, 150 etc fields. So, if I create the RDF file using a file with 3 fields, and then import all files using that RDF, what happens is that all files will only contain 3 fields after importing into IDEA. Is there a way to avoid this situation. Ideally, IDEA should import each file as a unique file with its own and correct number of fileds and related data, as an accurate copy of an original data file.
 
If there is a way to work around this, please kindly assist.
 
Thank you.
 
Smiso.

Smiso Wed, 12/11/2019 - 05:49

Hi klmi
 
Thank you for your response.
 
The issue with using the the RDF file is that if I use a file with 150 fields to make it, IDEA will also create empty columns up to 150 for files that have less columns, such as the file with only 6 columns. The same happenes if I use the file with 6 columns to create the RDF file, what happens is that every file imported will using that RDF file will only have 6 columns.
 
The lines of code on the first link you shared above, posted by Brian, will not work since i have a lower version.
 
I just need a blanket RDF file that is independent of the number of fields a file may contain.
 
Thank you.

klmi Thu, 12/12/2019 - 09:56

Have your import files any kind of structure whereby you can recognize if it has 3, 6 … 150 fields?
How different is the field structure - f.e. are first 3 fields in a 6 field file the same as in a 3 field file …?
Can the field names be found in the first line of the csv-files?
How many files you need to import?

Possible solutions:
1) Make separate imports for the files with 3, 6 … 150 fields
2) Import all 150 columns and delete empty columns by the following script
http://support.audicon.net/index.php/idea/idea-aktuelle-downloads/doc_d…
3) IDEAScript:
3.1) analyze csv-file with basic VBA functions
3.2) generate RDF by script
3.3) import data with the generated RDF
In detail the third solution is very complex and requires some programming expertise.

Smiso Sun, 12/15/2019 - 12:12

Hi Klmi
 
Thank you for your response.
 
Im not sure if i understand your first two questions, but ill try to answer. I have 122 csv files. All 122 files have different number of fields in them, these fields range from 3 to 150 in a particular file. All 122 files do not have headers, so IDEA create defualt field names upon import, depending on the imported field recognised data type. However, one thing to note is that, when im importing these files i need to change the first field data type to character, since IDEA recognises it as numeric, if you leave it as numeric, the type does not work for some of the files, because some of the files are containing characters in their fisrt field.
 
If there is a possibility for possible solution number 3 (IDEA Scripting), please kindly assist with the scripting, im not a programmer or a systems engineer, im simply an IDEA tooI user with challanges, trying to work around the tool. If there is anyone out there who has the time and capability, please kindly assist. 
 
Thank you.

klmi Mon, 12/16/2019 - 07:52

Well, importing 122 files is a lot but I mean scripting would likely need more time. Furthermore that your files have no headers isn't very helpful. In conclusion the last solution isn't as good for you as it sounds. I think nobody would code that for free for you! If necessary you have to learn to write scripts by yourself. 
My first two questions weren't answered! I'll try to describe what I mean with other words:
- Do your filenames have a special naming scheme, so that you know before importing which files have 3, 6 ... 150 fields? 
example: filexxxaaaxxx.csv (all files with 3 fields have "aaa" in filename) , filexxxabcxxx.csv (all files with 6 fields have "abc" in filename)
- If we look on files with more than 3 fields, do the additional fields contain only supplementary information or a completely different structure? 
example: ID, FIRST_NAME, LAST_NAME (3 fields)
ID, FIRST_NAME, LAST_NAME, BIRTHDAY, STREET, CITY (6 fields - supplementary information)
ID, BIRTHDAY, STREET, CITY, POSTCODE, PHONE (6 fields - different structure)
 I guess you need to import each kind of file (3, 6 ... 150 fields) at least one time manually -> save RDF files for 3, 6 ... 150 fields. In case that files with 6...150 fields contain supplementary information you will need only one import with all 150 fields -> save RDF file. Maybe I can help you that IDEA makes a batch import for the remaining files so that you don't need to do that manually, too.

Smiso Mon, 12/16/2019 - 15:51

Hi Klmi
 
To answer your questions, the file names are unique and the data they contain is also unique. So, the fields are not prograssive like in your example, as we open more files. Grouping files by the number of fields they contain will not necessarily help in this situation, in my opinion. A required solution here, is a solution independent of what the names of the files are or what fields are contained within a certain file, we merely want to import all of these files using a common delimiter. Thus, the solution should only import the fields that are only contained within a particular file, just like how IDEA would do it if you were to import each file manually, one by one. Well, i could learn how to code, but the process would be accelerated if someone already capable were to assist. If someone can do it, but not for free, its alright, we can negotiate the price and get this thing going.
 
I hope the above provides more of a clear picture.
 
Thank you. 

Brian Element Tue, 12/31/2019 - 10:56

Which version of IDEA are you using?  If 10.4 or later check out the CSVDefintion in the language browser as this might help you create a generic file import for your files.

Smiso Sun, 01/05/2020 - 05:28

Hi Brian
 
Thank you for your response and happy new year.
 
I am using Version: 10.1.6.3. 
 
I will try to figure it out on the Language Browser.
 
Thank you.

scotchy33 Tue, 01/21/2020 - 15:18

Hi Smiso,
I have added an option to the Import Multiple File Script (Text Delimited (no Record Definition 'RDF')).
https://www.ideascripting.com/ideascript/import-multiple-files?page=1
The script should be able to automatically detect what the delimiter is in the file, so in most cases you can leave blank.
I have used the RDFCREATORLib in the macro.  I have not tested this method or macro modification, so please post your results and/or feedback.
 
Scott