NEED TO MANIPULATE CSV FILE PRIOR TO IMPORT

  • 1
  • 2
  • Question
  • Updated 1 year ago
  • Answered

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

Photo of eduardo

eduardo

  • 40 Points

Posted 5 years ago

  • 1
  • 2
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
I have a limited time today to answer questions but you should investigate using the libraries listed below. Accounting JS will allow you to format your currency, Date JS will allow you to format your dates, Underscore JS will allow you to manipulate your data in parallel and D3 has a CSV parser.

Accounting JS
http://openexchangerates.github.io/accounting.js/
https://cdnjs.cloudflare.com/ajax/libs/accounting.js/0.4.1/accounting.min.js

Date JS
http://www.datejs.com/
https://cdnjs.cloudflare.com/ajax/libs/datejs/1.0/date.min.js

Underscore JS
http://underscorejs.org/
https://cdnjs.cloudflare.com/ajax/libs/underscore.js/1.7.0/underscore-min.js

D3 JS
http://d3js.org/
https://github.com/mbostock/d3/wiki/CSV
https://cdnjs.cloudflare.com/ajax/libs/d3/3.4.13/d3.min.js

Underscore and DatesJS are used by QuickBase and can be drawn from URLs to their CDN, but drawing the libraries from their CDN will force you to occassionally edit the URLS a the version number is built into the URL.
I will come back to your question later in the week. Or perhaps we could devise a scheme where we break your question up into dozens of smaller questions so I can earn more points.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
See this Question which has a working example:

How To: Select File, Transform and Import ?
https://quickbase-community.intuit.com/questions/1011724
If you fly me down to Brazil I will integrate your data and beautify the example with Bootstrap for you!
Photo of eduardo

eduardo

  • 40 Points
Yes it covers all functionalities I wanted to implement and deserves "spontaneous celebration and prance".

I would like to try and adapt it for my data, don't know if that breaks proprietary right or something that I have to be aware of, so I wanted to ask just in case.
If you allow me to use it, do I have to give any credit, and where??

About Brazil: It is a huge growing Market, which Intuit does not address efficiently in any of his products (QuickBooks etc.), starting with not supporting Portuguese language.

Only has a translated version of QuickBooks Online, which does not deal with Brazilian Taxes so it is useless.

On the other side Salesforce.com is quite active in Brazil, and starting to grow. There are some cloud-based products (Brazilian made), but they cannot compete with the strengths of Intuit.

We have 2 small companies (entertainment production company, and telephone stores) In Brazil, and I am angel Investor in two APP start-ups. I am not an internet/programing expert (would love to be If I was Born again), and I can tell you that some things I have accomplished with quick base are 10 times better than any packed software I have analyzed, and I was able to adapt the process as how we worked and not the other way round.

I am so fond of Quick base (and now that I am using those examples and libraries you provided I feel as a savy Tech Hacker hahaha...), that I was thinking of hiring one or two programing experts to understand Quick base thoroughly, and Become a Quick Base solution Provider In Brazil.

If you have any thoughts about this... I am willing to exchange ideas... Lots of opportunities to travel to Brazil!!!! Olympics, Rio Carnival, wonderful Surf...

Thanks again
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
>About Brazil: It is a huge growing Market ...
>If you have any thoughts about this... I am willing to exchange ideas ...

Okay let's set up the first QuickBase User Group Conference in Rio de Janeiro Brazil. Fly me down and I will keynote for you and teach you all the tricks. I am serious so contact me using the info in my profile. I am getting in the mode with a little Barry White: http://grooveshark.com/s/Rio+De+Janeiro/3UIEEc?src=5
Photo of eduardo

eduardo

  • 40 Points
Adapted working example to my needs

http://pastebin.com/HzXQ8tpz

Used ";" separator in d3

Has 6  fields: conta, Data_Mov, Nr_Doc, Historico, Valor, Deb_Cred,

I do 1 transformations
var valorFormat = row["Valor"].replace(".", ",");  //replaces "." with ","
csv_line.push(valorFormat);

I create a new one concatenating
 3 fields

csv_line.push(row["Conta"] + "-" + row["Nr_Doc"] + "-" + row["Valor"].replace(".", ","));// A new string.

It is working but I am terribly confused.

Some files are imported, some files are not. It seems that is something related with de size of the file.
Files higher than 800bytes are not imported??
I there a buffer or something similar I have to deal with?

Thanks
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Add a console command to view the resposne to the API_Import:FromCSV:

 $.when(promise).then(function(xml) {
   alert("all done");
   console.dirxml(xml);
 });

This will tell you what happened and what the error code was.
Photo of eduardo

eduardo

  • 40 Points
[Log] #document (biy98dgip, line 81)
<qdbapi>​...​</qdbapi>​

This is the output of the console. Same output for Imported and no imported
Photo of eduardo

eduardo

  • 40 Points
sorry i did not check the ...
Photo of eduardo

eduardo

  • 40 Points
HAHA Thank this thing of the console detected my problem. Something with unique fields
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
The output is drill down so you have to click to expose the details.
Photo of eduardo

eduardo

  • 40 Points
txt file has no headings.
After parsing with d3

How you identify the array, when you do push
csv_line.push(row[1]);, etc.... line by line.....

THKS
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
The text file has no headings probably because you sliced them off with these lines of code:

            var lines = reader.result.split('\n');
            lines.splice(0,0);
            var newtext = lines.join('\n');
            var dsv = d3.dsv(";", "text/plain");
            var data = dsv.parse(newtext);

My original example had an extra header row which I sliced off and you used the same code. Try this:

            var dsv = d3.dsv(";", "text/plain");
            var data = dsv.parse(reader.result);
Photo of eduardo

eduardo

  • 40 Points
nope dan it has no headings. Its a text fiel (formatted but with no headings)
I already understood the line splice thing, after a couple of hours hahaha
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Okay so add an header manually:

var header = "foo,bar,baz,quz,quux";
var data = dsv.parse(header + "\n" + reader.result);
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
And do everyone a favor and published a cleaned up version once you sort out all the details. I am afraid a lot of people would be interested in this but the series of low level questions is confusing readers.
Photo of eduardo

eduardo

  • 40 Points
Lets wrap up: I feel really good now.

Just Cut and Paste of Dan Diebold ideas made by a "dummies"

Two different TEXT files separated by ";", that go to One same table.

File with headings:
"Conta";"Data_Mov";"Nr_Doc";"Historico";"Valor";"Deb_Cred"
"1520003000015762";"20140602";"608000";"CONV608000";"60000.00";"C"
"1520003000015762";"20140604";"000035";"CHEQUE SAC";"20000.00";"D"
"1520003000015762";"20140604";"000017";"LIB TALAO";"5.00";"D"

Transforms to:
"1520003000015762","2014/06/02","608000","CONV608000","60000,00","C","1520003000015762-608000-60000,00"
"1520003000015762","2014/06/04","000035","CHEQUE SAC","20000,00","D","1520003000015762-000035-20000,00"
"1520003000015762","2014/06/04","000017","LIB TALAO","5,00","D","1520003000015762-000017-5,00"


Transformations done
a.- select ";" as separator
b.- Introduce "/" to configure date formt. Did not use Date.js
c.- Transform number xxx.xx to xxx,xx (european mode) Did not use accountant.js
d.- Create a new column to configure a unique record concatenating three other columns.

http://pastebin.com/w0Kfzr0C

File without headings

03/06/2014;TAR CONTA CERTA    05/14;-205,40
03/06/2014;REND PAGO APLIC AUT MAIS;0,24
06/06/2014;SISPAG  ELEM CONS SERVIC;589,88


Transforms to

"03/06/2014","TAR CONTA CERTA    05/14","-205,40","03/06/2014-TAR CONTA CERTA    05/14--205,40"
"03/06/2014","REND PAGO APLIC AUT MAIS","0,24","03/06/2014-REND PAGO APLIC AUT MAIS-0,24"
"06/06/2014","SISPAG  ELEM CONS SERVIC","589,88","06/06/2014-SISPAG  ELEM CONS SERVIC-589,88"

Transformations:

a.- Create a Header of column headings: var header = "Data_Mov;Historico;Valor" (same as first file)
b.- Create a new column with three others to configure a unique record.

http://pastebin.com/dar6cPip

For now I have the two scripts (literally) in two pages, and In a dashboard Two web UrL directing to each  page. One for each type of file.

My next objectives:
1.- create a button or something to select Type of File?. On selection go to one or other script. Will search or ask on the community.
2.- On selection I want to introduce on the second file a new column "Conta" with the same value "itau" to identify the file.

Would be nice to post (following Dan's examples); Different types of transformations that we could "plug and Play" for dummies as me. Syntax kills me!!
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Eduardo,
I think i am trying to do something very similar. I am also from Brazil and I have the same problem with the date format. 
Also, looking to automate the import of some csv files into a table. Let me know if you could share your experience, because that above it sound like Chinese to me.
Photo of eduardo valdes

eduardo valdes

  • 162 Points 100 badge 2x thumb
yes I can share. most of It is Dan diebolts ideas.

mail me if you want and we can arrange a conference call. Looking forward to meeting Brazilian Quickbase Users!!!!

eduardo.valdes@mac.com
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Could you share the code or formulas to get the task you mention above accomplished? 
I also have reports that I receive everyday that need to be worked before importing. Also, would love to automate that.
Thanks in advance
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,174 Points 20k badge 2x thumb
Sergio you never said you were from Brazil Mas Que Nada! - Eduardo and I were planning a QuickBase conference in Brazil where we would feature only topics you can't do in native QuickBase. Delayed but still game on. 

I got you voice mail but email directly me using info in my profile to become part of this epic QuickBase conference:

https://getsatisfaction.com/people/dandiebolt


da
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,174 Points 20k badge 2x thumb
Let's start a Kickstarter campaign for a QuickBase user conference in Brazil. I know the original idea was to only feature topics that couldn't be done in native QuickBase but lets be fair. We could rebrand to also include native topics that you didn't know you could do in QuickBase. It will be an epic event.. GAME ON.
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Dan,

We are a merchant service provider in Brazil, and I have had some of my merchants requesting a CRM solution. One of them really liked what I showed them, and I am sure it would be a great solution.
I am very interested to be offering Quickbase as one of my solutions, and I am able to put it to thousands of customers if we can tropicalize it.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,174 Points 20k badge 2x thumb
> ... tropicalize it. ...

I am not sure what you mean by "tropicalize" but I thinking you might mean what is called "internationalization" or i18n  - making the application suitable for other languages and locales such as Portuguese / Brazil. QuickBase does not have a solution yet but if your users are logging into the application there and you can require them to use a specific set of browser there are a lot of things you can do to make them more comfortable with their language. 

Feel free to email me using the information in my profile:

https://getsatisfaction.com/people/dandiebolt
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
I mean that you will have to adapt basic fields like address, phone numbers, language, specially all the errors alerts etc.
I am sure you will have a solution for all the barriers my users find.
Photo of eduardo valdes

eduardo valdes

  • 162 Points 100 badge 2x thumb
Sergio yes I can share... and Dan will certainly help.
Photo of Sergio

Sergio

  • 492 Points 250 badge 2x thumb
Looking forward to!
Photo of Martin

Martin

  • 116 Points 100 badge 2x thumb
Hi, I'm from Brazil too, I've never used Dan's techniques (they sound fantastic) but they are out of my level ( I don't know programming).
I'm thinking of giving up quickbase after 3 years without seeing advances in the internationalization or use advanced mobile features (as we are a construction company I need offline access).
Today we do many routines in Excel to be able to import the bank information and boletos, which generates many human mistakes and loss of time.
I am studying migrating to windev / webdev and starting from scratch. I would like to know your experience and perspectives. please contact me at martin@mirantesdalagoa.com.br
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,174 Points 20k badge 2x thumb
FWIW, I added Brazilian Tax ID Number (format: 00.000.000/0000-00 | 00000000000000) to the field validation example in this post:

Field Validation - Sorry No Bubbles
https://community.quickbase.com/quickbase/topics/field-validation-sorry-no-bubbles

I ask you:
What other online database supports the validation of  Brazilian Tax ID NumbersOnly QuickBase!
(Edited)
Photo of eduardo valdes

eduardo valdes

  • 162 Points 100 badge 2x thumb
Dan one thing I have not achieved is a JavaScript code to substitute diacritics and accents before importing in a consistent way. I can do it in a quickbase field ( you suggested that in another post)but would be better before. In that way the database is not polluted and can be filtered.
Any ideas to look at in the internet? It is code related no quickbase related.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,174 Points 20k badge 2x thumb
I did a project removing diacriticals for French. I think the issue was that search was not picking up the diacritical - I am a little foggy on the requirements as it was a while ago.

Also, when I converted the Pastie Database to point to posts in the new forum I had to run a script to search on the title of the post and finding a match swap in the new URL. As I recall it worked perfectly except on a few messages that had diacriticals in the title. So I am pretty sure I can find a solution.

So I am sure it can be done so feel free to contact me off-world using the information in my profile:

https://getsatisfaction.com/people/dandiebolt/
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,174 Points 20k badge 2x thumb
Eduardo,

I can remove all of the following diacriticals used by Portuguese:
  • the cedilla (ç)
  • acute accent (á, é, í, ó, ú)
  • circumflex accent (â, ê, ô)
  • tilde (ã, õ)
  • grave accent (à)
If this is one of the limitations to using QuickBase in Brazil (or other countries) I don't see a problem.

I am beginning to think we can come up with a working solution sooner than QuickBse will offer an i18n solution and learn a lot in the process.

But I have other work to do so contact me off-world if you want to pursue this.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,174 Points 20k badge 2x thumb
Good news on the English to Portuguese QuickBase project. We worked out all the technical details and are just testing the Regular Expressions and DOM Selectors to do the string substitutions at scale in the Service Worker

Here is an architecture diagram which shows the Service Worker translating the languages strings within the HTML and JavaScript. The whiteboard shows English to Spanish translation (Hello => Hola)  but in fact we are doing English to Portuguese first.



If you are in town this Wednesday our local Portuguese Language Meetup is meeting this Wednesday from 7-10 pm at Grizzly Peak Brewing Co.

Portuguese Language Meetup in ANN ARBOR, Michigan!!!
E aí, galera? Vamos para Ann Arbor?!!
(What's up guys? Let's go to Ann Arbor? !!)
https://www.meetup.com/DetroitPortuguese/events/248027525/