too many rows in csv file

[dan]

I have a .csv file that consists of thousands of lines (6000 pages when
opened in MS Word).

How can I get that data into excel?

I have seen some knowledge bases do it with a macro but that macro seems to
work only with text files and ignores the fact that my data is a .csv so I
get loads of rows with all my column data all in column A as text with
commas!!!

Please help me - my brain is hurting!

 

[Kevin B]

Make a backup of your original source file and then use notepad to split the
file into 2 or more files, this will solve the to many rows issue.

Then open Excel and select FILE/OPEN. Change the FILES OF TYPE to text
files and then retrieve the of the text files. This will open the Text
Import Wizard. The first screen is where you tell Excel which row to start
the import with. Screen 2 is where you can confirm you delimiter, and screen
3 lets you indicate how each column of data should be translated.

Repeat the FILE/OPEN procedure for the remaining text files.

 

[dan]

Nice idea but I'd have about 700 text files!

I need an automated (macro) method

Thanks for trying though....

 

[Dave O]

If the issue is that the file contains more rows than Excel can
accommodate, 65,536, then run this macro to parse the file into blocks
of 50,000 rows each. The original file will not be affected in any
way: this program reads the large file and writes it line by line into
new files.

You may need to change the path reference and filename to match yours.

Sub CSV_Blocks()
Dim RowCounter As Long
Dim FileCounter As Byte
Dim Lyne As String

Open "c:\LargeCSVFileName.csv" For Input As #1
FileCounter = 1
Open "c:\CSV" & FileCounter & ".csv" For Output As #2

Do While Not EOF(1)
Line Input #1, Lyne
Print #2, Lyne
RowCounter = RowCounter + 1
If RowCounter = 50000 Then
RowCounter = 0
Close #2
FileCounter = FileCounter + 1
Open "c:\CSV" & FileCounter & ".csv" For Output As #2
End If
Loop
Close #1
Close #2
End Sub

 

[dan]

Ta very much - just the ticket

(please excuse the language -- I'm British)!

Cheers

 

[Dave O]

Dan-
Please let us know how that works for you, and what the rest of your
process looks like- we may be able to tweak that code to work better
for you.

Dave O