So, I get an SAP output from a SPOOL. It comes out as a terrible "|" delimitated .CSV file in multiple "pages" which are actually blank rows with "---------" in between and some other text. I have to clean it before importing it in to Access which I have a VBA script for.
I have this code to do text-to-columns and it works great!
These are the steps I take to clean from the raw output from SAP:
1) Concatenate all cells in the 1st row like "=A1&B1&C1.....&Z1" ..... because SAP will continue delimitation overflow in to column B,C and some others... its pretty variable depending on the amount of columns I'm exporting from excel.
I do that in column AA then fill it down till there are no more rows. Sometimes is 3,000 rows and sometimes its 500,000 rows.
2) Once that is all in AA, I copy it to a new sheet in A1
3) Text-to-columns (now I can use that VBA code)
4) TRIM all column headings (I usually copy paste in a new sheet, transpose, trim, copy/paste values, transpose back)
5) Filter and delete all things in column "B" that either BLANK or begin with "----"
If someone could help me, I'd freak out. Kind of looking like this at first then
I have this code to do text-to-columns and it works great!
Code:
[FONT=arial]Sub TextToColumns()[/FONT]
[FONT=arial] Dim ws As Worksheet[/FONT]
[FONT=arial] For Each ws In Worksheets[/FONT]
[FONT=arial] ws.Columns("A:A").<wbr>TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _[/FONT]
[FONT=arial] TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _[/FONT]
[FONT=arial] Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|"[/FONT]
[FONT=arial] Next ws[/FONT]
[FONT=arial]End Sub[/FONT]
These are the steps I take to clean from the raw output from SAP:
1) Concatenate all cells in the 1st row like "=A1&B1&C1.....&Z1" ..... because SAP will continue delimitation overflow in to column B,C and some others... its pretty variable depending on the amount of columns I'm exporting from excel.
I do that in column AA then fill it down till there are no more rows. Sometimes is 3,000 rows and sometimes its 500,000 rows.
2) Once that is all in AA, I copy it to a new sheet in A1
3) Text-to-columns (now I can use that VBA code)
4) TRIM all column headings (I usually copy paste in a new sheet, transpose, trim, copy/paste values, transpose back)
5) Filter and delete all things in column "B" that either BLANK or begin with "----"
If someone could help me, I'd freak out. Kind of looking like this at first then