excel table to text flat

5 pts.
Microsoft Excel
Microsoft Excel macros
Microsoft Excel table
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. Many thanks! Sub createtextfile() 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 Sheets("Data").Select Range("A2").Select Range(Selection, Selection.End(xlDown)).Select 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) Else val = arrval(0) cc = "" End If Print #1, dim1 & sep & dim2 & sep & yea & sep & val & sep & cc Next Next Close #1 Sheets("Control").Select Application.ScreenUpdating = True End Sub

Answer Wiki

Thanks. We'll let you know when a new response is added.

Can you create a table in Excel using a pivot table function? It is very powerful method of filtering and combining information without having to resort to macro programming.

Discuss This Question:  

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: