• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

from excel column to array

Participant ,
Jul 22, 2020 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

599

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
community guidelines
Community Expert ,
Jul 22, 2020 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 )

Votes

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
community guidelines
Participant ,
Jul 22, 2020 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

Votes

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
community guidelines
Community Expert ,
Jul 22, 2020 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 )

Votes

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
community guidelines
Participant ,
Jul 22, 2020 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 

 

Votes

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
community guidelines
Community Expert ,
Jul 22, 2020 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;

 

Votes

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
community guidelines
Participant ,
Jul 22, 2020 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? 

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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')

 

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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!

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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"

 

Votes

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
community guidelines
Participant ,
Jul 23, 2020 Jul 23, 2020

Copy link to clipboard

Copied

I've more than 10 columns.

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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')

 

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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 

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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

Votes

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
community guidelines
Participant ,
Jul 23, 2020 Jul 23, 2020

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Participant ,
Jul 27, 2020 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! 

Votes

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
community guidelines
Participant ,
Jul 28, 2020 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')

 

 

Votes

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
community guidelines
Participant ,
Jul 28, 2020 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;

Votes

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
community guidelines
Participant ,
Jul 28, 2020 Jul 28, 2020

Copy link to clipboard

Copied

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

Votes

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
community guidelines
Participant ,
Jul 28, 2020 Jul 28, 2020

Copy link to clipboard

Copied

LATEST

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! 

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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"; 

Votes

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
community guidelines
Community Expert ,
Jul 23, 2020 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 )

Votes

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
community guidelines
Participant ,
Jul 23, 2020 Jul 23, 2020

Copy link to clipboard

Copied

yes but i can't process the data

Votes

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
community guidelines
Community Expert ,
Jul 23, 2020 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 )

Votes

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
community guidelines
Participant ,
Jul 23, 2020 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

Votes

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
community guidelines