Can you post an example data set in a packaged workbook (.TWBX) file?  That will help us help you.

What database are you using? You're probably better off doing this with custom SQL, since you need to both split the rows and unpivot them. I.e., you want the final data to look something like:

I'm sure it's possible to do this in Microsoft Jet (the database connection used for text, Excel and Access data sources), but it might be ugly and slow with a bunch of string manipulation. On the other hand it's a straight forward Python script (or any other script tool, including VBA (?)). And if you're using a proper database, you might have a more efficient split function.

Jim

Another quick thought. If your your example data above is your data file, then you could add a header row, connect as text and use a simple custom SQL connection to reshape the data.

Data Source Text File:

Albert, Books, 10, PC, 8, Home, 7
Bob, PC, 20, Electronics, 8, Books, 6

Add Header Row:

Customer, Cat1, Amt1, Cat2, Amt2, Cat3, Amt3
Albert, Books, 10, PC, 8, Home, 7
Bob, PC, 20, Electronics, 8, Books, 6

You can append a header row in Windows with:

echo Customer, Cat1, Amt1, Cat2, Amt2, Cat3, Amt3 > data.txt
type OriginalDataFile.txt >> data.txt

If you're using linux, just replace type with cat.

Connect to text data source in Tableau and select Custom SQL: 

SELECT 
  [data#txt].[Customer] AS [Customer],
  "1" as [Rank],
  [data#txt].[Cat1] AS [Category],
  [data#txt].[Amt1] AS [Amount]
FROM [data#txt]
UNION ALL
SELECT 
  [data#txt].[Customer] AS [Customer],
  "2" as [Rank],
  [data#txt].[Cat2] AS [Category],
  [data#txt].[Amt2] AS [Amount]
FROM [data#txt]
UNION ALL
SELECT 
  [data#txt].[Customer] AS [Customer],
  "3" as [Rank],
  [data#txt].[Cat3] AS [Category],
  [data#txt].[Amt3] AS [Amount]
FROM [data#txt]

The data now looks like

And you can get your result easily in Tableau:

Jim