importing cvs txt into column vs. row

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig J - 13 Apr 2004 14:43 GMT

I am trying to import a CSV text file into excel in a column format vic
row due to the fact that I have 1024 cells of data and in the ro
format it gets cut off due to length limitations. Anyone have an
ideas? Thank yo

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Bernie Deitrick - 13 Apr 2004 16:09 GMT

Craig,

Try something like the code below.

HTH,
Bernie
MS Excel MVP

Public Sub TransposeImportTextFile(FName As String, Sep As String)
'Based on Chip Pearson's Code
'Modified by Bernie Deitrick June 10, 2003
Dim RowNdx As Integer
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveRowNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveRowNdx = ActiveCell.Row
ColNdx = ActiveCell.Column

Open FName For Input Access Read As #1

While Not EOF(1)
Line Input #1, WholeLine
If Right(WholeLine, 1) <> Sep Then
WholeLine = WholeLine & Sep
End If
RowNdx = SaveRowNdx
Pos = 1
NextPos = InStr(Pos, WholeLine, Sep)
While NextPos >= 1
TempVal = Mid(WholeLine, Pos, NextPos - Pos)
Cells(RowNdx, ColNdx).Value = TempVal
Pos = NextPos + 1
RowNdx = RowNdx + 1
NextPos = InStr(Pos, WholeLine, Sep)
Wend
ColNdx = ColNdx + 1
Wend

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #1

End Sub

> I am trying to import a CSV text file into excel in a column format vice
> row due to the fact that I have 1024 cells of data and in the row
[quoted text clipped - 3 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Hammer_757 - 13 Apr 2004 16:47 GMT

Bernie, I work with craig. thatnks for the help, but we are having
prooblem with the code and are too ignorant to sort it out.

We pasted that code into a module and saved it but the Macro does no
show up in the Excel macro menu. When we try to run it from the VB
editor, it pops up the empty macro menu.

we are lost

Thanks again for responding in the first plac

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Bernie Deitrick - 13 Apr 2004 17:08 GMT

Hammer,

Sorry. I should have been more explicit with my instructions.

The macro needs to be called from another macro, since it must be passed
parameters.

Since it is declared like this:

Public Sub TransposeImportTextFile(FName As String, Sep As String)

it muct be used like this

Sub Test()
TransposeImportTextFile "C:\Excel\CSVfile.txt", ","
End Sub

You could also rewrite the macro to not take parameters, but prompt for the
filename:
Change

Public Sub TransposeImportTextFile(FName As String, Sep As String)

to

Public Sub TransposeImportTextFile()
Dim FName As String
Dim Sep As String
FName = Application.GetOpenFilename
Sep = ","

Sorry for the confusion.

HTH,
Bernie
MS Excel MVP

> Bernie, I work with craig. thatnks for the help, but we are having a
> prooblem with the code and are too ignorant to sort it out.
[quoted text clipped - 9 lines]
> ---
> Message posted from http://www.ExcelForum.com/

Reply to this Message

Hammer_757 - 13 Apr 2004 17:31 GMT

Thanks Bernie, that works like a champ

--
Message posted from http://www.ExcelForum.com

Reply to this Message

Bernie Deitrick - 13 Apr 2004 17:45 GMT

You're welcome, and thanks for the feedback.

Bernie
MS Excel MVP

> Thanks Bernie, that works like a champ!

Reply to this Message