NEED TO MANIPULATE CSV FILE PRIOR TO IMPORT
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...
Forum Discussion
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!!
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!!