Using VBA to clean up imported data

[capefear]

I receive data from an old system that does not format the data in a table format. It takes hours for me to clean up the data so I can use pivot tables, etc. to summarize the data. Does anyone know how to write VBA to do this automatically? I am new to VBA. Doing this manually takes hours. I would really appreciate some help. The data received looks like this:

SSDB example r1.xlsx
	A	B	C	D
1		Query Definition
2
3	Recipient:	My name
4	Query:	salestest (302306-1)
5	Period:	03/08-02/09
6	Results:	Volume[Kg],Sales[USD]
7	Break 1:	By Month
8	Break 2:	By Product
9	Break 3:	By Holding
10
11	Selections:
12	EG only
13
14	Product list
15	956910	ProductA
16	987322	ProductB
17
18	Month
19	ProdID	Product	Volume[Kg]	Sales[USD]
20	HdgID	Holding	12Mth-02/09	12Mth-02/09
21	----------------	--------------------------------	-------------	---------------
22	2008/03
23	----------------	--------------------------------
24	956910	ProductA
25	----------------	--------------------------------
26	106325	Cust A	10	100
27	109102	Cust B	50	500
28	111424	Cust C	12	150
29	119200	Cust D	5	180
30	================	================================	=============	===============
31	2008/03		77	930
32
33
34	2008/04
35	----------------	--------------------------------
36	956910	ProductA
37	----------------	--------------------------------
38	106325	Cust A	50	200
39	109102	Cust B	10	180
40	111424	Cust C	25	300
41	================	================================	=============	===============
42	956910	ProductA	85	680
43
44	987322	ProductB
45	----------------	--------------------------------
46	134000	Cust E	110	300
47	707500	Cust F	1975	2000
48	================	================================	=============	===============
49	987322	ProductB	2085	2300
50
51
52	================	================================	=============	===============
53	2008/04		2170	2980
54
55
56	2008/05
57	----------------	--------------------------------
58	956910	ProductA
59	----------------	--------------------------------
60	106325	Cust A	10	300
61	109102	Cust B	16	150
62	111424	Cust C	5	50
63	119200	Cust D	150	560
64	================	================================	=============	===============
65	956910	ProductA	181	1060
66
67	987322	ProductB
68	----------------	--------------------------------
69	109102	Cust B	130	250
70	111424	Cust C	5	20
71	119200	Cust D	570	1500
72	================	================================	=============	===============
73	987322	ProductB	705	1770
74
75
76	================	================================	=============	===============
77	2008/05		866	2830
78
79	================	================================	=============	===============
80	TOTAL		3133	6740
RAW DATA

I would like the data to look this:

SSDB example r1.xlsx
	A	B	C	D	E	F
1	Date	ProdID	Customer ID	Customer	Volume[Kg]	Sales[USD]
2	Feb-08	956910	106325	Cust A	10	100
3	Feb-08	956910	109102	Cust B	50	500
4	Feb-08	956910	111424	Cust C	12	150
5	Feb-08	956910	119200	Cust D	5	180
6	Apr-08	956910	106325	Cust A	50	200
7	Apr-08	956910	109102	Cust B	10	180
8	Apr-08	956910	111424	Cust C	25	300
9	Apr-08	987322	134000	Cust E	110	300
10	Apr-08	987322	707500	Cust F	1975	2000
11	May-08	956910	106325	Cust A	10	300
12	May-08	956910	109102	Cust B	16	150
13	May-08	956910	111424	Cust C	5	50
14	May-08	956910	119200	Cust D	150	560
15	May-08	987322	109102	Cust B	130	250
16	May-08	987322	111424	Cust C	5	20
17	May-08	987322	119200	Cust D	570	1500
Table

 

[ShaunD30]

Firs thing I would do is just loop through the first column and delete the ----------

I am not pro but you would want something like

Sub CleanUp()
 
Range("A1").Select

    Do Until ActiveCell.Value = "TOTAL"
        If ActiveCell.Value = "----------------" Then
        ActiveCell.EntireRow.Delete
Else
        If ActiveCell.Value = "=================" Then
        ActiveCell.EntireRow.Delete
    End If
    End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

That should atleast get you started

Shaun

 

[ShaunD30]

BTW, you want to right click on the sheet tab of your workbook and click on "View Code".
Then you will want to look on the left hand side and find the filename you are working on..Right click on that and insert "Module", and paste that code into the module.

Then go back to your workbook and you will find "cleanup" in your macros

Shaun

 

[capefear]

Thanks Shaun for the start. It works great except for one little thing. The macro does not stop. I think it is because of the "========" right before the "Total". It deletes the line which places the cursor on the cell with Total, but then the offset command moves the cursor to the next cell below it so the macro continues to run until I ctrl break.
Rick

 

[ShaunD30]

Sorry...Try this instead (I do that to myself all the time)

Sub CleanUp()
 
Range("A1").Select
    Do Until ActiveCell.Value = "TOTAL"
        If ActiveCell.Value = "----------------" Then
        ActiveCell.EntireRow.Delete
        Else
        If ActiveCell.Value = "=================" Then
        ActiveCell.EntireRow.Delete
        Else
        ActiveCell.Offset(1, 0).Select
        
    End If
    End If
Loop
End Sub

 

[capefear]

Thanks, that works. Now I just need to figure the rest out. Any more suggestions would be appreciated.

 

[Legacy 68668]

What the delimiter of the originla text file ?

 

[capefear]

Sorry, I am not sure I know what you mean. I copy and paste the data from the text file directly into excel. The text file looks like the excel file below, but excel divides the data into columns as shown below automatically when I paste it into excel.

 

[Legacy 68668]

Or can you show me how the original txt file look like ?

 

[capefear]

Here is part of it. Is this enough?

"Query Definition"

"Recipient:" "My Name"
"Query:" "salestest (302306-1)"
"Period:" "03/08-02/09"
"Results:" "Volume[Kg],Sales[USD]"
"Break 1:" "By Month"
"Break 2:" "By Product"
"Break 3:" "By Holding"

"Selections:"
"EG only"

"Product list"
"915165" "product a"
"913166" "product b"

"Month" " "
"ProdID" "Product" "Volume[Kg]" "Sales[USD]"
"HdgID" "Holding" "12Mth-02/09" "12Mth-02/09"
"----------------" "--------------------------------" "-------------" "---------------"
"2008/03"
"----------------" "--------------------------------"
"915165" "product a"
"----------------" "--------------------------------"
"106325" "International Essence SA De Cv" 2 1369.04
"109102" "S utive Ltd" 50 27692.95
"111424" "Wes Soya" .5 367.03
"119200" "International Development Limited" 5 2717.2
"123438" "Pizzza Spa" 10 5885.