|
Erik,
I too have Excel 2010. And I live in Sweden in northern Europe so
I have the same problem with Excel CSV files as you have.
I created an Excel file, JMRI_Users_Erik84750_test.xlsx, and
stored it as a CSV file JMRI_Users_Erik84750_test.csv and as a TAB
separated text file JMRI_Users_Erik84750_test.txt. I start
PanelPro, open the LogixNG Tables table and then click "Add". I
click the CSV file name button and selects the textfile (without
renaming it to CSV) and opens it and then click on the Create
button. Now the table opens correctly. Note that I didn't renamed
the txt file to csv.
See my attached files for reference.
Daniel
On 2025-05-26 20:09, Erik84750 via
groups.io wrote:
toggle quoted message
Show quoted text
On Mon, May 26, 2025 at 06:59 PM, danielb987 wrote:
Erik,
Which version of Excel do you have? I have Excel 2010 and
it supports export to TAB separated CSV file
I have MS Office 2010.
Two versions of export to .csv are presented when using "save
as":
1. CSV (MS-DOS) *.csv
2. CSV separated by list-separation sign *.csv
?
In both cases the exported .csv file uses semi-colon (";"),
as described in my previous message. Summary: due to the fact
that EU decimal (,) and thousands (.) separation is different
from US separation, a semi-colon is used as record separation.
If I want to use tab-separated records I have to export as
"Text (tab separated) *.txt".
Causing my file to loose any Excel link.
?
Hence my upcoming request to have this third option when
retrieving .csv files, (besides "tab" and "comma" separation)
for "semi-colon" separation.
|
Does the input also understand tab separated files? If so, that gets around the EU character issue in seeing the right number of fields. But that would still leave the question of if the importer would recognize the EU embedded characters in the number or time fields correctly. The issue may span a couple of different stages of this process. ? -Ken Cameron, Member JMRI Dev Team
? ?
|
Ken,
> But that would still leave the question of if the importer
would recognize the EU embedded characters in the number or time
fields correctly. The issue may span a couple of different stages
of this process.
The import of CSV files treats each cell as a string. If the user
wants to treat the value as an integer or a floating point number,
he needs to convert it. LogixNG has functions for converting a
string to an integer or a float.
Daniel
On 2025-05-27 03:40, Ken Cameron wrote:
toggle quoted message
Show quoted text
Does the input also understand tab
separated files? If so, that gets around the EU character
issue in seeing the right number of fields. But that would
still leave the question of if the importer would recognize
the EU embedded characters in the number or time fields
correctly. The issue may span a couple of different stages of
this process.
?
-Ken
Cameron, Member JMRI Dev Team
?
?
|
On Tue, May 27, 2025 at 03:33 AM, danielb987 wrote:
I created an Excel file, JMRI_Users_Erik84750_test.xlsx, and stored it as a CSV file JMRI_Users_Erik84750_test.csv
Your file JMRI_Users_Erik84750_test.csv (see first screenshot below) opens as follows (second screenshot below) in LogixNG Tables, with "comma separated tabs"
To me it does not seem a correct import into LogixNG Tables: "Row 1" is not separated from "1,23" etc.. ?
?
?
?
When I use your JMRI_Users_Erik84750_test.xlsx and want to save it as .csv, I have two .csv options when doing a "save as":
1. CSV (MS-DOS) *.csv
2. CSV separated by list-separation sign *.csv
Which of these options did you use?
?
When opening Excel "Options", "Advanced", "System record separation sign" box, is it "ticked" in you case? Do you use a comma to separate decimals?
?
Erik
?
?
|
Erik,
> Your file JMRI_Users_Erik84750_test.csv (see first
screenshot below) opens as follows (second screenshot below) in
LogixNG Tables, with "comma separated tabs"
> To me it does not seem a correct import into LogixNG Tables:
"Row 1" is not separated from "1,23" etc.. ?
It is correct. Header A, Header B, Header C is in the Excel file
and so are Row 1, Row 2 and Row 3. The reason I have these cells
are that the first row and the first column is treated as column
headers and row headers by LogixNG. See the documentation about
tables:
> When I use your JMRI_Users_Erik84750_test.xlsx and want to
save it as .csv, I have two .csv options when doing a "save as":
> 1. CSV (MS-DOS) *.csv
> 2. CSV separated by list-separation sign *.csv
> Which of these options did you use?
I store the text file as a TAB separated text file, not as a CSV
file.
Daniel
On 2025-05-27 04:29, Erik84750 via
groups.io wrote:
toggle quoted message
Show quoted text
On Tue, May 27, 2025 at 03:33 AM, danielb987 wrote:
I created an Excel file,
JMRI_Users_Erik84750_test.xlsx, and stored it as a CSV file
JMRI_Users_Erik84750_test.csv
Your file JMRI_Users_Erik84750_test.csv (see first screenshot
below) opens as follows (second screenshot below) in LogixNG
Tables, with "comma separated tabs"
To me it does not seem a correct import into LogixNG Tables:
"Row 1" is not separated from "1,23" etc.. ?
?
?
?
When I use your JMRI_Users_Erik84750_test.xlsx and want to
save it as .csv, I have two .csv options when doing a "save as":
1. CSV (MS-DOS) *.csv
2. CSV separated by list-separation sign *.csv
Which of these options did you use?
?
When opening Excel "Options", "Advanced", "System record
separation sign" box, is it "ticked" in you case? Do you use a
comma to separate decimals?
?
Erik
?
?
|
On Tue, May 27, 2025 at 05:12 AM, danielb987 wrote:
It is correct. Header A, Header B, Header C is in the Excel file and so are Row 1, Row 2 and Row 3. The reason I have these cells are that the first row and the first column is treated as column headers and row headers by LogixNG.
I am confused now Daniel.
?
I the Table import of JMRI_Users_Erik84750_test.csv the resulting Table does not place "Row 1"? and "1,23" and "2,46 and "Hello" each in its own record cell?? ..as is the case with your example file JMRI_Users_Erik84750_test.txt?
|
Erik,
> I the Table import of JMRI_Users_Erik84750_test.csv the
resulting Table does not place "Row 1"? and "1,23" and "2,46 and
"Hello" each in its own record cell?? ..as is the case with your
example file JMRI_Users_Erik84750_test.txt?
That's because that file was exported as CSV, not as a TAB
separated text file. So don't use that file. Maybe I shouldn't
have sent that file at all.
Daniel
On 2025-05-27 05:28, Erik84750 via
groups.io wrote:
toggle quoted message
Show quoted text
On Tue, May 27, 2025 at 05:12 AM, danielb987 wrote:
It is correct. Header A, Header B, Header C is in
the Excel file and so are Row 1, Row 2 and Row 3. The reason I
have these cells are that the first row and the first column
is treated as column headers and row headers by LogixNG.
I am confused now Daniel.
?
I the Table import of JMRI_Users_Erik84750_test.csv the
resulting Table does not place "Row 1"? and "1,23" and "2,46 and
"Hello" each in its own record cell?? ..as is the case with your
example file JMRI_Users_Erik84750_test.txt?
|
Ok Daniel, understood.
?
How do you modify (add values) to the JMRI_Users_Erik84750_test.txt file?
?
I tried as follows: open this .txt file, and add a new row with tab separated values (see screen shot below). Then reopen Panel Pro and then resulting Table below at bottom.
The result is not ok.
?
|
Erik,
> How do you modify (add values) to the
JMRI_Users_Erik84750_test.txt file?
I recommend that you always do the changes in Excel in the
original Excel document and then export that document to a TAB
separated text file. As long as the text file has the same
filename, it works fine. You need to restart JMRI for the changes
to take effect.
> I tried as follows: open this .txt file, and add a new row
with tab separated values (see screen shot below). Then reopen
Panel Pro and then resulting Table below at bottom.
> The result is not ok.
Attach the file to a reply and I will take a look at it.
Daniel
On 2025-05-27 08:02, Erik84750 via
groups.io wrote:
toggle quoted message
Show quoted text
Ok Daniel, understood.
?
How do you modify (add values) to the
JMRI_Users_Erik84750_test.txt file?
?
I tried as follows: open this .txt file, and add a new row
with tab separated values (see screen shot below). Then reopen
Panel Pro and then resulting Table below at bottom.
The result is not ok.
?
|
Ok, so best is to keep an original Excel file: I read you make any changes to that file, not to the resulting .txt file (as I had done in my previous message).
?
But when "save as" in Excel then the original Excel .xlsx file disappears into a .txt file. How do you keep the original .xlsx file?
?
Attached is the .txt file as I had modified for my previous message.
?
?
|
Erik,
To retain the Excel data format, do a two step save. ?The first save is for the Excel format. ?The "save as" creates the text file. ?The final step is to quit Excel. ?This is important since it cleans up the environment. ?The next time you want to update the CSV file you start Excel and load the Excel version. ?After making the changes, do the save/save as/quit process.
Dave Sand
----- Original message ----- Subject: Re: [jmriusers] LogixNG table editor and CSV question #logixng Date: Tuesday, May 27, 2025 9:13 AM
Ok, so best is to keep an original Excel file: I read you make any changes to that file, not to the resulting .txt file (as I had done in my previous message). ? But when "save as" in Excel then the original Excel .xlsx file disappears into a .txt file. How do you keep the original .xlsx file? ? Attached is the .txt file as I had modified for my previous message. ? ?
Attachments: - JMRI_Users_Erik84750_test.txt
|
Erik,
> Ok, so best is to keep an original Excel file: I read you
make any changes to that file, not to the resulting .txt file (as
I had done in my previous message).
Yes.
> Attached is the .txt file as I had modified for my previous
message.
In your new row, you have spaces instead of TABs between the
columns. Many text editors have a setting whenever it should use
TABs or replace TABs with spaces.
Daniel
On 2025-05-27 16:13, Erik84750 via
groups.io wrote:
toggle quoted message
Show quoted text
Ok, so best is to keep an original Excel file: I read you
make any changes to that file, not to the resulting .txt file
(as I had done in my previous message).
?
But when "save as" in Excel then the original Excel .xlsx
file disappears into a .txt file. How do you keep the original
.xlsx file?
?
Attached is the .txt file as I had modified for my previous
message.
?
?
|
On Tue, May 27, 2025 at 05:24 PM, danielb987 wrote:
Many text editors have a setting whenever it should use TABs or replace TABs with spaces.
Right, finally I got it. I had to tick "Hard Tabs" box.
?
|
@danielb987 After our entire conversation here I am left with the feeling that my initial point, namely request JMRi developpers to provide a third option, besides "Tab separated" or "comma separated", namely "semi-colon separated" would save having to use the .txt file workaround.
?
What is your take?
?
Erik
?
|
Erik,
It would be good to have the feature, but someone will have to
implement it. And I don't have time to do it.
Daniel
On 2025-05-27 17:42, Erik84750 via
groups.io wrote:
toggle quoted message
Show quoted text
@danielb987 After our entire conversation here I am left with
the feeling that my initial point, namely request JMRi
developpers to provide a third option, besides "Tab separated"
or "comma separated", namely "semi-colon separated" would save
having to use the .txt file workaround.
?
What is your take?
?
Erik
?
|
Daniel,
I have figured out the change to?AbstractNamedTable.java to support the semicolon separator. ? Based on that, I should be able to add the semicolon option to the LogixNG Table add dialog.
Dave Sand
----- Original message ----- Subject: Re: [jmriusers] LogixNG table editor and CSV question #logixng Date: Tuesday, May 27, 2025 3:24 PM
Erik, It would be good to have the feature, but someone will have to
implement it. And I don't have time to do it. Daniel On 2025-05-27 17:42, Erik84750 via
groups.io wrote: @danielb987 After our entire conversation here I am left with
the feeling that my initial point, namely request JMRi
developpers to provide a third option, besides "Tab separated"
or "comma separated", namely "semi-colon separated" would save
having to use the .txt file workaround. ? What is your take? ? Erik ?
|
Erik,
Dave Sands changes are approved and will be included in the next
JMRI test release 5.11.7 and the next JMRI production release
5.12.
Daniel
On 2025-05-28 04:21, Dave Sand wrote:
toggle quoted message
Show quoted text
Daniel,
I have figured out the change to?AbstractNamedTable.java to
support the semicolon separator. ? Based on that, I should be
able to add the semicolon option to the LogixNG Table add
dialog.
Dave Sand
----- Original message -----
Subject: Re: [jmriusers] LogixNG table editor and CSV
question #logixng
Date: Tuesday, May 27, 2025 3:24 PM
Erik,
It would be good to have the feature, but someone will have
to implement it. And I don't have time to do it.
Daniel
On 2025-05-27 17:42, Erik84750
via groups.io wrote:
@danielb987 After our entire conversation here I am left
with the feeling that my initial point, namely request JMRi
developpers to provide a third option, besides "Tab
separated" or "comma separated", namely "semi-colon
separated" would save having to use the .txt file
workaround.
?
What is your take?
?
Erik
?
|
Thank you all! Great to see such smart people on board here :).
?
Erik
?
|
Ken Cameron
Erik84750