Forum Discussion

MaryPerkins's avatar
MaryPerkins
Qrew Trainee
3 years ago

QB export to ASCII Fixed Field Flat File

Hey All,

We're need to export a file from Quickbase on a weekly basis to import into a trans-edi system for billing. They are requesting an ASCII Fixed Field Flat file. I only see an option for CSV exports from reporting. I was going to export to a CSV but then use BBEdit to strip the commas. The problem I am running into is some fields such as name fields (also left justified) are not always the same length and two groups of number fields are right justified and also not the same length. How do I overcome these? Can I build a formula to create the filler spaces or is there a different format that I can export too? I tried requesting help from the Support Staff, but the customer care rep suggested that I request it via the Feedback form. 
I've attached the file format for you to review. We actually only use 16 fields, the rest I would have to add to Quickbase with the formatting. Please let me know your thoughts.


------------------------------
Mary Perkins
M3TR1CS Business Solutions
mary.perkins@m3tr1cs.com
------------------------------
Data import and export

3 Replies

Sort By
  • JimHarrison's avatar
    JimHarrison
    Qrew Champion
    3 years ago
    Hi Mary,

    All spaces are trimmed during export, so you would have to put a character into the field before export. You might be able to use a formula that adds a special character at the end up to a set number of characters. Alternatively using workato may be an option. You can also use something like BBEdit, Notepad++ or Excel and make a function, on your local device.

    I like Notepad++ because it allows me to see the ascii chars but that is the most manual process.
    I would build a solid process and then look at automation but I like to see the data and verify it is working properly before setting it free. 

    I don't know if Quickbase will give good results.

    Good luck,


    ------------------------------
    Jim Harrison
    transparency = knowledge + understanding : The Scrum Dudes
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Qrew Champion
      3 years ago

      You can make a set of padded mirror fields for each field. 


      If a field needs to be 10 characters blank padded, the formula would be

      PadLeft([my field], 10, " ")

      it will still export with commas which you will need to strip out using a tool or just Find and Replace in Excel. 




      ------------------------------
      Mark Shnier (YQC)
      mark.shnier@gmail.com
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago

        I now just understand what Jim was saying about the export stripping leading blanks.


        so we would need to do like this

        "|" & PadLeft([my field], 10, " ")

        then strip out the pipe | using a tool. 



        ------------------------------
        Mark Shnier (YQC)
        mark.shnier@gmail.com
        ------------------------------