Import CSV to rows

[Andreas Knudsen]

Hello

I have a .txt file consisting of values separated by ", ", i.e. a comma
and a space. When trying to open this file, Excel 2004 starts the Text
Import Wizard, and manages to put the values in separate columns in one
row. However, I want these values in separate ROWS in one column. I know
that it is possible to convert a column-list to a row-list, but my
original file consists of more values than the maximum number of columns
allowed in a spreadsheet.

Is there really no way to import CSV into rows instead of columns?

 

[TR]

Here's what I would try-- not sure if it will do what you need.

I'd open the .txt file in Word (or your favorite text processor) and do
a global search and replace. Replace all instances of ", " with a hard
return ("^p" in Word). Save and import into Excel.

Just a thought.

 

[Andreas Knudsen]

Here's what I would try-- not sure if it will do what you need.

I'd open the .txt file in Word (or your favorite text processor) and do
a global search and replace. Replace all instances of ", " with a hard
return ("^p" in Word). Save and import into Excel.

Just a thought.

Thanks - I think that will do the trick. Still baffled by the lack of
this option in Excel, though.

 

[JE McGimpsey]

Hello

I have a .txt file consisting of values separated by ", ", i.e. a comma
and a space. When trying to open this file, Excel 2004 starts the Text
Import Wizard, and manages to put the values in separate columns in one
row. However, I want these values in separate ROWS in one column. I know
that it is possible to convert a column-list to a row-list, but my
original file consists of more values than the maximum number of columns
allowed in a spreadsheet.

Is there really no way to import CSV into rows instead of columns?

One way, using a macro:

Public Sub ImportLongRowDelimitedFile()
Const sDELIM As String = ", "
Const nMAXLEN As Long = 32767
Dim sFileName As String
Dim sInput As String
Dim nFileNumber As Long
Dim nLen As Long
Dim nPos As Long
Dim nRow As Long
sFileName = CStr(Application.GetOpenFilename)
If sFileName <> "False" Then
nLen = Len(sDELIM)
nFileNumber = FreeFile
Open sFileName For Input Access Read _
Lock Read As #nFileNumber Len = nMAXLEN
Do While Not EOF(nFileNumber)
Line Input #nFileNumber, sInput
nRow = nRow + 1
nPos = InStr(1, sInput, sDELIM)
Do While nPos > 0
Cells(nRow, 1).Value = Trim(Left(sInput, nPos - 1))
sInput = Mid(sInput, nPos + nLen)
nRow = nRow + 1
nPos = InStr(1, sInput, sDELIM)
Loop
Cells(nRow, 1).Value = Trim(sInput)
Loop
Close #nFileNumber
End If
End Sub

Note that this will fail if you have more than 65536 values, or if one
"line" (series of comma-space delimited values) has more than 32767
characters - that's a limitation on string size in VBA. A different
method could be devised if needed, but more info on your data would be
needed to design an efficient algorithm.

If you're not familiar with macros, see

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[JE McGimpsey]

Thanks - I think that will do the trick. Still baffled by the lack of
this option in Excel, though.

It's largely due to convention - 99.9% of CSV files generated are of the
form

record1_field1,record1_field2,record1_field3<cr>
record2_field1,record2_field2,record2_field3<cr>
record3_field1,record3_field2,record3_field3<cr>


Where <cr> is a carriage return or linefeed character (or both).

Most useful databases (i.e., the primary source/destination of these
files) have a maximum of 256 fields per record, but may have tens of
thousands of records.

XL is designed to automatically interpret files of this form, which,
again, 99.9% of the time, is what the user wants.

So it shouldn't be too baffling that the design doesn't cater to the
<0.1%...

 

[Andreas Knudsen]

It's largely due to convention - 99.9% of CSV files generated are of the
form

record1_field1,record1_field2,record1_field3<cr>
record2_field1,record2_field2,record2_field3<cr>
record3_field1,record3_field2,record3_field3<cr>


Where <cr> is a carriage return or linefeed character (or both).

Most useful databases (i.e., the primary source/destination of these
files) have a maximum of 256 fields per record, but may have tens of
thousands of records.

XL is designed to automatically interpret files of this form, which,
again, 99.9% of the time, is what the user wants.

So it shouldn't be too baffling that the design doesn't cater to the
<0.1%...

Thank you for the explanation, I wasn't aware of this. When I've been
working with respondent lists, survey data etc., I've seldom come across
files with carriage returns, so I guess that made me biased. However, I
still feel Excel should offer the option to put fields in rows and
records in columns.

 

[Bob Greenblatt]

Andreas, I've been following this thread. Your suggestion about allowing
rows and columns to be switched on input is a good one. You ought to suggest
it. Use the Help-Send Feedback choice on the menu.

Now, If you have never heard of using carriage returns to separate records,
what does this in your file? How would Excel, (or J.E.'s macro) know when
the next record started? And, does your data set really have more than 256
fields per record, but less than 256 records? That seems strange indeed.