cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Françoise
Level V

pb with update database

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() ) );

 

 

 

 

7 REPLIES 7
gzmorgan0
Super User (Alumni)

Re: pb with update database

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-

  

Françoise
Level V

Re: pb with update database

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.

gzmorgan0
Super User (Alumni)

Re: pb with update database

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.

 

Françoise
Level V

Re: pb with update database

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

 

 

Françoise
Level V

Re: pb with update database

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(

1,

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

gzmorgan0
Super User (Alumni)

Re: pb with update database

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

to  

<< 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.

 

Françoise
Level V

Re: pb with update database

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