When would you ever need to convert a single row of text to several columns of that same text? It’s like turning the text sideways. Suppose you had a list of people down a single column. Suppose the list was like this: First Name, Last Name, Phone Number, Email Address, and so on. What you really want is to have each person moved sideways so that there were a few columns. The good news is that you can automate this instead of copying and pasting over and over again.

I have another use-case for you. If you have a lot of text that you need to insert into a database for a project, and you don’t want to take the time to copy and paste to do it manually, then this post is for you. Suppose you have a text or RTF file that lists a bunch of famous quotes that you want to insert into a database. The format of the text file is the date, the quote, and then the author and some comments. Each famous quote has three parts. It repeats down the text file. What are the steps to getting this into the database?

First, get that RTF file into a wordprocessing file such as MS Word. If it’s just plain text in a .txt file you don’t need to put it inti Word. Next, try copying and pasting the text from MS Word into MS Excel so that it comes in “clean”. We want no extra rows and everything in one column. Check it and adjust as needed or try again. Here is what my Excel file looks like.

Here are some comments about the Excel file.

• This illustrates how to convert one row to multiple columns
• In this case, each “entry” or “quote” is on 3 rows in column A.
• The formula in Columns B, C, and D therefor have a “3” in them
• Change the 3 to the number that suits your data.
• The E column converts the dates to a REAL number that SQLite uses to store dates
• Column F builds the INSERT INTO SQL statements that we can run in DB Browser for SQLite
• CHAR(34) is a double quote
• Select the cells under the F column and copy into your RDMS to run the SQL statements

You raw quote data is in column A. Raw data is also in column E. Columns B, C, D and F are all formulas. What are the formulas? The formula in cell B1 is below.

=INDEX($A:$A,ROW(A1)*3-3+COLUMN(A1))

C1 is shown below.

=INDEX($A:$A,ROW(B1)*3-3+COLUMN(B1))

D1 is shown below.

=INDEX($A:$A,ROW(C1)*3-3+COLUMN(C1))

B2 is below. The pattern repeats. In fact, all you need to do is enter the formula in cell B2 and copy to the right and down.

=INDEX($A:$A,ROW(A2)*3-3+COLUMN(A2))

Understanding the INDEX Formula

How does the formula above work? First, we’ll look at the ROW and COLUMN functions. In the first formula above, we are in the B1 cell. We’ve got ROW(A1) which returns the row number of the cell A1, which is simply 1. COLUMN(A1) return the column number of A1, which is 1. When we plug those into the formula we get 1. Now our INDEX formula looks like this: INDEX($A:$A,1). What does that mean? How does INDEX() work? The first part of the function is the entire A column as shown by $A:$A. This is where the “answer” is. This is the range of cells that contain the result I want to see in the cell after evaluation. The second part of the INDEX formula is the position of the row, or better said, the row number. So, if the formula was =INDEX(A:A,1) the result would be whatever was in the cell A1. If we have =INDEX(A:A, 2) we would get whatever was in A2.

The SQL String Formula

This formula shown is in the F1 cell. The database table is called ThoughtsTable and it has at least 3 columns with the following names: date, Thought and Explanation. The date comes from cell E1, which is just a number types right into th cell. CHAR(34) is a double quote.

="INSERT INTO ThoughtsTable (date, Thought, Explanation) VALUES(" & E1 & "," & CHAR(34) &  C1 & CHAR(34) &  "," & CHAR(34) & D1 & CHAR(34) & ");"

The result of the above formula is:

INSERT INTO ThoughtsTable (date, Thought, Explanation) VALUES(2459216,"Change your thoughts and you change your world.","Norman Vincent Peale (May 31, 1898 – December 24, 1993) was an American minister and author.");