topic Re: pb with update database in Discussions

pb with update database

Françoise — Tue, 14 Aug 2018 06:20:58 GMT

hi,

with jmp14, I've created a database with the query builder.

when I want to update the data base, there is no update.

I don't understand why.

If someone could help me...


//Beginning of the script for the update of the database:

New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables(
		["Exploitation" =>
		"Source(Open(
	\!"S:\Guingamp\Commun\Fab\BDD Exploitation.xlsm\!",
	Feuilles de calcul( \!"Exploitation\!" ),
	Use for all sheets( 1 ),
	Concatenate Worksheets( 0 ),
	Create Concatenation Column( 0 ),
	Worksheet Settings(
		1,
		Has Column Headers( 1 ),
		Number of Rows in Headers( 1 ),
		Headers Start on Row( 4 ),
		Data Starts on Row( 5 ),
		Data Starts on Column( 1 ),
		Data Ends on Row( 0 ),
		Data Ends on Column( 0 ),
		Replicated Spanned Rows( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 0 ),
		Suppress Hidden Columns( 0 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( \!"-\!" )
	)
))"
		]
	),
	QueryName( "SQLQueryGGP" ),
	Select(
		Column(
			"Semaine",
			"t1",
			Analysis Type( "Ordinal" ),
			Numeric Format( "Fixed Dec", "0", "NO", "" )
		),
		.....
		
		
		
		
		
		
//end of the script:
			)
) << Run Foreground( UpdateTable( Current Data Table() ) );

Re: pb with update database

gzmorgan0 — Tue, 14 Aug 2018 08:14:59 GMT

It is not clear to me what you are trying to do, what you are trying to update. Your script has used a query to extract data from an XLSM workbook.

Are you trying to update a table, opened prior to running this query with the extracted information?
Are you trying to update current records in the .xlsm from a table that is open or trying to change multiple records conditionally, for example UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
??

The JSL you shared is not creating a database, it is creating a JMP table.

-confused-

Re: pb with update database

Françoise — Tue, 14 Aug 2018 08:24:05 GMT

I'm trying to update a jmp table from a xlsm file.

the jmp file was created with: table/jmp query builder.

then, every day, I open the same jmp file and I use "Update from the database". I want that the jmp file will be updated from the xlsm file.

Re: pb with update database

gzmorgan0 — Tue, 14 Aug 2018 11:47:20 GMT

I have examples that work with .xls and .xlsx.

I took my JMP table, deleted a column, deleted rows, changed values, then ran the update and the JMP table was restrored.

Sorry, I have no experience with .xlsm.

Re: pb with update database

Françoise — Tue, 14 Aug 2018 12:14:14 GMT

could you send me the beginning and the end of the script to update the database?

Re: pb with update database

Françoise — Wed, 15 Aug 2018 15:16:34 GMT

Hi,

I've used the querybuilder JMP to create a database JMP from an excel file.

First, I have the beginning of the update data with this:

New SQL Query(

Version( 130 ),

Connection( "JMP" ),

JMP Tables(

["BDD 2" =>

"Source(Open(

\!"S:\Pilotage\MTBSTAT\BDD Anthony\MEGABASE.xlsx\!",

Feuilles de calcul( \!"BDD\!" ),

Use for all sheets( 1 ),

Concatenate Worksheets( 0 ),

Create Concatenation Column( 0 ),

Worksheet Settings(

Has Column Headers( 1 ),

Number of Rows in Headers( 1 ),

Headers Start on Row( 1 ),

Data Starts on Row( 2 ),

Data Starts on Column( 1 ),

Data Ends on Row( 0 ),

Data Ends on Column( 0 ),

Replicated Spanned Rows( 1 ),

Replicated Spanned Headers( 0 ),

Suppress Hidden Rows( 0 ),

Suppress Hidden Columns( 0 ),

Suppress Empty Columns( 1 ),

Treat as Hierarchy( 0 ),

Multiple Series Stack( 0 ),

Import Cell Colors( 0 ),

Limit Column Detect( 0 ),

Column Separator String( \!"-\!" )

)

It seems that the update is good the first time and I save the new jmp file

But, when I open again ths file and I make a new update, the script of the update is changed automatically and replaced by:

New SQL Query(

Version( 130 ),

Connection( "JMP" ),

JMP Tables(

["BDD 2" =>

"\S:\Pilotage\MTBSTAT\BDD Anthony\JMP\\BDD 2.jmp"

]

QueryName( "BDD 2" ),

Select(

Column( "N° de Cuve", "t1", Analysis Type( "Ordinal" ) ),

and the update doesn't go on.

Has someone a solution?

best regards

Re: pb with update database

gzmorgan0 — Wed, 15 Aug 2018 23:08:00 GMT

I am not sure why that is happening, but here is a suggestion. Copy the Modify script from created data table and paste it to a new script named, My Update. Change

<< Modify

<< Run Foreground( UpdateTable( Current Data Table() ) )

then save. Now delete some rows and columns and empty some values then run it ...do not save it until you have tested it works.

If that doesn't work, you might want to call Tech Support. Please post whether this works for you. Good Luck.

p.s. Oops, forgot to remind you to check that you do not have a date or other criteria that might be restricting the update.

I do not know why JMP would have written over the script, but it should not write over a custom named script.

Re: pb with update database

Françoise — Thu, 16 Aug 2018 08:06:05 GMT

Thanks for your answer.

I don't understand also why my jsl is modified by JMP and I find it alarming.

I shall not call the tech support. Once, they answered me that they answer to pbs of installation but not for customers jsl.

I must find the solution alone. it's a big pb if I can't update jmp database: my jsl and JMP will not have interest to be used.

best regards