mvimport comma delimited text file while stripping out commas from numeric values

[Southlander]

Hi all,

So I have some csv files (fields are comma delimited) with numeric data like so:

453.25
25.54
1,254.78
4,547.54

How do you all deal with the commas such that they are not seen as delimiters on the import? I just write some Mivascript for stuff at work from time to time and am not that adept at this type of thing. My script works well once the commas are out (I manually stripped them in a spreadsheet), but this part has me stumped.

Thanks -

[Scot - ScotsScripts.com]

You're looking for the glosub function.

If you're using template code you can take your csv file data, let's say it's l.csv, and go <mvt:assign name="l.csv" value="glosub(l.csv,',','')" />

The glosub will strip the commas out for you. Works the same way using regular mivascript mvassign.

[Southlander]

Hello Scot,

Thanks - yes I have used glosub quite a bit over the years. The issue is how to get the file contents in so that it can work as needed. mvimport does not offer a way to differentiate field separator characters from characters that occur within the fields - that I know of. So -

If it imports these lines using delimiter = "," :

Line 1 546.25
Line 2 1,526.11

Then line 2 is seen as having 2 fields those being 1 and 526.11 whereas Line 1 is correctly seen as having just one.

If there is a way to import the value 1,526.11 using the comma delimiter setting, and NOT have the Miva engine see that comma as a field separator, that would certainly solve it.

Once the data is imported the glosubs are easy.

Thanks -

[wmgilligan]

I hate to suggest it, but what I would do is open the files in excel, then format each row/column to remove the commas, then save as csv and import.

B

[Kent Multer]

The grammar of CSV files doesn't distinguish between commas that are part of a number, and commas that are the separators between numbers. In general, if the fields aren't quoted, there is no algorithmic way to tell, for example, whether "1,234" represents one number or two. Sorry for the bad news.

In your examples, though, all the numbers have a decimal point and two digits after it. If that's always the case, then you can write code that deletes any comma that doesn't appear exactly two digits after a decimal point.

Where is this data coming from? Maybe you can go back to the source, and arrange to get data that doesn't have all those extra commas, or that has quotes in the right places to delimit the numbers. For instance, if you're generating the CSV from a spreadsheet, you can change the spreadsheet formatting so that it doesn't include the unwanted commas in the CSV. Or maybe you can check a box and make the spreadsheet program quote all the fields.

Hope that helps --

[Southlander]

I hate to suggest it, but what I would do is open the files in excel, then format each row/column to remove the commas, then save as csv and import.

B

Hi Bill,

Yes this is what I have been doing for now.
Thanks

[Southlander]

The grammar of CSV files doesn't distinguish between commas that are part of a number, and commas that are the separators between numbers. In general, if the fields aren't quoted, there is no algorithmic way to tell, for example, whether "1,234" represents one number or two. Sorry for the bad news.

In your examples, though, all the numbers have a decimal point and two digits after it. If that's always the case, then you can write code that deletes any comma that doesn't appear exactly two digits after a decimal point.

Where is this data coming from? Maybe you can go back to the source, and arrange to get data that doesn't have all those extra commas, or that has quotes in the right places to delimit the numbers. For instance, if you're generating the CSV from a spreadsheet, you can change the spreadsheet formatting so that it doesn't include the unwanted commas in the CSV. Or maybe you can check a box and make the spreadsheet program quote all the fields.

Hope that helps --

Hi Kent,

Yes I had considered altering the file in Mivascript before an mvimport to strip out the extra commas. What is the command in mivascript to do that though - is what stumped me. (?) Does Mivascript have a way to simply glosub a file directly? That is glosub - and re-write the file directly (not import)?

What would be cool - for Miva to add - is if mvimport had an extra parameter such as this:

<mvimport format ="describe field pattern format here"> so that it could handle these things similar to a way a spreadsheet handles it.

Thanks -

[Southlander]

OK I relented and just resolved to telling our people to use a spreadsheet to change the delimiter for now. Open the CSV in Libre Office, then re-save with the pipe ( | ) as the delimiter. From there the Mivascript I wrote easily glosubs out the '$' and the commas from the currency field values on the import. Works well.

Good enough. Thanks.