Convert Excel Rows Into SQL Columns

Question

I have a client that inherited an Excel spreadsheet full of contact information for a mailing list. Apparently, his predecessor attempted to format the spreadsheet for printing pin-feed mailing labels. It uses one column, and has the address info in separate rows:

Name
Street Address
City, ST ZIP
Name
Street Address
City, ST ZIP
Name
Street Address
City, ST ZIP

What I need to do is transpose the data into columns, with separate columns for City, ST, and ZIP (for carrier route sorting). My initial thought was to use Pivot, but I couldn't seem to get that to work.

There are 1,800+ names in this mailing list, so manually fixing it is not an option. I also had an idea to export them as a CSV, and then use a series of find and replace operations to replace line breaks with commas, but that seems bush league. There has to be a proper data solution.

Ideas?

Thanks,

ty

Maybe Excel vba could help: 1st column = name; 2nd addressline1; 3rd column string operations and move to next name column. — Krish
Is the format you show here always the case? If it is you could run a script of some sort to split every 3 line breaks into a record and then within that split the city, st, zip line based on the comma position. — Chizzle
As krish said I would use vba for this. You could simply use it to generate a sql insert script with relative ease. You would split the values by whatever they are separated with (most likely a comma). — Dane

Ido Gal Ido Gal · Accepted Answer · 2015-10-01T16:27:23

This is my solution. It is based on the assumption that the structure that you've described is fixed and consistent.

It is in written T-SQL (SQL Server). You shouldn't have any problem converting it to my-sql.

I am against the vba solution. SQL is powerful enough.

If you have hard time implementing this, I have a few more ideas. (I'm just not sure what's available in my-sql and have to check it out). Any way - It should work.

--DROP TABLE Example_Table;

CREATE TABLE Example_Table 
   (
   Val     VARCHAR(1000),
   Row_Num INT IDENTITY(1,1)
   );

INSERT INTO Example_Table
    SELECT 'John' AS Val UNION ALL
    SELECT 'Elm St.' AS Val UNION ALL
    SELECT 'Tel Aviv, 151515' AS Val UNION ALL
    SELECT 'Doe' AS Val UNION ALL
    SELECT 'Manhatten St.' AS Val UNION ALL
    SELECT 'Jerusalem, 344343' AS Val UNION ALL
    SELECT 'Fox' AS Val UNION ALL
    SELECT 'Mulder St.' AS Val UNION ALL
    SELECT 'San Francisco, 3243424' AS Val UNION ALL
    SELECT 'Jean Lic' AS Val UNION ALL
    SELECT 'Picard St.' AS Val UNION ALL
    SELECT 'Enterprise City, 3904734' AS Val;

SELECT Name_Details.Val AS Name
     , Street_Details.Val AS Street
     , SUBSTRING(City_Details.Val, 1, CHARINDEX(',', City_Details.Val, 0) - 1) AS City
     , SUBSTRING(City_Details.Val, CHARINDEX(',', City_Details.Val, 0) + 2, LEN(City_Details.Val) - CHARINDEX(',', City_Details.Val, 0)) AS Zip
     , City_Details.Val AS City_And_Zip
FROM   Example_Table AS Name_Details
       INNER JOIN 
       (SELECT VAL, Row_Num FROM Example_Table AS E WHERE  Row_Num % 3 = 2) AS Street_Details
         ON (Street_Details.Row_Num = Name_Details.Row_Num + 1)
       INNER JOIN 
       (SELECT VAL, Row_Num FROM Example_Table AS E WHERE  Row_Num % 3 = 0) AS City_Details
         ON (City_Details.Row_Num = Name_Details.Row_Num + 2)
WHERE  Name_Details.Row_Num % 3 = 1 ;

DROP TABLE Example_Table;

Convert Excel Rows Into SQL Columns

1 Answers