Mivascript and importing CSV

[ids]

Ran into a data source that only can export a comma-delimited file. Of course, the export has descriptions. Descriptions are wrapped in quotes. The descriptions have commas.

Am I stuck doing multiple parsing to separate the fields as they are intended or is there a CSV decode type of function that knows how to parse the fields wrapped in quotes and ignore the comma delimiter within the quotes?

Scott

[dcarver]

No we don't have any functions that would decode the data for you. You'd most likely have to write your own if you wanted to do that in MivaScript.

[William Davis]

Tip:

If you are using Windows PC:

1. Go to Control Panel > Region > Additional Settings > List Separator and change it from "," comma to "|" (pipe).

2. Go Miva Admin > Data Management > Import/Export > Export Products To Flat File > Then change the File Delimiter to "|" (pipe), and export the file.

3. Open file in Excel, etc.

Let me know if that works for you.

I think this could be easily resolved if Miva left the Description field in the last column of export file, or provide a feature to arrange order of export fields.

[Kent Multer]

Here's some code that I've used to solve that puzzle in a number of modules.

CSVFields(text, labels, trim)

It scans the text character-by-character to parse the commas, so it's not super-fast; but it works, and it's small.
"text" is one line of text from the CSV file.

• "labels" is a string containing a comma-separated list of field names. These are Miva variable names: only letters, digits, and underscores. You can use white-space characters adjacent to the commas, in case you have a long list that you want to break across multiple lines.
• "trim" is a flag that, if true, will cause the function to trim() each field value.

The function returns an object with members having the names listed in the "labels" parameter.

This is weird ...
I tried to paste the code directly into this message, but apparently we're no longer allowed to do that; I got a "Forbidden" error.
I tried to upload it as an attachment, but I got an "Error uploading image" message for that.
Same results with both Firefox and Chrome. Is the forum broken?

Scott, if you'd like a copy of this, you can email me at Kent "at" The Magic M "dot" com.

Hope that helps --

[ids]

Thanks Kent. I'll send you an email offline. I've done that exercise in other languages, but haven't needed to in a web based tool.

[ids]

There is actually a different issue with files from this data source. These files have potentially really long rows because of detailed description type fields. There can be multiple description fields. There are only 58 fields and consistent. Unused fields are just a comma.

The file works fine in Data Management = Import settings. IOW, creating a new import and manually mapping loads the files without any problem. However I need to script this. MvImport isn't finding the end of the line correctly. A file with 10 rows becomes 205. File_read() and parsing with a LF or CRLF has similar problems.

<MvIMPORT FILE = "{ l.myfilepath }" FIELDS = "linestring" DELIMITER = "">
<MvASSIGN NAME = "linerecord" INDEX = "{l.count}" VALUE = "{trim(linestring)}">
blah...

or

<MvASSIGN NAME = "g.FilenameReturn" VALUE = "{ file_read(l.myfilepath, 'data', g.data ) }">
<MvASSIGN NAME = "l.counter" VALUE = "1">
<MvASSIGN NAME = "l.line" VALUE = "{ gettoken(g.data,asciichar(10),l.counter) }">
<MvWHILE EXPR = "{ NOT ISNULL l.line }">
blah...

For me this scenario has never been complicated until this. MvImport and File_Read has always worked. So, what is the Import Settings script doing to read the file correctly? It even handles the Quoted fields correctly.

Thanks,

Scott

[Bruce - PhosphorMedia]

Ran into something similar. Spend too long trying to figure this out. Googled it and some wise ass named bruce had this solution which worked.

<mvt:assign name="l.settings:product:name" value="glosub(l.settings:product:name, '\r\n', '')" />

[ids]

Thanks. The bad news, No Joy with this solution.
The good news... None so far.

[Bruce - PhosphorMedia]

did you also try /r /n on their own..

yea, it might not be the issue.

Some other ideas
Do you have a text editor that can display non-printable characters?
Can you force the Character Set while performing the operation?

[Kent Multer]

If a 10-row file comes in with 205 rows, there are probably line-breaking characters inside the quoted text fields. I don't think MvIMPORT handles those; so if that's the problem, you need to write code that can detect an un-terminated text field, and combine multiple MvIMPORTs into one long line of CSV. It wouldn't surprise me to find that the store's built-in delimited-import feature has that ability.

The parsing function I mentioned before doesn't do multiple lines, but it wouldn't be too hard to add that ability. Or you might consider writing a new-style import module that uses the advanced features. The LSK includes the CSV product import module; I've used that as the starting point for a couple of projects.

[lesliekirk]

did you also try /r /n on their own..

yea, it might not be the issue.

Some other ideas
Do you have a text editor that can display non-printable characters?
Can you force the Character Set while performing the operation?

I was wondering the same thing. Maybe you could email me a small portion of the file, I can open it with what I have and see if there might be anything I see that could help. I know that when I open a CSV with OpenOffice (Mac). It has options that can be set for the Import. Maybe those could help you figure out what you are up against? Just sort of thinking out loud here.

[ids]

I have multiple editors that can display hidden character. These files appear to have none. Kent's characterization of un-terminated is likely the case. I haven't needed to deal much with the new import modules from the LSK but that is now the plan. Hopefully this isn't yet another rabbit hole.

Scott

[Kent Multer]

The product import module, mmlsk-productimport.mv, is large and complex, but it's well-written. I've actually been meaning to post a note on this forum to give a tip of the hat to the person who wrote it. In case he's reading this, Thanks! I've spent enough time digging through the LSK to be aware of some flaws; but this module is nice, clean code.
When I wrote that I "used it as a starting point," that was a bit of an exaggeration. More precisely, I took a copy of the module and customized it. I added the features I needed; and I disabled some unneeded features by removing admin settings, not by deleting large chunks of code. It seemed safer that way. :^)

[ids]

Thanks Kent.

This suggestion has worked well so far.

Scott