Copy and Paste to listgrid from Excel

[zaj]

Hello there,

Can somebody please guide me how to manage this situation. I have a listgrid with 5 columns. 4 cols are integer and 1 is string. Now I have a excel sheet from where I am copying the col and trying to paste in my sgwt listgrid. Now the problem i have is in excel all the col are string,so when i copy the numbers from there into my listgrid i get validation error - "Must be whole number." the reason is there are comma in the numbers in the excel sheet. Is there some way I can convert them to integer in my listgrid so i can save it to my db.

cheers
Zolf

[Isomorphic]

See DataSource.recordsFromText() and the Excel to Grid example.

[zaj]

infact I am using that but for some reason I get this error in my console and the listgrid cell shows the red exclamation saying require whole number. see the attachement

[ERROR] [kaizenbeh] - 13:46:48.158:MUP2:WARN:DataSource:importExcelDS:importExcelDS.qtys: value: " - " failed on validator: {type: "isInteger",
typeCastValidator: true,
_generated: true,
stopIfFalse: true,
defaultErrorMessage: "Must be a whole number."}
[ERROR] [kaizenbe] - 13:46:48.164:MUP2:WARN:DataSource:importExcelDS:importExcelDS.qtys: value: " 584,000,000 " failed on validator: {type: "isInteger",
typeCastValidator: true,
_generated: true,
stopIfFalse: true,
defaultErrorMessage: "Must be a whole number."}
[ERROR] [kaizenbeh] - 13:46:48.169:MUP2:WARN:DataSource:importExcelDS:importExcelDS.qtys: value: " - " failed on validator: {type: "isInteger",
typeCastValidator: true,
_generated: true,
stopIfFalse: true,
defaultErrorMessage: "Must be a whole number."}
[ERROR] [kaizenbeh] - 13:46:48.173:MUP2:WARN:DataSource:importExcelDS:importExcelDS.qtys: value: " - " failed on validator: {type: "isInteger",
typeCastValidator: true,
_generated: true,
stopIfFalse: true,
defaultErrorMessage: "Must be a whole number."}
[ERROR] [kaizenbeh] - 13:46:48.177:MUP2:WARN:DataSource:importExcelDS:importExcelDS.qtys: value: " - " failed on validator: {type: "isInteger",
typeCastValidator: true,
_generated: true,
stopIfFalse: true,
defaultErrorMessage: "Must be a whole number."}
[ERROR] [kaizenbeh] - 13:46:48.182:MUP2:WARN:DataSource:importExcelDS:importExcelDS.qtys: value: " - " failed on validator: {type: "isInteger",
typeCastValidator: true,
_generated: true,
stopIfFalse: true,
defaultErrorMessage: "Must be a whole number."}

[Isomorphic]

Read the FAQ about the information you need to provide to enable others to help you.

[zaj]

I am using the SmartClient Version: v10.0p_2015-03-13/PowerEdition Deployment (built 2015-03-13) and testing on FF26 browser

[Isomorphic]

Right.. keep reading the FAQ. The point is to enable others to reproduce the problem, which obviously cannot be done without the data you are pasting, the DataSource definition, and several other pieces of information.

[zaj]

sorry about it.

My Datasource file looks like this and I have attached my data in html format as I was not able to upload the excel file.

<DataSource ID="importExcelDS" serverType="sql" tableName="expd">
	<fields>
		<field name="id" type="sequence" hidden="true" primaryKey="true" />
		<field name="name" title="Product Name" type="ntext" />
		<field name="qtys" title="Sales Quantity" type="int" />
		<field name="qtysk" title="Stock Quantity" type="int" />
		<field name="qtyw" title="Stock on way" type="int" />
		<field name="qtyt" title="Total Quantity" type="int" />
		<field name="did" title="Distributor" type="int" hidden="true"/>
		<field name="date" title="Date" type="date"  hidden="true"/>
	</fields>
</DataSource>

[Isomorphic]

Your .html upload seems to have failed, but was obviously unnecessary in the first place, since what is needed is just the text you paste into the TextArea used for recordsFromText(). Since you're likewise dealing with a TextArea in these forums, just PASTE the data. Use [ code ] tags as the FAQ instructs.

[zaj]

 -   	 -   	 -   	 -   
 -   	 -   	 -   	 -   
 -   	 -   	 -   	 -   
 93,484,800 	 1,728 	 -   	 -   
 1,148,000 	 70 	 -   	 -   
 837,588,000 	 1,252 	 90,984,000 	 136 
 -   	 -   	 -   	 -   
 -   	 -   	 -   	 -   
 -   	 -   	 -   	 -   
 2,736,240,000 	 11,401 	 353,280,000 	 1,472 
 -   	 -   	 -   	 -   
 4,964,800,000 	 8,560 	 574,780,000 	 991 
 8,592,610,000 	 10,946 	 554,210,000 	 706 
 4,772,172,000 	 10,676 	 564,561,000 	 1,263 
 -   	 -   	 -   	 -   
 -   	 -   	 -   	 -   
 3,681,167,000 	 14,213 	 279,461,000 	 1,079 
 -   	 -   	 -   	 -   
 967,346,000 	 14,438 	 3,752,000 	 56 
 1,138,125,000 	 3,035 	 73,875,000 	 197 
 -   	 -   	 -   	 -   
 -   	 -   	 -   	 -   
 -   	 -   	 -   	 -   
 33,051,080,800 		 3,078,903,000

[Isomorphic]

So it looks like in your source spreadsheet, someone has entered "-" instead of just leaving the cells blank. It also looks like someone has introduced a blank space in front of the numeric values as well, which is not normal for an Excel paste.

If the data your users will be pasting has strange little application-specific details like this, you will need to pre-process it to remove these problems before the data can be passed to recordsFromText().

[zaj]

Isomorphic, i am struggling with this issue. How can i pre-process the data when recordsFromText() takes only String. In my datasource I have defined some fields as Integer.