Highlighted

from excel column to array

Participant ,
Jul 22, 2020

Copy link to clipboard

Copied

Hey!!

 

quick question.

I've extracted a cloumn from an excel sheet and i want to save this column in an array, how could i?

 

app.excelImportPreferences.properties = {
    sheetIndex : 0,
    aligmentStyle : AlignmentStyleOptions.SPREADSHEET,
    rangeName :"B2:B114" 
}

var myProperties = app.excelImportPreferences.properties;

	
myTextFrame.place(fileExcel, false, myProperties); 

 

TBH, I've taken this code from the community and now I'm wondering how to save the range in array. Because if i manged to save it in array then i can loop through it, to manuplate it.

thanks all. 

 

*I'm using javascript

TOPICS
How to, Import and export, InCopy workflow, Scripting, SDK

Views

91

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

from excel column to array

Participant ,
Jul 22, 2020

Copy link to clipboard

Copied

Hey!!

 

quick question.

I've extracted a cloumn from an excel sheet and i want to save this column in an array, how could i?

 

app.excelImportPreferences.properties = {
    sheetIndex : 0,
    aligmentStyle : AlignmentStyleOptions.SPREADSHEET,
    rangeName :"B2:B114" 
}

var myProperties = app.excelImportPreferences.properties;

	
myTextFrame.place(fileExcel, false, myProperties); 

 

TBH, I've taken this code from the community and now I'm wondering how to save the range in array. Because if i manged to save it in array then i can loop through it, to manuplate it.

thanks all. 

 

*I'm using javascript

TOPICS
How to, Import and export, InCopy workflow, Scripting, SDK

Views

92

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jul 22, 2020 0
Adobe Community Professional ,
Jul 22, 2020

Copy link to clipboard

Copied

If you successfully imported that Excel sheet as table in your text frame, the text frame or the story of the text frame should contain a table object. From that table object you could access the contents of a column. If there are more than one rows that contents is already an array you could loop. Something like that should work:

 

var contentsArrayOfFirstColumn = myTextFrame.tables[0].columns[0].contents;

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 22, 2020 0
Participant ,
Jul 22, 2020

Copy link to clipboard

Copied

Hey Uwe, Thank you!

 

but i've question, how i know that i imported that Excel sheet as table?? becuse the indesign alert me that the object is invalid

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 22, 2020 0
Adobe Community Professional ,
Jul 22, 2020

Copy link to clipboard

Copied

What is the page with your text frame showing?

Is a table imported or not?

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 22, 2020 0
Participant ,
Jul 22, 2020

Copy link to clipboard

Copied

I didn't specify any pages. How can I import the table? Th excel file are saved in variable 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 22, 2020 0
Adobe Community Professional ,
Jul 22, 2020

Copy link to clipboard

Copied

Have you defined?

myTextFrame

 If not, then add this code. It will add a text frame that fills the entire first page of the active document.

var myTextFrame = app.activeDocument.pages[0].textFrames.add();
    myTextFrame.geometricBounds = app.activeDocument.pages[0].bounds;

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 22, 2020 1
Participant ,
Jul 22, 2020

Copy link to clipboard

Copied

Yes I've defined it, but if you may tell me how to import my excel sheet to table? 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 22, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

hi,

try this code:

 

function test(path){
	if(path != null){
		var doc = app.documents.add()
		doc.properties = {
			documentPreferences:{
				pageHeight:'297mm',
				pageWidth:'210mm',
			}
		}
		var tmp_textframe = doc.pages[0].textFrames.add()
		tmp_textframe.properties = {
			textFramePreferences:{
				autoSizingReferencePoint:1953459301,
				autoSizingType:1752069993,
			}
		}
		try{
			tmp_textframe.place(path)
			tmp_textframe.texts.everyItem().convertToTable('\t')
		}
		catch(e){
			alert(e)
			doc.close(1852776480)
		}
	}
}

app.doScript("test(File.openDialog('select file', '*.*'))", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, 'test')

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

Many thank to you! it's very helpful i also, I've tried to extract  one column in var so i can loop through it:

 

	var id = tmp_textframe.tables[0].columns[1].contents;

 

but it didnt work this way, do you know why?

 

 other small question, do you know if there are any replacement to place method? becuse i don't want to render the whole table to the page

 

		tmp_textframe.place(path)

 

 

thanks a lot again!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

Also, if iwant to manuplate the table how? these lines are to place it and format the file 

	tmp_textframe.place(path)
			tmp_textframe= tmp_textframe.texts.everyItem().convertToTable('\t')

 i want to save it and manuplate the data, for instance ;

table.rows.everyItem().height = "36pt"; 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

var id = tmp_textframe.tables[0].columns[1].contents;

don't work because your table contains only one column.

 

if you want to place a part of the table you should use .place(path, true) because you don't know how many row in the table, so this line is error :

rangeName :"B2:B114"

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

I've more than 10 columns.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

try to restart indesign, it remembers excelImportPreferences so you are getting error.

 

if you are using my code but with tmp_textframe.place(path, true) remove this line:

 

tmp_textframe.texts.everyItem().convertToTable('\t')

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

Ok now I've the column i want but how to itreate each cell? the properties of table are not accessible for the text frame 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

okay, now I am trying to explain ...
when you use any excelImportPreferences including .place(path, true)
tmp_textframe is already table so this line is not need :

tmp_textframe.texts.everyItem().convertToTable('\t')

but... if you don't use any excelImportPreferences
line above needs to stay

 

I suggest this code :

function test(path){
	if(path != null){
		var doc = app.documents.add()
		doc.properties = {
			documentPreferences:{
				pageHeight:'297mm',
				pageWidth:'210mm',
			}
		}
		var tmp_textframe = doc.pages[0].textFrames.add()
		tmp_textframe.properties = {
			textFramePreferences:{
				autoSizingReferencePoint:1953459301,
				autoSizingType:1752069993,
			}
		}
		
		/*
		// part of your code
		app.excelImportPreferences.properties = {
			sheetIndex : 0,
			aligmentStyle : AlignmentStyleOptions.SPREADSHEET,
			rangeName :"B2:B114" 
		}
		*/
		
		try{
			// work with part of your code
			//tmp_textframe.place(path, false, app.excelImportPreferences.properties)
			
			// work with window of import options
			//tmp_textframe.place(path, true)
			
			// file to place like text
			tmp_textframe.place(path)
			// only if file to place like text
			tmp_textframe.texts.everyItem().convertToTable('\t')
		}
		catch(e){
			alert(e)
			doc.close(1852776480)
		}
		
		var table = tmp_textframe.tables[0].cells.length == 1 ? tmp_textframe.tables[0].cells[0].tables[0] : tmp_textframe.tables[0]
		
		// here you may manipulate the table
		table.columns.add(LocationOptions.AT_END)
		//table.rows.add(LocationOptions.AT_BEGINNING)
		//table.columns[1].remove()
	}
}
app.doScript("test(File.openDialog('select file', '*.*'))", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, 'test')

now I hope I helped you

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

Your were a BIG HELP!! Thanks a LOT!!! wish you all the best.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 27, 2020

Copy link to clipboard

Copied

Hey again, i want to ask you about this line of code 

var tmp_textframe = doc.pages[0].textFrames.add()
		tmp_textframe.properties = {
			textFramePreferences:{
				autoSizingReferencePoint:1953459301,
				autoSizingType:1752069993,
			}
		}

you specified the size of the text, but i figured a problem. which is the table has a limt and dosn't read the entire column in the excel sheet. so do you know how can i make it more flix so it can read the entire row? no worries if the table exceeds the page i'm going to delete it at the end.

and thank again! 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 27, 2020 0
Participant ,
Jul 28, 2020

Copy link to clipboard

Copied

I suggested this code for demonstrated placing the table in indesign, at work I usage other code (similar like this) :

 

function place_excel(path){
	app.excelImportPreferences.properties = {tableFormatting:2018858068}
	var doc = app.documents.add()
	var tmp_textframe = doc.pages[0].textFrames.add()
	var tmp_content = ''
	try{
		tmp_textframe.place(path)
		tmp_content = tmp_textframe.parentStory.contents.split('\r')
	}
	catch(e){alert('File opened by another program\nor cannot be placed in InDesign')}
	doc.close(1852776480)
	return tmp_content
}
var exceltable = app.doScript("place_excel(File.openDialog('select file', '*.*'))", ScriptLanguage.JAVASCRIPT, undefined, UndoModes.ENTIRE_SCRIPT, 'place_excel')

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 28, 2020 0
Participant ,
Jul 28, 2020

Copy link to clipboard

Copied

I've already build my code so i dont have the brivlige to change somthing right know:( i'm sorry and thank you!

 

I tried to use this function but still didn't read the entire column.

	var tmp_textframe = doc.pages[0].textFrames.add()
		tmp_textframe.textFramePreferences.autoSizingType = AutoSizingTypeEnum.HEIGHT_AND_WIDTH_PROPORTIONALLY;
		tmp_textframe.textFramePreferences.autoSizingReferencePoint = AutoSizingReferenceEnum.TOP_LEFT_POINT;

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 28, 2020 0
Participant ,
Jul 28, 2020

Copy link to clipboard

Copied

I am very glad for you 🙂
Try not to add textFramePreferences and see what happens

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 28, 2020 0
Participant ,
Jul 28, 2020

Copy link to clipboard

Copied

I've tried to remove it. In the next step when i want to save the table in array it says their is no table! 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 28, 2020 0
Adobe Community Professional ,
Jul 23, 2020

Copy link to clipboard

Copied

Hi salshaw749,

if you want to do a range of cells use the code in your initial post first.

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

yes but i can't process the data

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Adobe Community Professional ,
Jul 23, 2020

Copy link to clipboard

Copied

If your text frame contains a table you could access the table with:

if( tmp_textframe.texts[0].tables.length > 0 )
{
	var column1contentsArray = tmp_textframe.texts[0].tables[0].columns[0].contents;
}
else{ alert("Error: No table found in text frame.")};

 

From that on you could loop the contents array.

Perhaps you should show a screenshot of your page after you placed the Excel file.

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 23, 2020

Copy link to clipboard

Copied

Thanks! I maneged to exctract the data in varible! thanks again it was really helpfull!

 

Other thing if you don't mind.

	for(var i = 1; i < column1contentsArray.length; i++){

				if (column1contentsArray[i].row == column1contentsArray[i++].row){
						alert(" equal")


					}
					else{
						alert("not equal")
					}
				}

I want to loop throgh each cell to compare the content so based on it i'm going to do somthing. Anyhow, it alwayes go to the first condtion and alert me that all the content are equeal. but its not! do you know where is the issue?

 

and thanks again you were a big help for me. i hope all the best for you

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Adobe Community Professional ,
Jul 23, 2020

Copy link to clipboard

Copied

if (column1contentsArray[i].row == column1contentsArray[i++].row

 

This will always alert true, since you're just comparing if a Row Object is equal to another Row Object. You need to drill down into the cells' contents to evaluate. 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Adobe Community Professional ,
Jul 23, 2020

Copy link to clipboard

Copied

You need to drill down into the cells' contents to evaluate.

 

No. Because column1contentsArray[i] is already the contents of a cell.

But I wonder why there is a comparison with the contents where the iteration number is actually changed !

 

Our OP should do just a loop to see how the value of i will change in its course!

 

salshaw749, just run this little code in the ESTK ( ExtendScript Toolkit ), or if on Mac OS X where the ESTK cannot run anymore, test the script with an alert() instead of a $.writeln():

 

var counter = 0;
for(var i = 1; i < 10 ; i++)
{
	$.writeln( counter.toString()+"\t"+ i );
	$.writeln( counter.toString()+"\t"+ ( i === i++ ) ); // Always returns true
	counter++
};

 

Result:

0	1
0	true
1	3
1	true
2	5
2	true
3	7
3	true
4	9
4	true

 

See, how the value of i is jumping? That makes no sense at all.

Also note, that [i] is always the same as [i++], because: first the values are compared and then the value of i is counted up.

 

Regards,
Uwe Laubender

( ACP )

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 23, 2020 0
Participant ,
Jul 27, 2020

Copy link to clipboard

Copied

I've solve it by using nested loop! which is seems time consuming but more accurate!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jul 27, 2020 0