import CSV with too many columns

[Robert H]

I have a CSV file with of less than 200 records with each record
exceeding the excel column count limit. I think I'm around three
hundred fields of data for each record.

When I import the CSV data part of my data is lost. The transpose
option is useless as far as I can tell because I can’t use it until
the data is imported and at that point it’s been truncated already.

Is there a way to transpose before or during the import using VBA if
so is there existing code available? I tried Chip Pearson’s
“Importing Big Files” code but I still lost columns as that is not
what its intended for.

 

[Shane Devenshire]

Hi,

One option is to use Excel 2007 which allows 16,374 columns.

A second might be to import the file and tell the Import wizard that the
data it fixed width and set a single break about half what.
Then when the data is in Excel run the Data, Text to Columns command twice,
once on each half, putting there outputs in different areas.

Take a look here:
http://www.vicon.com/support/solution_view.php?id=1099

If these help, please click the Yes button,

Cheers,
Shane Devenshire

 

[Dave Peterson]

This may get you started...

Option Explicit
Sub testme01()

Dim TextLine As String
Dim mySplit As Variant

Dim fCtr As Long
Dim oCol As Long
Dim oRow As Long
Dim wks As Worksheet

Close #1
Open "c:\my documents\excel\book1.csv" For Input As #1

Set wks = Workbooks.Add(1).Worksheets(1)

oCol = 0
Do While Not EOF(1)
Line Input #1, TextLine
mySplit = Split(TextLine, ",")
oCol = oCol + 1
If oCol > wks.Columns.Count Then
MsgBox "out of columns!"
Exit Sub
End If
oRow = 0

For fCtr = LBound(mySplit) To UBound(mySplit)
oRow = oRow + 1
If oRow > wks.Rows.Count Then
MsgBox "out of rows"
Exit Sub
End If
wks.Cells(oRow, oCol).Value = mySplit(fCtr)
Next fCtr

Loop

Close #1

End Sub


You'll notice that it splits using the comma as a delimiter. But it doesn't do
any real parsing. If one of your fields contains a comma, then it'll be split
into multiple cells.

If you have double quotes surrounding a text field, then those double quotes
will appear in your data.

 

[Robert H]

Dave that worked great except for one thing. I was mistaken I had
around a thousand so my records ended up truncated... I the shord term
I spilt the records up in the three input files and it works great.
Ill need to mod the code to split excess records into separate
worksheets.

Thanks for the help, with it I was able got get back to the analysis I
needed to do and not trying to parse data...
Robert

 

[Dave Peterson]

Glad it worked ok for you.

But you must live a wonderful life -- no commas in the fields and no double
quotes messing up the text!

 

[Robert H]

there were some double quotes but i found and replaced them with
nothing before running your code recommendation

I guess its nice not to really need them