How can I export directly to a txt file?
I have a report that is formatted with line breaks and I need to export it to a txt file. I can not use a csv or tsv because it wraps each record in quotes. How can I export directly to a txt file?
Forum Discussion
11 Replies
- QuickBase correctly formats and exports CSV and TSV files and they are text files. Take a look at the output from this URL referencing four records in the Formula Function Reference table:
h t t p s://login.quickbase.com/db/6ewwzuuj?a=q&query={3.EX.16}OR{3.EX.17}OR{3.EX.18}OR{3.EX.19}&clist=3.4.10&opts=csv
Records 17 and 16 contain multi-line text in the [Examples] Field which is correctly formatted.
More than likely whatever downsteam system you are feeding this into is not being used properly. I would investigate that as the source of your problem. - Thank you. Yes that is true for multiline but not if there are manual line breaks which have been added into the field. It add quotes to keep it one record.
- I am not following you here. I don't see any issue with what QuickBase outputs under any condition. If you have a workaround go with it but I still think the problem is with consuming the output from QuickBase downstream.
If you want to modify the raw output from QuickBase's export to make it conform to the exact format your downstream needs I am sure that is easy to do with script. But it really comes down to isolating exactly what characters and format your downstream process is choking on.
- Yes which is why I need to export direct to a Unicode 8 txt file. This is what my initial report looks like.
When I export to csv and open in Notepad I get :
I cannot have the quotes in my file. I can remove it manually with a find and replace but I shouldn't have to. - You have to get down into the dirt to debug this problem. I entered your sample data into an application and ran a script against it to spit out each character and its decimal ASCII code:
First of all, I am not sure I have exactly the same field input as shown in your diagram as there almost appears to be extra whitespace at the bottom of each of your [Contact] fields:
But the second observation is probably the source of you problem. If you output CSV QuickBase is going to throw double quotes into the output where it thinks they are needed (and it does this correctly). In particular it will throw quotes around field values that have a new line within them. In contrast, if you use the API_Do_Query method you can get precise control of what is outputted as you can inspect every single character.
What I am seeing is new line character (\n or decimal 10) both in the middle and at the end of every line. The input to your downstream probably just needs to remove these newlines and concatenating all the fields into a line of CSV. I am pretty sure this would solve the problem. So the bottom line is that you just need a custom exporter script given how data has landed in your application. Or you could take steps to remove the extra newlines when the data in entered or imported in the first place.
But to be honest it is difficult to tell as I don't recognize what format your downstream process is in. What are you feeding the exported data to?
In any event it is trivial to create custom export scripts that export your data in unique ways._ - Yes I understand that it is doing what it is supposed to which is why I need a direct export to txt. The file format is a gft or gift format which is required by some eLearning programs. The extra new lines are required.
- Okay problem solved. Basically you need a custom export that conforms to GFF file format. I see some blub explaining GFF here:
http://www.ensembl.org/info/website/upload/gff.html
This may or may not be similar to CSV or TSV but it is clearly different or they would not have their own file extension.
So this is what I would do: Use script and grab all the data feilds using API_DoQuery with &fmt=structured. Then iterate through the records and fields and paste together a line of GFF. Concatenate all the lines into one blob of text.
When you have your final blob of text assembled download it as a file using the download() function in this pastie:
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=316
BTW, that pastie was used to create some custom export file that was in a format called LEDES1998 - whatever that is. It is basically the same problem you have just a different file format.
Problem solved from my perspective.
Here is the script I used to iterate through the sample records you provided which you should be able to adopt to your purposes:
var dbid = "bm64xf9ij";
var dbidTable1 = "bm64xf9ji";
var apptoken = "k7dnxfe8k6cbdthevgzchzse6y";
$.ajaxSetup({data: {apptoken: apptoken}});
$.get('${dbidTable1}?act=API_DoQuery&qid=1&fmt=structured')
.then(function(xml) {
console.dirxml(xml);
$("record", xml).each(function() {
$("f", this).each(function() {
var field = $(this).text();
console.log(field);
});
$("f", this).each(function() {
var field = $(this).text();
var letters = field.split("").map(function(letter) {
return letter.charCodeAt(0);
});
console.log(JSON.stringify(letters));
});
console.log(" ");
});
});
If you need further help with this feel free to contact me offline using the information in my profile:
https://getsatisfaction.com/people/dandiebolt - Final note:
Apparently that is the wrong format. GFF is General Feature Format having something to do with genetics.
https://en.wikipedia.org/wiki/General_feature_format
Your format is GIFT (General Import Format Template) which is quite different:
https://en.wikipedia.org/wiki/GIFT_(file_format)
So I can see how this GIFT format is quite different and clearly requires a custom format like so://Comment line ::Question title :: Question { =A correct answer ~Wrong answer1 #A response to wrong answer1 ~Wrong answer2 #A response to wrong answer2 ~Wrong answer3 #A response to wrong answer3 ~Wrong answer4 #A response to wrong answer4 }
Anyway I put too much effort into discovering this myself so this is the limit of my forum contribution on this question.
If you need further help with this feel free to contact me offline using the information in my profile:
https://getsatisfaction.com/people/dandiebolt - Thank you! Yes I can have Quick Base create the right format in a field which is what I have done I just need that field to export as is to a Unicode 8 txt file.
