Why does the macro recorder not handle this CSV-conversion? What is hidden? How to solve?

[Razzy]

I'm a beginner so I start simple. Why is this not working and how can I fix it? Been at this a whole week, cant sleep guys!

*I need a CSV file to UTF-8 with BOM to ANSI.
*I am hoping to use VBA to solve this.
*Alternatively VB Script (open file in notebook, save as ANSI - can this be coded? Works fine manualy)

Notes:
1. When I do this manualy it works fine.
- Open file in Excel, go threw the display options, save as CSV with Semicolon seperation.

2. When I record my actions it does not save properly.
- It changes ";" to "," and decimal from "," to ".".

3. The idea is that I will build the action into a loop and converge a set of CSV files. I can share this code when I get this to work.

Greetings from Norway


Untouched.csv CSV UTF-8 with BOM

Sideetikett;Art. nr.;Type;Length;Hight;With;Thickness;Utstøping;Concrete
BA-E01;5031;Utstøping B30;23,12;0;0;0,25;32,49;8,12
BA-E02;5601;Dekke;12,34;0,4;0,3;0,25;;
BA-E03;5034;Dekke;11,2;0;0;0,25;5,96;1,49
BA-E04;5031;Utstøping B30;7,51;0;0;0,25;2,24;0,56
BA-E05;5033;Utstøping B45;6,8;0;0;0,25;1,85;0,46
BA-E06;5031;Dekke;6,54;0;0;0,27;1,41;0,38
BA-E07;5014;Dekke;6,47;0;0;0,25;;
BA-E08;5031;Utstøping bad B30;4,59;0;0;0,19;0,89;0,17
BA-E09;5079;Dekke;3,14;0;0;0;0,45;0
BA-E10;5604;Beam;3,03;0;0;0,25;;
BA-E11;5086;Dekke;2,35;0;0;0,25;;
BA-E12;5086;Dør;2,32;0;0;0,25;;
BA-E13;5086;Dør;2,14;0;0;0,25;;
BA-E14;2082;Dør;1,8;0;0;0,25;;
BA-E15;5600;Søyle;1,59;0,4;0,3;0,25;;
BA-E16;5084;Dekke;1,59;0;0;0,25;;

Goal.csv ANSI, done manualy by open and save Excel-file og open in notebook and save with ANSI.

Sideetikett;Art. nr.;Type;Length;Hight;With;Thickness;Utstøping;Concrete
BA-E01;5031;Utstøping B30;23,12;0;0;0,25;32,49;8,12
BA-E02;5601;Dekke;12,34;0,4;0,3;0,25;;
BA-E03;5034;Dekke;11,2;0;0;0,25;5,96;1,49
BA-E04;5031;Utstøping B30;7,51;0;0;0,25;2,24;0,56
BA-E05;5033;Utstøping B45;6,8;0;0;0,25;1,85;0,46
BA-E06;5031;Dekke;6,54;0;0;0,27;1,41;0,38
BA-E07;5014;Dekke;6,47;0;0;0,25;;
BA-E08;5031;Utstøping bad B30;4,59;0;0;0,19;0,89;0,17
BA-E09;5079;Dekke;3,14;0;0;0;0,45;0
BA-E10;5604;Beam;3,03;0;0;0,25;;
BA-E11;5086;Dekke;2,35;0;0;0,25;;
BA-E12;5086;Dør;2,32;0;0;0,25;;
BA-E13;5086;Dør;2,14;0;0;0,25;;
BA-E14;2082;Dør;1,8;0;0;0,25;;
BA-E15;5600;Søyle;1,59;0,4;0,3;0,25;;
BA-E16;5084;Dekke;1,59;0;0;0,25;;

Attempt with macro.csv ANSI, by macro recorder, se errors

Sideetikett,Art. nr.,Type,Length,Hight,With,Thickness,Utstøping,Concrete
BA-E01,5031,Utstøping B30,23.12,0,0,0.25,32.49,8.12
BA-E02,5601,Dekke,12.34,0.4,0.3,0.25,,
BA-E03,5034,Dekke,11.2,0,0,0.25,5.96,1.49
BA-E04,5031,Utstøping B30,7.51,0,0,0.25,2.24,0.56
BA-E05,5033,Utstøping B45,6.8,0,0,0.25,1.85,0.46
BA-E06,5031,Dekke,6.54,0,0,0.27,1.41,0.38
BA-E07,5014,Dekke,6.47,0,0,0.25,,
BA-E08,5031,Utstøping bad B30,4.59,0,0,0.19,0.89,0.17
BA-E09,5079,Dekke,3.14,0,0,0,0.45,0
BA-E10,5604,Beam,3.03,0,0,0.25,,
BA-E11,5086,Dekke,2.35,0,0,0.25,,
BA-E12,5086,Dør,2.32,0,0,0.25,,
BA-E13,5086,Dør,2.14,0,0,0.25,,
BA-E14,2082,Dør,1.8,0,0,0.25,,
BA-E15,5600,Søyle,1.59,0.4,0.3,0.25,,
BA-E16,5084,Dekke,1.59,0,0,0.25,,

Straight from macro recorder:

Sub Makro1()
    Workbooks.OpenText Filename:="C:\Users\FR\Downloads\Untouched.csv", _
        Origin:=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=True, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1)), TrailingMinusNumbers:=True
    ChDir "C:\Users\FR\Downloads"
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\FR\Downloads\Attempt with macro.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
    ActiveWindow.Close
End Sub

 

[Razzy]

    Workbooks.Open Filename:="C:\Users\FR\Downloads\Untouched.csv"
    ActiveWorkbook.SaveAs Filename:="C:\Users\FR\Downloads\Untouchedv2.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWindow.Close

Result - Untouchedv2.csv: ANSI, but adds double commas (compare with wished goal above):

Sideetikett;Art. nr.;Type;Lengde (m) - Input 2000;Høyde sprang (m);Bredde sprang (m);Tykkelse utstøping (m);Utstøping (m2);Betong (m3),,,,
BA-E01;5031;Utstøping B30;23,12;0;0;0,25;32,49;8,12
3;5601;sprang m.bæring;12,34;0,4;0,3;0,25;;
BA-E01;5034;Utstøping PSB;11,2;0;0;0,25;5,96;1,49
BA-E01;5031;Utstøping B30;7,51;0;0;0,25;2,24;0,56
BA-E01;5033;Utstøping B45;6,8;0;0;0,25;1,85;0,46
BA-E01;5031;Utstøping B30;6,54;0;0;0,27;1,41;0,38
3;5014;dekke opph. vegg;6,47;0;0;0,25;;,,
3;5031;Utstøping bad B30;4,59;0;0;0,19;0,89;0,17
3;5079;steng rundt utsp.;3,14;0;0;0;0,45;0,,
3;5604;Integrert bjelke i dekke;3,03;0;0;0,25;;,,
3;5086;dekkeforkant balkong;2,35;0;0;0,25;;,,
3;5086;dekkeforkant balkong;2,32;0;0;0,25;;,,
3;5086;dekkeforkant balkong;2,14;0;0;0,25;;,,
3;2082;kantsteng;1,8;0;0;0,25;;,,
3;5600;sprang;1,59;0,4;0,3;0,25;;
3;5084;kantsteng synlig dekkeforkant;1,59;0;0;0,25;;,,

This actually results in almost correct result. The only flaw now is that it adds double commas in some places. Not sure why.

Tried to record a macro to take a search and replace the comma with nothing, but it did not work.

Can you solve the last bit of code? How to replace double commas with nothing?

Download Untouchedv2.csv here

 

[ebea]

It actually also do (handle the conversion). But if you look in the file, these extra commas, comes only in those columns, where you have no data. And in you'r VBA you have defined all 9 columns to separate with a Comma! Is it Norwegian language in the file

 

[Razzy]

Sub Makro()

    Workbooks.Open Filename:="C:\Users\FR\Downloads\Untouchedv2.csv", Local:=True
    Range("A1:I17").Select
    Range("I12").Activate
    Selection.Replace What:=",,", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    ActiveWorkbook.Save
    ActiveWindow.Close
End Sub

Why is this simple macro change the decimal seperator and add commas?...

 

[Razzy]

It actually also do (handle the conversion). But if you look in the file, these extra commas, comes only in those columns, where you have no data. And in you'r VBA you have defined all 9 columns to separate with a Comma! Is it Norwegian language in the file

You have a keen eye for coding

Am I getting around to this?

As recently posted, I'm trying to open the CSV file and replace ",," with nothing.

Debugger shows that this works, but something goes wrong with the storage itself.

 

[ebea]

I do not know, from where the data to the CSV file comes, but if you in those columns where there are missing data (those who will be filled with dbl. Commas in the end), put in a 0, you will not have the problem!

 

[Razzy]

I do not know, from where the data to the CSV file comes, but if you in those columns where there are missing data (those who will be filled with dbl. Commas in the end), put in a 0, you will not have the problem!

Thx Ebea!

 

[ebea]

You'r welcome