Hello everyone,I need help, I need to import/upload a text file that loads all the data in the table, except the header. I have preferred header, so I cancel out the header coming from the import file.Note: It can't affect the output file*Please see the attached example text file below*Here's my scenario:1. Sample text file, I only need the data that start with a number, what I mean is disregard the header (H 1 S SMIC S1MIC000.C53 20230331):2. My logic on the service side of the input file:
3. My logic on my interface or the client side:
4. On my case, I only import a ".xlsx" file, I need also import a text file too. When importing/uploading the file it automatically loads all the data in table:
Thanks,Ally
You can check in service center there must be error logged for index issue. Problem was in your text file last line did not matched the format which is why error. (which is why highlighted before if we get any value not in format it might create issue)
Assuming this is not part of row data then we can remove the last row (same as did for header) and import. It works, imported all records from text file, see screenshot. Attached the updated OML file.
@A L L Y , Attaching the updated code. There must be some issue. You can always check in service center, there must be error logged why data didn't got updated in table.
Hi @A L L Y ,
Created this sample demo(with same type of text format file you shared). You can check and try to implement on your side. To remove the header, make sure you add Start Index 1. Hope this helps.
Hello @Puja Rani,I have an error; I'd just follow your work and my error says:
I have updated the OML file to work as per your text file. Pls check. (However, it is hard to maintain like this since your text file does not have proper delimiter/separator to identify different column value. For eg- SLA Savings, this is one value. In future lets say you had two column with Text & has space in text so how to identify which column value. Not sure if I was clear here to make my point).
This might not be the optimal solution in a long run and as Kilian pointed you might need to create extension of own.
@Puja Rani It works but it didn't load the data on the table:
@A L L Y Did the data got inserted in the table? Please cross verify the logic of insert and after upload check the logic if you have the table refreshed to show on UI after successful import.
@Puja Rani,Yours too, it didn't work to load all the data on the table:
@Puja Rani,I already check and verify it. Also, I have refreshed on my logic same as you did. I'd just follow your step. Where the data must get inserted in the table.
Okay, I tested with 3 rows insert it worked yesterday. Let me check , will get back to you.
@Puja Rani,Okay I'll wait your update. May I ask, maybe can you complete all the rows same on my example?Thanks,Ally
Using BinaryDataToText and then String_Split is not a proper solution. It may work, but with large files you'll quickly run out of memory, crashing your server. Also, NewLine() may not be sufficient for splitting if there's both a CR and LF as new line indicator in your file (which is standard Windows). If this is for a one-time import I'd say it's fine, but for a proper solution you need to do it differently.
Hello @Puja Rani,Many thanks sir! It worked on my end, I really appreciate your time and effort. Again, Thank you very much sir!
You're welcome :) .. Glad to know your issue is solved.
Hello @Puja Rani,Please see the attached sample file belowI need your help please for one last time, I have a problem when I import a file like this:I faced an error, and the error says:Many thanks,Ally
This text file has a comma separated values. Are you trying to import this file using the same code given before? If yes, then it won't work. As mentioned before, in code we have specified the separator and index value based on text split have been mapped to respective column. so here did you changed in code the delimiter and index assign value?
Hello @Puja Rani,Yes, I'm trying to import this file. And, yes, I actually did a changed in code the delimiter and index assign value. I don't know what to do, I do a lot of changes and same error. Please help me.
@Puja Rani,I try your work but when I import my file it says done so it means it worked? right? but there's no data to be stored in table also in database:
@Puja Rani,It worked! Thank you very much!I have a last question. The import file widget will accept this kind of file type "F43 File"? Then, what if, if will not accept that kind of file type?
Ok, so I just had a quick test. It accepted this file type and was able to import.
@Puja Rani,Okay great, I will try it on my end. I really appreciate your help and kindness. Thank you for your patience and understanding!
@Puja Rani,Sorry to bother you, I need your help on your logic. When you import the file where the sample data under the Total Amount column was like this "200.00 or 500.00" but when I import the file also after being save on the table there's no ".00" on the Total Amount, the output data it's only "200 or 500" basically there's no ".00".I need the output like this: 200.00 or 500.00 even if the decimal value was ".00" and need to implement on the table client side and server side.Please help...
For trailing zero for decimal values, as per this post it says OutSystems doesn't handle nulls, instead it has always a default value based on the type .The solution was to use attribute as Text datatype and treat like decimal, then it will work. However, I also tried with FormatDecimal() function but it didn't seem to work . But as per this post , it was marked as solved, so I am not sure.
Also let's not use this thread for other issues, you can open a new issue and follow up there.
Regards,
Puja
@Puja Rani,Yeah, I thought too, it didn't work because at first place the value of ".00" the outsystems consider it as null. Correct me if I am wrong. Yeah, I already do a lot of possible ways but does not go well.Anyways, thanks a lot!
Yes, since the column was decimal and if you expect to save in db as is in the text file, it won't show like that. OutSystem consider that as null. If you want to save on server side same like as in text file then you can change attribute Total amount to Text data type. But if it is just about showing on screen with trailing zeros, then we can achieve using FormatDecimal function
Hi Ally,
What are you using to read the text file and import every line? There's no out-of-the-box functionality in OutSystems to do that, so if you want to import text files, you need to either write an extension yourself, or use one from the Forge.