By using this website, you agree to our Terms of Use (click here)
Good day, I am trying to create an import scenario and/or data provider that can be for a fixed-field length .txt file. I have to import a file every day, but I was asked if this can be done by using the file in its original form. Attached I put an image of the file. Is there a way to either create a new Data provider or use one that is already in Acumatica? Thank you and have an amazing day.
Indeed there is no default data provider type for fixed text. The only nasty way i can think of is use the CSV data provider type, import each record as one long String field called "Data". Then in your import scenario, use functions instead of fields. Use the Substring function to extract the data for each field, and wrap that with a Cxxx function, to convert the string to the correct field type.
So suppose the first field is the 0001 above, you might use:
=CInt(Substring[Data],0,4))
This is only a theory.
I just noticed a typo, it should be:
=CInt(Substring([Data],0,4))
@roycelithgo How would you go about setting up the schema on the Data Providers screen? Do you use your code to refer to where each field is located per line or that is only used on the import scenario screen?
@jswartz As i said in my comment, import as one long string and extract the data in the import scenario. The schema would have a single field of type string with length as the maximum expected record length.
@roycelithgo I think I understand what you are saying now. I am trying to automate an AP Bills import with a fixed-width file type. I have attempted to try and first see if I can import the PO number but I am getting an error during the prepare phase. I will post photos of my setup for reference. Thank you again for your help with this import.
@jswartz You need to include a header record in your input file. Use the word "Data" for the record.
example:
Data
ABC123DEF
ABC456GHJ
In my test case I could prepare this file with the following outcome:
@roycelithgo Thank you! I now have the data populating. However, I am trying to separate the columns based on their width using the formula you provided above. However, it keeps adding the whole line into one column when it should be separating it with the function based on the field I designate. Photos for example.
@jswartz The formula will only be applied during the actual import - so when you prepare it, you will see the entire value as one column but when you import it should split it based on the formulas. Did you actually try and import the data?
@roycelithgo I was able to resolve the issue and get it to work! Thank you for your help.
You might also checkout this product for an easier way to work with fixed-length files:
Online Members
