Excel – Import data into excel that doesn’t use a line break as a row delimiter

importmicrosoft excelmicrosoft-excel-2007

Is there any simple method that doesn't resort to macros whereby a plain text file or string can be imported into excel without using a line break as a row delimiter?

Example

11,12,13;21,22,23;31,32,33

A string in this format, and with approximately one million rows and 20 columns is being returned by an application. I need the simplest way to load this into excel.

Solution 1: Import with ; as the delimiter, paste transpose the columns, and then use 'text to columns' feature to convert all the comma delimited cells in the one column into many columns. This doesn't work because excel only supports a little over 16,000 columns, and we have many more rows than that.

Solution 2: Load the string into an advanced text editor that supports replacing the ; with a line break. (Most simple text editors only support simple text replace, no line breaks.) I would have trouble instructing a business user how to do this, and am not aware of any such tools that they are likely to have on their windows machine.

Can anyone else propose a simple way to deal with this miserable data format? As far as I know, there's no way to specify a row delimiter other than line break when importing into excel.

Best Answer

Use Microsoft Word as per your solution 2, it's probably already in your business users PC. The newline is indicated as ^l. See also http://support.microsoft.com/kb/95474/en-us

Related Solutions

Excel – Creating multiple rows from one row of Excel Data

Here we go.

STEP 1:

Name ranges for convenience. PARAMETERS is the list of parameters from A2 on down; LOCATIONS is the list of locations, from B1 across; DATA is the large square from B2 to the end. See my example:

STEP 2:

In another sheet, set up your new table. First column prints out all the locations, and it lists each location as many times as there are parameters:

That formula:

=INDEX(LOCATIONS,ROUNDUP((ROW()-1)/COUNTA(PARAMETERS),0))

That formula copies down.

STEP 3:

Second column prints out all the parameters, and it lists each parameter once until there are no more to list (note that this count corresponds to the count of how many times to list each Location in Step 2). Now you've got your entire list of every location/parameter combination, once each:

That formula:

=INDEX(PARAMETERS,MOD(ROW()-2,COUNTA(PARAMETERS))+1)

That formula copies down.

STEP 4:

From here, the way forward should be clear - we're now working with a simple INDEX MATCH to find the data at the intersection of the given location and parameter.

That formula:

=INDEX(DATA,MATCH(B2,PARAMETERS,0),MATCH(A2,LOCATIONS,0))

That formula copies down.

CONCLUSION:

With three formulas you've created your join table. Please consider selecting this answer so this question can be removed from the unanswered queue.

NOTES:

This works dynamically no matter how many columns/rows you have in your data (as long as you adjust the named ranges as needed, if you add more than the 365*768 records in this question's spec).
It doesn't do anything special with missing or empty data, though; you could easily wrap the final INDEX MATCH in Step 4 with an IF(ISBLANK()) to return something more useful than '0'.
This is NOT designed to skip those records, which adds a layer of complexity that's outside the scope of this question.

Related Question