Discussions

 View Only
  • 1.  Negative in parenthesis

    Posted 04-07-2017 22:07
    I have a spreadsheet that is using parentheses to notate negative numbers. so -4.30 is (4.30) and positive 4.30 is 4.30.  This is being read by a connected CSV table and the system isn't reading the (4.30) as negative but stripping the parentheses. Any way to fix this other than me converting the field to text, and then creating a formula-numeric field to strip the ( ) off and add the negative valuation?

    Sounds troublesome because I might have 5-10 fields and have to do so many fields that way.


  • 2.  RE: Negative in parenthesis

    Posted 04-08-2017 07:04
    I'm not an excel wiz, but I think you can change your .xlsx settings to display as a -4.30 rather than parentheses (4.30).

    Once you switch that, then save the .csv for the import


  • 3.  RE: Negative in parenthesis

    Posted 04-08-2017 13:08
    Matthew, thanks, and I am pretty good at excel, but I'm getting a daily XLSX from a subcontractor and my goal is to avoid having to do any manual manipulation (it would be every morning!) before the CSV gets sucked in. it's pretty easy to automate a XLSX to CSV conversion, but the more "issues" I have to fix the more mistakes are made and time wasted. 


  • 4.  RE: Negative in parenthesis

    Posted 04-09-2017 07:05
    Yeah, QuickBase doesn't have any settings like this to handle (negative) numbers.  

    How do you "automatically" convert the xlsx to csv?  maybe We can do something there...  

    This is a new problem for me.  Sorry I can't help more.


  • 5.  RE: Negative in parenthesis

    Posted 04-09-2017 07:42
    You of course can do this with script. Generally the process involves these steps:
    1. Place a <input type=file> control on a miniature form somewhere
    2. When a file is selected you read it using the FileReader API
    3. When the FileReader object is loaded you process the file's contents by parsing the CSV into an array of objects with one object per line of CSV. The parsing is done using D3's CSV method
    4. Now you iterate through the array changing the format of every numberr in parentheses into a number formatted with a leading minus sign (any and other transofmration you want to make on the data - this is a very general processing procedure)
    5. Reassemble the data into a blob of CSV and import using the API method API_ImportFromCSV
    It may sound complicated but the APIs (FileReader API, API_ImportFromCSV) and libraries (D3, jQuery, Underscore) involved make for a writing a small amount of code because they operate at higher levels of abstraction. Most of the steps outlined above are implemented in a couple of lines of code.

    I should also mention that when you use script you achieve your original objective and make future tasks and implementing additional features easy work because the script you use is infinitely adaptable to the new purpose. In other words , when using script you don't encounter arbitrary limitations or hit random ceilings (like the number for form rules allowed).

    If you need individual assistance with this task please feel free to contact me off-world using the information in my profile:

    https://getsatisfaction.com/people/dandiebolt


  • 6.  RE: Negative in parenthesis

    Posted 04-09-2017 13:07
    I should add is that there is a working demo I created a few years ago that demonstrates this technique.

    If you download this CSVish file:

    https://haversineconsulting.quickbase.com/db/bjfwg3cwb?a=dbpage&pagename=data.txt

    You can import it into this application:

    https://haversineconsulting.quickbase.com/db/bjfwg3cwb

    This file is CSVish because it has two header columns, separates data with pipe characters.

    The custom processing logic performs these three cleanup steps:
    1. Concatenates the first and last names into a single field
    2. Normalizes spelled out states to have two letter abbreviations
    3. Normalizes emails to be all lower case
    Here is a fragment of code that process each line:
    csv_line.push(row["First Name"] + " " + row["Last Name"]); csv_line.push(row["Address"]);
    csv_line.push(row["City"]);
    if (row["State"].length > 2) {
      csv_line.push(states[row["State"].toLowerCase()]);
    } else {
      csv_line.push(row["State"]);
    }
    csv_line.push(row["Zip"]); csv_line.push(row["Email"].toLowerCase());

    It would be a simple matter to perform the parentheses processing.

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=356

    If you need individual assistance with this task please feel free to contact me off-world using the information in my profile:

    https://getsatisfaction.com/people/dandiebolt