Hello, I am not an IT expert to start with!
I have a series of tables in excel made of 3 dimensions. Two dimensions are vertical (indicator and country), one is horizontal (years). The different tables contain data for a variable number of years and for a variable number of countries and indicators, therefore each table has a different number of columns and rows.
I need to import all these data in a SQL server and I need to find a way to transform my tables in flat text files (made of 4 columns: indicator, country, years and value).
A colleague created a VBA excel macro. It works but the output it is not exactly what I would like (see macro below).
I would like to modify 3 things but I am not able since I don't understand how it works.
1) The range “years” should not be a pre-defined range (in other tables I might have data starting 1998 going to 2007 only, or I might have more years (1985-2008 for instance).
2) The excel table that I gave to my colleague (who is now gone) had a period in cells where the value was missing. To import data in SQL empty values should be blank, but if I get rid of the points in the excel sheet the macro doesn’t work any more…
3) In the output text file that I obtain there is a comma also after the fourth column (the column containing the values). I would like the comma to separate the columns but there should be nothing after the value.
The question for you is:
- could you explain in words what the macro does?
- would you be able to modify it?
- do you know who can create this macro for me? I am willing to pay, but I don't know who sells such services.
Application.ScreenUpdating = False
Dim dim1 As String
Dim dim2 As String
Dim yea As String
Dim valcc As String
Dim val As String
Dim cc As String
Dim arrval As Variant
Dim sep As String
Dim outfile As Variant
Dim processline As Variant
Dim processyear As Variant
Dim stroutput As String
outfile = Trim(Range("destination").Text)
sep = Trim(Range("separator").Text)
Open outfile For Output As #1
For Each processline In Selection
dim1 = Trim(processline.Text)
dim2 = Trim(processline.Offset(0, 1).Text)
For Each processyear In Range("years")
yea = Trim(processyear.Text)
valcc = UCase(Trim(Cells(processline.Row, processyear.Column).Text))
arrval = Split(valcc, " ")
If UBound(arrval) = 1 Then
val = arrval(0)
cc = arrval(1)
val = arrval(0)
cc = ""
Print #1, dim1 & sep & dim2 & sep & yea & sep & val & sep & cc
Application.ScreenUpdating = True