I would be grateful if you check the box next to the answer - mark it as correct. This will allow other users to quickly find a solution to similar problems.
I have found out one problem. It looks like by default macro opens with UTF-8 character set.
Sample data:
ščžŠČŽ
are opened as:
�螊Ȏ
In my case in Text Import dialog I always select the following:
-
Character set: Eastern Europe (Windows-1250/WinLatin 2)
-
Language: Slovenian
-
From row: 1
-
Separator Options: Semicolor is checked, String delimiter: "
-
Other Options: Format quoted fields as text is checked
All other options are unselected or unchecked.
And unfortunately one more problem. Macro trims spaces at the beginning and at the end of field. I only want to trim spaces at the end of field. My regular expression “\s+$” means delete spaces at the end of field.
It isn’t a problem 
Just replace this line OpenProp(1).value="59,34,33,1,,1060,false,false,true,false,true" 33=Windows-1250, 1060=Slovenian. All codes (and some other information about FilterOptions) you can find here
I have solved the trim problem by replacing Trim function with RTrim in your macro.
Also your above solution fixes character set problems.
I am marking your post as solution to the problem.
Thanks a lot for your help.
I have found one more problem. If source csv file has numbers using macro are always imported as text.
Source file:
DATE;NAME;VALUE
"2018-07-10";Jack;1
"2018-07-11";Jordan;2
"2018-07-12";Andrew;3
I came out with this:
OpenProp(1).value="59,34,33,1,1/1/2/1/3/1,1060,true,false,true,false,true"
or without parameter 5
OpenProp(1).value="59,34,33,1,,1060,true,false,true,false,true"
But numbers are always imported as text. See: https://i.imgur.com/tlXY740.png
It seems to me that your problem begins to go beyond the standard of this resource “question-answer”, you are trying to discuss various options for the problem (in your initial question, nothing was said about Jack and Andrew with any numerical values). Be kind, use for this any forum - members of the community speak almost all the languages of the world.
Sorry it looks like I am adding new questions. My question sample data was really simple, because when I import data manually I only need to click on OK button all other settings are accepted from my previous session selections. I see macro does not accept my previous selections, so I never know what kind of real life data are going to be incorrectly imported using macro. Now I updated sample data with more real life columns. I studied Filter_options, but numbers are imported as strings. Bug?
String “59,34,33,1,1/2/2/2/3/5/4/2/5/10/7/2/8/10,1060,false,false,true,false,false” with your updated example work fine
JonhSUN, I did as you have instructed, but it does not work. When manually import csv file number fields are correctly imported, but when macro is run numbers are imported as “strings”. I have recorded video to prove this. This looks like a bug to me.
Can you please see the short video animation:
There is no last part - how the macro doesn’t work. In addition, with such a manual import (without setting the format of any columns), all fields remained the default “Standard”. In my last comment, the numeric fields are imported as “US English”… Please write to johnsun na-znak i dot ua
Sorry not to be clear enough. When I manually import (and leave all fields as “Standard”) the numeric fields are imported as numbers (right aligned) and on them I can execute mathematical functions like sum. But when doing the same with macro numbers are imported as strings (left aligned) and on such fields it is impossible to do mathematical functions like sum, they always return 0. I have recorded one more video to prove this:
You are wrong in thinking that not clear enough - even from the first video, it was clear where you are making a mistake. When importing, in the filter settings window, you immediately click on the OK button. Select the columns with numbers that are imported incorrectly, from the “Column type” drop-down list select “US English”, and then start the import. And once again - such a long discussion in the comments, we’ll break the format of this resource, it’s not customary to do so here.
John, now I see what you mean selecting “Column type = US English” for two numeric fields. I did this and got from getMeCode the string: “59,34,33,1,5/10/6/10,1060,true,false”. I replaced the “OpenProp(1).value” with this value in openAndTrimCSV macro, save it and run from cmd and the problem is the same - macro opens strings instead of numbers. It is still weird to me why should we chose “US English” in the first place, Standard should work, IMHO.
“opens strings instead of numbers” - in both columns, with “normal” numbers and with “quoted” numbers? Did you try to uncheck “Format quoted fields as text”? Did you try to check “Detect special numbers”? And you are right - Standard should work. And it works for me
First of all I upgraded to LibreOffice 6.1.2.1 and deleted my profile C:\Users[user]\AppData\Roaming\LibreOffice\4 and restarted LibreOffice. I tried uncheck “Format quoted fields as text” and the result is the same. I also tested the “Detect special number” and run getMeCode, copied OpenProp(1).value to openAndTrimCSV macro and rerun from cmd. The same problem: NUMBER and DEC_COMMA_DELIMITERED are still imported as strings. What else could be the difference between my and your system?
The only thing that comes to mind is that we use different CSV files for testing: you use your file, and I am the one that I created according to your instructions. This is one of the reasons that I offered to transfer this discussion to email (see a comment with word “na-znak”)
I am using the file from my first post and that can also be seen from one of my videos above.
Sorry I just can’t figure it out what is your e-mail address from “johnsun na-znak i dot ua”. What is domain?
i.ua - it is domain Laconic, right?
I send and e-mail now. I hope I recognized the correct form. I have send you a file sample I use from the first post. Any idea why we are having the same software, but different result, would be welcome.