Convert dumb customer records to csv for database import

[y2gabs]

Hey guys,

I know this question is likely answered somewhere here, or at least addressed... I've tried searching for any words I can think of that might relate to the question but no luck. Regardless, please see below:

Column1 Column2
Business Name Company X
Address Address XXX; city; province
Phone 1 XXX-XXX-XXXX
Fax XXX-XXX-XXXX
Email email@company.com
Website www.website.com

Business Name Company Y
Address Address XXX; city; province
Phone 1 XXX-XXX-XXXX
Fax XXX-XXX-XXXX
Email email@company.com
Website www.website.com

See file here:
http://www.rawkmedia.com/Customer-Sample.xls

My question is, is there an easy way or macro out there to convert the above records (1000's), that were kept in such a stupid format for years, into the proper format (each column representing Business Name, Address, Phone, etc) for importing into a database?

Does my question make sense?

Anyway, any help would be HUGE!

Thanks!!

J.

 

[djreiswig]

Your title was a little confusing with CSV. I'm assuming you want the data organized into columns in an Excel sheet. If you truly want a Comma Separated Values file, then this won't do that.

If you want it organized by columns then try this:

Add a new sheet and rename it Customer-New

Make sure the data you have looks exactly like the sample you provided.
With the customer data in column C starting at row 2 and a blank line between every customer's data

Paste this into a module and run it.

I'm sure this isn't the most elegant solution, but I think it should do what you want.

Sub OriganizeCustomers()

'make headers
For h = 1 To 6
    Sheets("Customer-New").Range(Choose(h, "A", "B", "C", "D", "E", "F") & "1").Value = _
    Sheets("Customer-Sample").Range("B" & h + 1).Value
Next h

'next row in new sheet
'first row is headers
newrow = 2
'next coumn in new sheet
newcolumn = 1

'loop through all data in column "C" in old sheet
For i = 2 To Sheets("Customer-Sample").Range("C" & Rows.Count).End(xlUp).Row
    
    'transfer data
    Sheets("Customer-New").Range(Choose(newcolumn, "A", "B", "C", "D", "E", "F") & newrow).Value = _
    Sheets("Customer-Sample").Range("C" & i).Value
    
    'go to next column in new sheet
    newcolumn = newcolumn + 1
    
    'each customer has 6 data items so
    'skip 1 row in old sheet if next row is multiple of 7
    If i Mod 7 = 0 Then
        i = i + 1
        'next customer so go to next row and back to first column in new sheet
        newrow = newrow + 1
        newcolumn = 1
    End If
Next i
    
End Sub

 

[djreiswig]

Hmmm. I wonder if that worked.