Export/Import of parameters from the Parameter Defaults table
48 views
Skip to first unread message
Eric Fallabel
Jun 25, 2009, 8:57:40 AM6/25/09
to IADS
We want to create a backup file of our derived parameters from the
Parameter Defaults table to bring with us to the telemetry room
because of a series of problems we've had with making updates to our
master configuration file (I'll detail these problems in a different
post).
The process we've tried to use is for each user to export his personal
derived parameters by highlighting them in the ParameterDefaults
table, right clicking and hitting "Export". This defaults to saving
the table in a *.csv file; I've tried to manually type a different
file extension and the table can be saved in other file formats, but
it seems that it is forced to be comma-delimited.
For backup purposes we then combine a number of these files to be re-
imported to the ParameterDefaults table if we have problems with our
equations.
Here's the problem: IADS forces the table to be exported in comma-
delimited format, BUT many derived equations have commas in them
because of the required syntax for most IADS operators. SO when we re-
import the file IADS treats commas within the syntax of derived
equations as column delimiters, and thus the columns do not line up
properly.
Is there some way to successfully import equations from a CSV file?
Is there some way to export data in tab-delimited format? From my
current understanding of the situation, there is no way to
successfully export and then re-import a set of equations, given the
pervasive use of commas in IADS operators.
Parameter Defaults table to bring with us to the telemetry room
because of a series of problems we've had with making updates to our
master configuration file (I'll detail these problems in a different
post).
The process we've tried to use is for each user to export his personal
derived parameters by highlighting them in the ParameterDefaults
table, right clicking and hitting "Export". This defaults to saving
the table in a *.csv file; I've tried to manually type a different
file extension and the table can be saved in other file formats, but
it seems that it is forced to be comma-delimited.
For backup purposes we then combine a number of these files to be re-
imported to the ParameterDefaults table if we have problems with our
equations.
Here's the problem: IADS forces the table to be exported in comma-
delimited format, BUT many derived equations have commas in them
because of the required syntax for most IADS operators. SO when we re-
import the file IADS treats commas within the syntax of derived
equations as column delimiters, and thus the columns do not line up
properly.
Is there some way to successfully import equations from a CSV file?
Is there some way to export data in tab-delimited format? From my
current understanding of the situation, there is no way to
successfully export and then re-import a set of equations, given the
pervasive use of commas in IADS operators.
MattM
Jun 25, 2009, 10:12:40 AM6/25/09
to IADS
Eric,
If you search on Smart Import, you'll see some of the problems with
using it to import parmeter information, especially derived parameters
with commas in it. In the past what I've done in Excel is change all
the commas to an alph numeric that I'll remember, import the data,
then change all the characters back to commas. It's annoying, but it
worked. I even played with the Smart Import to use another character
as a delimeter, but that didn't seem to work either (maybe you can
figure it out). It wasn't until I simply changed it to a character I
would recognize, and then change it back after importing that I got it
to work finally. That may not work for hundreds of parameters, but it
was a temporary work around for the 40 or so I had to import...
Matt Menard
If you search on Smart Import, you'll see some of the problems with
using it to import parmeter information, especially derived parameters
with commas in it. In the past what I've done in Excel is change all
the commas to an alph numeric that I'll remember, import the data,
then change all the characters back to commas. It's annoying, but it
worked. I even played with the Smart Import to use another character
as a delimeter, but that didn't seem to work either (maybe you can
figure it out). It wasn't until I simply changed it to a character I
would recognize, and then change it back after importing that I got it
to work finally. That may not work for hundreds of parameters, but it
was a temporary work around for the 40 or so I had to import...
Matt Menard
MattM
Jun 25, 2009, 10:14:36 AM6/25/09
to IADS
PS. Jim, are you guys fixing this in any of the newer releases...
Matt Menard
> > pervasive use of commas in IADS operators.- Hide quoted text -
>
> - Show quoted text -
Matt Menard
>
> - Show quoted text -
Eric Fallabel
Jun 25, 2009, 10:22:15 AM6/25/09
to IADS
Thanks Matt, you reminded me of the second half of this problem that I
forgot to mention:
We've figured out some other workarounds using MS Excel to get it to
work. However, Excel seems to have a character limit of 255
characters per cell. Some of our derived equations are longer than
255 characters, and thus get truncated if they are saved in Excel
format. Saving as a *.txt or *.dat file will solve this problem, but
that eliminates our workarounds.
An extremely onerous workaround would be to re-write all of our
derived parameters to be combinations of intermediate derived
parameters which are each less than 255 characters, and then use the
various Excel workarounds, but as you can imagine this is not an
acceptable solution.
-Eric
> > - Show quoted text -- Hide quoted text -
forgot to mention:
We've figured out some other workarounds using MS Excel to get it to
work. However, Excel seems to have a character limit of 255
characters per cell. Some of our derived equations are longer than
255 characters, and thus get truncated if they are saved in Excel
format. Saving as a *.txt or *.dat file will solve this problem, but
that eliminates our workarounds.
An extremely onerous workaround would be to re-write all of our
derived parameters to be combinations of intermediate derived
parameters which are each less than 255 characters, and then use the
various Excel workarounds, but as you can imagine this is not an
acceptable solution.
-Eric
Watzlavick, Robert L
Jun 25, 2009, 11:16:48 AM6/25/09
to ia...@googlegroups.com
Wait a sec... we export and re-import CSV files all the time and haven't had this problem. Upon export, does IADS replace the commas in derived parameters with a special value? If so, maybe that's why we haven't seen it. Most of our users create their user derived parameters in IADS and then export the CSV to send to us. I'm aware of the vertical pipe issue when importing from equations created in Excel which is similar right?
-Bob
-Bob
James Bretz
Jun 25, 2009, 11:58:18 AM6/25/09
to ia...@googlegroups.com
The export from any table in the ConfigTool is of type CSV only, sorry.
Eric, what version of Iads are you running? I *thought* all these problems
were already fixed in versions 6.2+. What should happen is that any cell
containing commas should become "quoted". That is the method in the CSV
spec. If this isn't happening in 6.2+ then we still have a bug and we'll
definitely fix it.
As far as the SmartImport bug, that was caught after 6.2/6.3 and we've
already fixed the problem but we're waiting for the next release.
As Bob mentioned, an import will replace the Pipe (|) with a special
character, but you should only see this special character inside of the
config file and not anywhere inside of the Iads app (it should have replaced
it before presenting to the user).
We'll reverify all these problems and make another post,
Jim
Dale Jones
Jun 25, 2009, 12:03:17 PM6/25/09
to IADS
Hi Eric,
I have a theory on what is going on. I just tried exporting a few rows
from my ParameterDefaults table that have derived equations containing
commas, and I noticed that IADS puts quotes around the derived
equations in the resulting CSV file. When I import that file back into
my configuration, everything appears to be handled correctly. On
import, IADS is seeing the quoted equation as a single field and
importing it correctly.
You mentioned that you "combine a number of these files to be re-
imported to the ParameterDefaults table." Are you using Excel to do
this by chance? In the past I've seen Excel automatically remove
quotations from a field in a CSV file. Example - you have a file with
the following line:
Field1,"Field2",Field3
You open that file in Excel, do some work do it, then save it. The
resulting file then contains:
Field1,Field2,Field3
Where Field2 no longer has quotes around it. I know I've seen this
happen before (maybe in older versions of Excel, I can't remember).
My point is that maybe at some point in the "combining" process, the
quotes around the derived equations are getting removed, and that's
why IADS is having issues re-importing them. Can you check to see that
A) When you do an export from the PD table that it's putting quotes
around the derived equation field to begin with, and B) Using a
regular text editor (such as Notepad or Wordpad, NOT Excel) look at
your "end-game" combined CSV file that you've created and see if those
quotes are still there?
Dale
On Jun 25, 8:16 am, "Watzlavick, Robert L"
I have a theory on what is going on. I just tried exporting a few rows
from my ParameterDefaults table that have derived equations containing
commas, and I noticed that IADS puts quotes around the derived
equations in the resulting CSV file. When I import that file back into
my configuration, everything appears to be handled correctly. On
import, IADS is seeing the quoted equation as a single field and
importing it correctly.
You mentioned that you "combine a number of these files to be re-
imported to the ParameterDefaults table." Are you using Excel to do
this by chance? In the past I've seen Excel automatically remove
quotations from a field in a CSV file. Example - you have a file with
the following line:
Field1,"Field2",Field3
You open that file in Excel, do some work do it, then save it. The
resulting file then contains:
Field1,Field2,Field3
Where Field2 no longer has quotes around it. I know I've seen this
happen before (maybe in older versions of Excel, I can't remember).
My point is that maybe at some point in the "combining" process, the
quotes around the derived equations are getting removed, and that's
why IADS is having issues re-importing them. Can you check to see that
A) When you do an export from the PD table that it's putting quotes
around the derived equation field to begin with, and B) Using a
regular text editor (such as Notepad or Wordpad, NOT Excel) look at
your "end-game" combined CSV file that you've created and see if those
quotes are still there?
Dale
On Jun 25, 8:16 am, "Watzlavick, Robert L"
Brenda Rassmussen
Jun 25, 2009, 12:06:01 PM6/25/09
to IADS
Hi all,
When you export a row or rows from a table in IADS, the file is saved
as a CSV file. If there are commas in the derived equation, it puts
quotes around the entire equation in the CSV file so it can be
imported corrected into IADS. I just tried it with a simple equation
and I opened up the .csv file in WordPad and saw the quotes around the
derived equation. I then deleted the row from my ParameterDefaults
table and reimported the parameter and it was loaded properly. Is
there maybe something that is happening in Excel that would be
removing those quotes from the derived equation field? Eric, can you
try a simple test and export a single parameter with commas in the
equation. Look at the .csv file to make sure that field has quotes.
Then do what you normally do in Excel and before importing back into
IADS, can you look at the .csv file again to see if the quotes are
still there? I'm wondering if how the Excel file is being saved is
removing the quotes which would then cause the import to not work
properly in IADS.
Bob, the reason the pipes work in the derived equations on import/
export is due to the commas used as a delimiter in the file. I'm not
sure what happens on the smart import if you have a pipe as the
delimiter with pipes in the derived equation, but I'll test that out
next.
Brenda
When you export a row or rows from a table in IADS, the file is saved
as a CSV file. If there are commas in the derived equation, it puts
quotes around the entire equation in the CSV file so it can be
imported corrected into IADS. I just tried it with a simple equation
and I opened up the .csv file in WordPad and saw the quotes around the
derived equation. I then deleted the row from my ParameterDefaults
table and reimported the parameter and it was loaded properly. Is
there maybe something that is happening in Excel that would be
removing those quotes from the derived equation field? Eric, can you
try a simple test and export a single parameter with commas in the
equation. Look at the .csv file to make sure that field has quotes.
Then do what you normally do in Excel and before importing back into
IADS, can you look at the .csv file again to see if the quotes are
still there? I'm wondering if how the Excel file is being saved is
removing the quotes which would then cause the import to not work
properly in IADS.
Bob, the reason the pipes work in the derived equations on import/
export is due to the commas used as a delimiter in the file. I'm not
sure what happens on the smart import if you have a pipe as the
delimiter with pipes in the derived equation, but I'll test that out
next.
Brenda
Eric Fallabel
Jun 25, 2009, 12:11:30 PM6/25/09
to IADS
Thanks for the responses. I'll clarify a few things:
-The "combining" of multiple CSV files doesn't have an effect on my
problem, because the problem occurs even if I haven't combined
multiple files. For instance, I can export a table from the
ParameterDefaults table and then immediately re-import the same file,
and have the problem.
-I'm currently running version 6.1. I believe that I can get a copy
of 6.3 from one of our IT folks, so that will be the next thing I do.
-The "combining" of multiple CSV files doesn't have an effect on my
problem, because the problem occurs even if I haven't combined
multiple files. For instance, I can export a table from the
ParameterDefaults table and then immediately re-import the same file,
and have the problem.
-I'm currently running version 6.1. I believe that I can get a copy
of 6.3 from one of our IT folks, so that will be the next thing I do.
Brenda Rassmussen
Jun 25, 2009, 12:13:05 PM6/25/09
to IADS
> Bob, the reason the pipes work in the derived equations on import/
> export is due to the commas used as a delimiter in the file. I'm not
> sure what happens on the smart import if you have a pipe as the
> delimiter with pipes in the derived equation, but I'll test that out
> next.
>
The scenario where you try to import a file that has pipes for the
> export is due to the commas used as a delimiter in the file. I'm not
> sure what happens on the smart import if you have a pipe as the
> delimiter with pipes in the derived equation, but I'll test that out
> next.
>
delimiters and pipes in the derived equation doesn't parse correctly
as I expected. I believe this situation is rare because the IADS
import/export uses commas and even Excel uses commas or tabs for
delimiters when files are saved. The only way I can think of to have
pipes as the delimiter (other than using the actual configuration
file) would be if someone created the file they wanted to import from
scratch and used a pipe as the delimiter.
Can anyone tell me if they are doing something similar to this or are
doing something differently that would result in a pipe being the
column separator? If so, we'll need to re-look at our import/export
scheme. Thanks.
Brenda
Jim Bretz
Jun 25, 2009, 1:21:27 PM6/25/09
to ia...@googlegroups.com
>delimiters when files are saved. The only way I can think of to have
>pipes as the delimiter (other than using the actual configuration
>file) would be if someone created the file they wanted to import from
>scratch and used a pipe as the delimiter.
The rule for a character separated file is: If you use your separator
character inside of a column, you need to quote the entire cell.
So as long as you follow those rules, everything should work ok. If IADS
doesn't import this file correctly then it's a bug,
Jim
Mike Hahn
Jun 25, 2009, 1:50:49 PM6/25/09
to IADS
Just as an aside, laterr versions of EXCEL have single cell limits of
something around 32k.
something around 32k.
Jim Bretz
Jun 25, 2009, 2:22:16 PM6/25/09
to ia...@googlegroups.com
>Just as an aside, laterr versions of EXCEL have single cell limits of
>something around 32k.
>something around 32k.
Yep, I bet it's 32k. Try clicking on the formula bar to edit the cell....
See below.
Excel 2000 (but maybe you have and older version):
Open workbooks Limited by available memory and system resources
Worksheet size 65,536 rows by 256 columns
Column width 255 characters
Row height 409 points
Page breaks 1000 horizontal and vertical
Length of cell contents (text) 32,767 characters. Only 1,024 display
in a cell; all 32,767 display in the formula bar.
Sheets in a workbook Limited by available memory (default is 3 sheets)
Colors in a workbook 56
Cell styles in a workbook 4,000
http://www.google.com/search?hl=en&rlz=1W1GGLD_en&q=Excel+specifications+and+limits&aq=f&oq=&aqi=g2
Jim
Eric Fallabel
Jun 25, 2009, 2:44:21 PM6/25/09
to IADS
I tried the Import/Export with version 6.3, and it worked as
expected.
Unfortunately for the time being, our telemetry rooms are running
v6.1.
> http://www.google.com/search?hl=en&rlz=1W1GGLD_en&q=Excel+specificati...
> Jim
expected.
Unfortunately for the time being, our telemetry rooms are running
v6.1.
> Jim
Reply all
Reply to author
Forward
0 new messages