Negative in parenthesis

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
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.
Photo of Stephen

Stephen

  • 760 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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
Photo of Stephen

Stephen

  • 760 Points 500 badge 2x thumb
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. 
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
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.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,304 Points 20k badge 2x thumb
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
(Edited)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 28,304 Points 20k badge 2x thumb
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
(Edited)