CSV Comma-Delimited v Comma-Separated issue

Post Reply
rhd_ole
Member
Posts: 92
Joined: Mon Jan 24, 2022 5:36 pm

CSV Comma-Delimited v Comma-Separated issue

Post by rhd_ole »

First time posting on here, but I've been using this as a resource for a long time.

I'm working on a flow that I'm taking metadata from a submit point and writing it privatedata to a CSV or TXT file for input into our MIS system to create a new job from that info. The format must be comma-delimited. However when I write the info the txt or csv I get comma separated format vs the comma-delimited. The only difference I see is a trailing comma at the end of each row. I added that extra comma thinking that would solve it but the file still shows it's comma separated.

Does anyone know how to get an actual comma-delimited file out of switch?


The format is like this:

description,promise_date,job_type,
F123456,2022-06-30,5013,


When you open in excel it shows as the comma separated format.

I've tried several apps, ASI DataLogger, Create text File and CSV Creator all give me the same separated file, the apps state they are using the UFT-8 CSV format. Thanks in advance.
Color Science & Workflow Automation
User avatar
JimmyHartington
Advanced member
Posts: 278
Joined: Tue Mar 22, 2011 7:38 am

Re: CSV Comma-Delimited v Comma-Separated issue

Post by JimmyHartington »

Hi

Can you post a file of your desired output and file of what you get now?
rhd_ole
Member
Posts: 92
Joined: Mon Jan 24, 2022 5:36 pm

Re: CSV Comma-Delimited v Comma-Separated issue

Post by rhd_ole »

You bet. Here are both. I named each accordingly

CSV Issue Files.zip
(1.21 KiB) Downloaded 220 times
Color Science & Workflow Automation
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: CSV Comma-Delimited v Comma-Separated issue

Post by freddyp »

I do not understand what the difference would be between a comma-separated and a comma-delimited file. The two files you sent are identical, so I really do not see the point.

There is one thing I can think of that could play a role here: what if the data itself contains a comma? That messes up the number of columns in the file of course. The csv specification says that values containing a comma must be enclosed by double quotes. That is not the case here, but what if your MIS is expecting all values to be double quoted? I am just guessing. A proper error message from the MIS could help.
rhd_ole
Member
Posts: 92
Joined: Mon Jan 24, 2022 5:36 pm

Re: CSV Comma-Delimited v Comma-Separated issue

Post by rhd_ole »

freddyp wrote: Wed Jun 29, 2022 1:33 pm I do not understand what the difference would be between a comma-separated and a comma-delimited file. The two files you sent are identical, so I really do not see the point.

There is one thing I can think of that could play a role here: what if the data itself contains a comma? That messes up the number of columns in the file of course. The csv specification says that values containing a comma must be enclosed by double quotes. That is not the case here, but what if your MIS is expecting all values to be double quoted? I am just guessing. A proper error message from the MIS could help.
I wish I knew the difference also since I've never had this issue before. The only difference I see is the trailing comma. When you do a save-as the file in Excel (the comma separated file) you see the that format, then also CSV UTF-8 comma-delimited format as well. When I save as the comma-delimited format it works.

The data will never contain a comma so I'm not worried about the part.

The error out of the MIS is "CSV format error when reading data." If I open the output file from Switch that is created and resave as actual comma-delimited format out of Excel it works when imported to the MIS.''

I really hope I can solve this.
Color Science & Workflow Automation
freddyp
Advanced member
Posts: 1008
Joined: Thu Feb 09, 2012 3:53 pm

Re: CSV Comma-Delimited v Comma-Separated issue

Post by freddyp »

Next suggestion: check the line endings. Does your MIS expect LF or CRLF (carriage return, line feed) or is it not important? The code for LF is 10 (hex 0xA), for carriage return it is 13 (hex 0xD). LF only is typical for Mac and Unix, CRLF is typical for Windows.

Looking at a hex dump of the files you sent the line endings are LF. What about the file that works?

If your MIS is Windows-based I think this is your problem.
rhd_ole
Member
Posts: 92
Joined: Mon Jan 24, 2022 5:36 pm

Re: CSV Comma-Delimited v Comma-Separated issue

Post by rhd_ole »

freddyp wrote: Wed Jun 29, 2022 2:26 pm Next suggestion: check the line endings. Does your MIS expect LF or CRLF (carriage return, line feed) or is it not important? The code for LF is 10 (hex 0xA), for carriage return it is 13 (hex 0xD). LF only is typical for Mac and Unix, CRLF is typical for Windows.

Looking at a hex dump of the files you sent the line endings are LF. What about the file that works?

If your MIS is Windows-based I think this is your problem.
We are using EFI Pace as our MIS. It runs on Debian/Linux. I will have the guy who manages the MIS look into the line endings.

I do know it will also accept .xlsx format as well but I don't know how to get that out of Switch.
Color Science & Workflow Automation
User avatar
JimmyHartington
Advanced member
Posts: 278
Joined: Tue Mar 22, 2011 7:38 am

Re: CSV Comma-Delimited v Comma-Separated issue

Post by JimmyHartington »

For converting and manipulating datasets as CSV and XLSX files I use www.easydatatransform.com.
It can be run via Execute Command.
So this should be able to convert to XLSX.
There also is the Microsoft Excel configurator, if you have Excel installed on the system. Have never tested if it accepts CSV as input. But it can of course save to XLXS
User avatar
JimmyHartington
Advanced member
Posts: 278
Joined: Tue Mar 22, 2011 7:38 am

Re: CSV Comma-Delimited v Comma-Separated issue

Post by JimmyHartington »

When I look at the sample files I can see both is UTF-8, but one is "with BOM". I do not know what it means, but perhaps it helps in finding the error.

Image
bens
Advanced member
Posts: 252
Joined: Thu Mar 03, 2011 10:13 am

Re: CSV Comma-Delimited v Comma-Separated issue

Post by bens »

BOM is "Byte Order Marker"; it consists of (usually) two bytes at the start of the file that differentiate between little-endian and big-endian encoding.
rhd_ole
Member
Posts: 92
Joined: Mon Jan 24, 2022 5:36 pm

Re: CSV Comma-Delimited v Comma-Separated issue

Post by rhd_ole »

So thanks for all the digging in this. Last night I thought of something while laying in bed, typical right? Anyway I tried it this morning and it worked but still not sure of the real issue here.

What I did was basically make a template that had only the headers in it, saved as true comma delimited CSV out of Excel. I put that file in the hotfolder where the CSV would normally be created by Switch and had the DataLogger app just write the PD to that CSV. When this file was imported into Pace (our MIS) it worked.

Odd workaround but I'm still going to try and figure out a way to do it naturally since this will be expanded on a lot, and the need for it will be common with our workflows.
Color Science & Workflow Automation
Post Reply