Hope dandiebolt wins the Hawaii trip. Maybe next one to Brazil. Think I am one of the few Brazilian happy quickbase customers....
My company imports 4 CSV files, regularly (Daily basis) to One table. We have to import to excel, format columns (Brazil uses Date format DDMMYYYY, and NUMBER 123.456,78), Add one column to create a [UNIQUE] field, add one column to create a [Related other table field], rename de headings, export to CSV, import to quick base.
Time consuming and prone to errors (a lot), though easy to detect and reimport.
One CSV, is comma separated, other is tab separated and two are ?;? separated
Information is similar, but Headings are different (one of them has no headings), some have extra information that is not needed, one uses decimal numbers using ?,? the others use ?.?.
Implemented successfully dandiebolt idea: HTML5 file reader, Parse with d3 which creates an Object :csv , Do a map of csv, which creates an ARRAY?? Csv_Blob, that can be imported using the API_ImportFromCSV.
Now I am trying to learn: How to manipulate CSV_Blob (Array), prior to importing, or csv (OBJECT) prior to _map. Seems that underscore library has powerful tools to do that. It must be simple if you understand (which I am trying) arrays, objects, propriety?s, strings etc., cause from what I have read underscores deals directly with this creepy things.
What I have accomplished (more o less)
1. - I can parse the file reader output with D3.js selecting different separators (tab, ?,?, ?;? other). That?s fine!!
2. - D3.js can manipulate date and number formats of the propriety?s of each objet. I was not able to correctly format Number 123456.00 to 123.456,00 and Date YYYYMMDD to DDMMYYYY (something related to iso xx which seems I have to buy to now the correct code). What I did is transform the data directly in quick base (creating a formula text, and a formula number to cover all possibilities). It is working.
What I have not (still studying) and wishing some hints to make easier my process
1.- Create two new columns (Excel way).
Think this can be done in csv object prior to map, or in csv_blob Array prior to importing. My understanding is I need to create 2 new propriety?s, in all the Objects included in the csv_blob array. Must be a direct underscore snippet that I haven?t found or understand.
Lets say:
Before [{Name: xx, Phone: 123}, {Name: yy, Phone: 456}......]
After [[{Name: xx, Phone: 123, Newcol1: to_be 1, Newcol2: to_be 2}, {Name: yy, Phone: 456, Newcol1: to_be 1.1, Newcol2: to_be 2.1 }...]
2.- Automatically fill the values side of that propriety?s across the entire ARRAY, with two rules:
Rule number one: New propriety must be a string resulting of concatenating two other propriety?s:
Lets say [{Name: xx, Phone: 123, Newcol1: ?xx?+?123?}, {[{Name: yy, Phone: 456, Newcol1: ?yy?+?456?}...]
This creates a UNIQUE field in my configuration, and avoids duplicate records
Rule number two: Upon selection of a Button (think there are some ideas of how to do that in the community), some variable takes a value var Button.
This value must go in all objects of the array in the newly created propriety
Lets say say [{Name: xx, Phone: 123, Newcol1: ?xx?+?123?, Newcol2: var button}, {[{Name: yy, Phone: 456, Newcol1: ?yy?+?456?, Newcol2: var button}...]
3.- Thus there are different rules and different type of csv files I must implement a kind of button selection (do this or do that). What I am using now is have the snippets in different quick base code pages, and create a dashboard, with web url to those pages for simplicity.
Long question (sorry)
Appreciate some guidance