VBA: Save Excel-Table as CSV (semicolon delimited)

To save a MS-Excel-table as a plain ASCII CSV-File is easy when you do it manually. No matter where you are. You go via menu FILE>SAVE AS> and select CSV as filetype.

But when your local (european) system uses the comma (“,”) as the decimal separator (you need semicolon as field delimiter in CSV) and you record this steps as a VBA-macro and execute it, you’d be surprised of the output.
Your CSV-file is now _really_ a comma-separated-value-file – while having the semicolon as the CSV-delimiter when saving manually. And to make things worse even your “european -style” date-type-fields (dd.mm.yyyy) are stored in the american notation (mm/dd/yyyy):

10/11/2010,9:15,12:30,13:00,18:30,W
10/12/2010,9:00,18:00,,,W
10/13/2010,9:20,12:30,13:00,17:30,W
10/15/2010,8:45,12:30,13:00,18:00,W

Obviously your regional-settings are completely ignored. At MS you find a not really helpfull advice to store your data manually as the given behaviour is intended because it “ensures that the result of running macro code is consistent regardless of the computer’s regional settings.”. Not that satisfactorily, is it?!

At first I tried to use a procedure that merges the fields semicolon delimited. But that way I lost my time formats:

11.10.2010;0,385416666666667;0,520833333333333;0,541666666666667;0,770833333333333;W
12.10.2010;0,375;0,75;;;W
13.10.2010;0,388888888888889;0,520833333333333;0,541666666666667;0,729166666666667;W
15.10.2010;0,364583333333333;0,520833333333333;0,541666666666667;0,75;W

Ok, even when MS says that this CSV-problem applies to Excel 97, 2000 and 2002 I found a hint in my Excel 2002-Online-Help to use the parameter “Local:=true” in the save-statement:

ActiveWorkbook.SaveAs Filename:=fnameProject, FileFormat:=xlCSV,
CreateBackup:=False, Local:=True

This makes Excel to obey to your regional settings regarding decimal-separator and date-format.

11.10.2010;09:15;12:30;13:00;18:30;W
12.10.2010;09:00;18:00;;;W
13.10.2010;09:20;12:30;13:00;17:30;W
15.10.2010;08:45;12:30;13:00;18:00;W
Advertisements

Linux: get the length of a string

To get the length of a string on a Linux-Shell as number of characters you have various possibilities:

bash> VAR="linux-operating-system"

bash> expr length $VAR
22

bash> echo ${#VAR}
22

bash> echo $VAR | awk '{print length}'
22

bash>

To store the length in a variable you could do this:

bash> LEN=`expr length $VAR`

bash> echo $LEN
22
bash>

MySQL: concatenating strings from multiple rows in one table-field

If you have the need to concatenate multiple strings from different rows into one single field via a sql-query, MySQL gives you the useful GROUP_CONCAT()-function.

At first we create a table and insert some values with ID as our designated “group by”-column:

mysql> create table t1 (id int, txt varchar(3));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (1,'ABC'),(2,'def'),(1,'GHI'),(2,'jkl'),(1,'mno'),(2,'pqr');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+------+------+
| id   | txt  |
+------+------+
|    1 | ABC  |
|    2 | def  |
|    1 | GHI  |
|    2 | jkl  |
|    1 | mno  |
|    2 | pqr  |
+------+------+
6 rows in set (0.00 sec)

mysql>

Then we’ll do a SELECT on that table and concat the group-specific strings with GROUP_CONCAT():

mysql> select id, group_concat(txt) from t1 group by id;
+------+-------------------+
| id   | group_concat(txt) |
+------+-------------------+
|    1 | ABC,GHI,mno       |
|    2 | def,jkl,pqr       |
+------+-------------------+
2 rows in set (0.01 sec)

mysql>

We could also define the string-separator with the SEPARATOR-keyword:

mysql> select id, group_concat(txt SEPARATOR ':') from t1 group by id;
+------+---------------------------------+
| id   | group_concat(txt SEPARATOR ':') |
+------+---------------------------------+
|    1 | ABC:GHI:mno                     |
|    2 | def:jkl:pqr                     |
+------+---------------------------------+
2 rows in set (0.00 sec)

mysql>