🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all hot features!
Get Free Trial

sep= metadata handling on csv files

Resolved
Michael T asked on January 4, 2021

is there an option in flexmonster to properly parse or take into account the sep= metadata at the top of CSVs?
e.g. in europe many files use , in place of the decimal in numbers.
so the CSV then uses ; as sep.
 
so many CSVs have sep=’,’ or sep=’;’ etc. on the first line. Is there any facility in flexmonster to read this directive, or at least ignore it if it’s the default sep=’,’
 
Thank you!
 

12 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster January 6, 2021

Hello,
 
Thank you for contacting us.
 
Currently, Flexmonster does not support this type of metadata. CSV files with the “sep=” string on the first line will not be parsed correctly.
 
However, adding support for this notation sounds reasonable. We will include the feature in the minor update ETA Feb 8.
Our team will notify you as soon as the version with the feature is available to download.
 
Also, we want to draw your attention to the fieldSeparator property of the Data Source Object. It can be used as a temporary workaround to define different fields separator to split CSV row.
 
Please contact us if other questions arise.
 
Regards,
Illia

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster February 9, 2021

Hello, Michael,
 
We are pleased to inform you that now it is possible to define the sep= prefix on the first row of the CSV file. It will be considered as fieldSeparator while parsing CSV files.
 
This is available in the 2.8.26 version of Flexmonster: https://www.flexmonster.com/release-notes/.
 
You are welcome to update the component: https://www.flexmonster.com/doc/updating-to-the-latest-version/.
 
Please let us know if it works well for you.
 
Best regards,
Illia

Public
Michael T February 13, 2021

Thank you,
I believe the sep implementation compatible with Excel (e.g. for semicolon) is 
“sep=;” or sep=; (double quotes or no quotes)
 
The new feature in FM
appears to allow single quotes or double quotes around the separator.
sep=’;’ or sep=”;”
 
Is the feature able to be expanded to accept “sep=;” and sep=;  
for the purposes of excel compatibility?
 
thank you
 
 

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster February 15, 2021

Hello,
 
Thank you for reporting this issue.
 
This will be fixed with a nearest minor update ETA Feb 22.
Our team will notify you as soon as the version with a fix is available to download.
 
Regards,
Illia

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster February 22, 2021

Hello, Michael,
 
We are pleased to inform you that the sep= prefix format was changed according to Excel standard. Now it supports the following: sep=; and "sep=;".
 
This is provided in the 2.8.27 version of Flexmonster: https://www.flexmonster.com/release-notes/.
 
You are welcome to update the component.
Here is our updating to the latest version tutorial for guidance: https://www.flexmonster.com/doc/updating-to-the-latest-version/.
 
Please let us know if everything works fine for you.
 
Best regards,
Illia

Public
Michael T February 23, 2021

Thank you for the update.
 
The new feature in FM seems to only recognize the separating character if there is a space after the separating character before the new line.

With “sep=; “
Excel parses the separating token as “; ” (both semicolon and space character) instead of just “;”
Is it possible for FM to recognize sep=; (no trailing space) and “sep=;” instead?

That would be cleaner and more consistent.

Thank you for your help.

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster February 24, 2021

Hello, Michael,
 
Thank you for your feedback.
 
It would be useful for our research if you could provide examples that would demonstrate differences between Flexmonster and Excel when using specific sep= notation formats.
As for now, separating characters without a closing trailing space is parsed correctly both in Flexmonster and Excel.
 
Our team is looking forward to seeing the examples.
 
Kind regards,
Illia

Public
Michael T February 25, 2021

Hello,

Thank you for your reply.

Please see the following examples (also attached as csv)
(flexmonster version February 2021 (v. 2.8.27))
(excel version 16.46 (21021202))

no_space.csv
“sep=,”
a,b,c
1,2,3
4,5,6
Excel convention, treats ‘a’, ‘b’, ‘c’ as separate values
FM treats ‘a,b,c’ as a single value (not parsing correctly)

with_space.csv
“sep=, “
a,b,c
1,2,3
4,5,6
Excel treats ‘sep=, ‘ as a value, and also ‘a,b,c’ as a single value (not parsing correctly)
FM treats ‘a’, ‘b’, ‘c’ as separate values

Public
Milena Pechura Milena Pechura Flexmonster March 1, 2021

Hello, Michael,
 
Thank you for providing us with CSV files and detailed info about component behavior.
It greatly helped us in our investigation.
 
Testing the sent files, we discovered that the cause of the issue is UNIX encoding of the newline symbol on the first line of the CSV file.
 
Our team will provide a fix for this with our upcoming release ETA 9 March.
Hope it works for you.
 
Please let us know if you have any additional questions.
 
Best regards,
Milena

Public
Milena Pechura Milena Pechura Flexmonster March 9, 2021

Hello, Michael,
 
We are happy to let you know that the issue with the "sep=" prefix in files with UNIX newline symbol encoding was fixed.
This is available in the 2.8.28 version of Flexmonster: https://www.flexmonster.com/release-notes/.
 
You are welcome to update the component.
Here is our updating to the latest version guide for assistance: https://www.flexmonster.com/doc/updating-to-the-latest-version/.
 
Please let us know if everything works fine for you.
 
Kind regards,
Milena

Public
Milena Pechura Milena Pechura Flexmonster March 16, 2021

Hi, Michael,
 
Our team was wondering whether the recent fix helped.
Please let us know if everything works as expected now.
 
Looking forward to your response.
 
Kind regards,
Milena

Public
Michael T March 24, 2021

Yes. seems to be working. Thank you for the followup.

Please login or Register to Submit Answer