Negative in parenthesis

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

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. 

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.

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

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