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

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

miguello — Fri, 09 Jun 2023 16:56:19 GMT

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

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

jthi — Thu, 07 Apr 2022 13:03:27 GMT

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;

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

miguello — Thu, 07 Apr 2022 16:40:04 GMT

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

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

jthi — Thu, 07 Apr 2022 16:54:07 GMT

You might be able to do something with techniques mentioned here

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