cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
miguello
Level VI

How to store Import Settings of Open() function in a separate expression

I need to open CSV files with various settings.

One of the examples is below:

dt = Open(
	filePath,
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 18 ),
		Data Starts( 19 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
);

I would like to store these settings that come inside Import Settings() construct as a separate expression and then use it when opening a file.

Something like this (but it doesn't work this way obviously):

settings1 = Expr(
Import Settings(
<my import settings here>
)
);
settings2 = Expr(
Import Settings(
<some other import settings here>
)
);

if(condition, importSettings = settings1, importSettings = settings2)
dt = Open(
filePath, 
Eval(importSettings)
);
1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: How to store Import Settings of Open() function in a separate expression

One option could be wrapping them like this:

Names Default To Here(1);

settings1 = Expr(
	dt = Open(
		filePath,
		Import Settings(
			End Of Line(CRLF, CR, LF),
			End Of Field(Comma, CSV(0)),
			Strip Quotes(1),
			Use Apostrophe as Quotation Mark(0),
			Use Regional Settings(0),
			Scan Whole File(1),
			Treat empty columns as numeric(0),
			CompressNumericColumns(0),
			CompressCharacterColumns(0),
			CompressAllowListCheck(0),
			Labels(1),
			Column Names Start(1),
			Data Starts(2),
			Lines To Read("All"),
			Year Rule("20xx")
		)
	)
);


settings2 = Expr(
	dt = Open(
		filePath,
		Import Settings(
			End Of Line(CRLF, CR, LF),
			End Of Field(Comma, CSV(0)),
			Strip Quotes(1),
			Use Apostrophe as Quotation Mark(0),
			Use Regional Settings(0),
			Scan Whole File(1),
			Treat empty columns as numeric(0),
			CompressNumericColumns(0),
			CompressCharacterColumns(0),
			CompressAllowListCheck(0),
			Labels(1),
			Column Names Start(5),
			Data Starts(6),
			Lines To Read("All"),
			Year Rule("20xx")
		)
	)
);

filePath = "somefilehere";
settings1;
settings2;
-Jarmo

View solution in original post

3 REPLIES 3
jthi
Super User

Re: How to store Import Settings of Open() function in a separate expression

One option could be wrapping them like this:

Names Default To Here(1);

settings1 = Expr(
	dt = Open(
		filePath,
		Import Settings(
			End Of Line(CRLF, CR, LF),
			End Of Field(Comma, CSV(0)),
			Strip Quotes(1),
			Use Apostrophe as Quotation Mark(0),
			Use Regional Settings(0),
			Scan Whole File(1),
			Treat empty columns as numeric(0),
			CompressNumericColumns(0),
			CompressCharacterColumns(0),
			CompressAllowListCheck(0),
			Labels(1),
			Column Names Start(1),
			Data Starts(2),
			Lines To Read("All"),
			Year Rule("20xx")
		)
	)
);


settings2 = Expr(
	dt = Open(
		filePath,
		Import Settings(
			End Of Line(CRLF, CR, LF),
			End Of Field(Comma, CSV(0)),
			Strip Quotes(1),
			Use Apostrophe as Quotation Mark(0),
			Use Regional Settings(0),
			Scan Whole File(1),
			Treat empty columns as numeric(0),
			CompressNumericColumns(0),
			CompressCharacterColumns(0),
			CompressAllowListCheck(0),
			Labels(1),
			Column Names Start(5),
			Data Starts(6),
			Lines To Read("All"),
			Year Rule("20xx")
		)
	)
);

filePath = "somefilehere";
settings1;
settings2;
-Jarmo
miguello
Level VI

Re: How to store Import Settings of Open() function in a separate expression

I guess I will have to spell out Open() function multiple times then. Or try to use Eval Insert()

jthi
Super User

Re: How to store Import Settings of Open() function in a separate expression

You might be able to do something with techniques mentioned here Can you construct this without writing expression as a string? 

 

 

Names Default To Here(1);

open_expr = Expr(Open(filePath));

settings1 = Expr(
	Import Settings(
		End Of Line(CRLF, CR, LF),
		End Of Field(Comma, CSV(0)),
		Strip Quotes(1),
		Use Apostrophe as Quotation Mark(0),
		Use Regional Settings(0),
		Scan Whole File(1),
		Treat empty columns as numeric(0),
		CompressNumericColumns(0),
		CompressCharacterColumns(0),
		CompressAllowListCheck(0),
		Labels(1),
		Column Names Start(1),
		Data Starts(2),
		Lines To Read("All"),
		Year Rule("20xx")
	)
);

Insert Into(open_expr, NameExpr(settings1));
Name Expr(open_expr);

 

-Jarmo