How do I break a large .CSV file into several small Excel files?

[Guest]

 

[Ron de Bruin]

You can use this example from Dave Peterson that create a csv file for every 25 rows.
It save the files in your temp folder (Start>Run Enter %temp% ) as csv files (you can change that to xls)

Sub testme()

Dim wks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim myStep As Long
Dim iCtr As Long

myStep = 25

Set wks = Worksheets("sheet1")
Set newWks = Workbooks.Add(1).Worksheets(1)
iCtr = 0
With wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow Step myStep
newWks.Cells.Clear
.Rows(iRow).Resize(myStep).Copy _
Destination:=newWks.Range("a1")
With newWks
Application.DisplayAlerts = False
iCtr = iCtr + 1
.Parent.SaveAs Filename:=Environ("temp") & "\Extracted_" _
& Format(iCtr, "000"), _
FileFormat:=xlCSV
Application.DisplayAlerts = True
End With
Next iRow
End With

newWks.Parent.Close savechanges:=False
End Sub

 

[Guest]

I'm a non technical person. Where do I type in these instructions? Also, my
..csv file has 170,000 records in it. I want to create 4 Excel files from it.
How can I do that? That would be about 50,000 records per file.

 

[Dave Peterson]

Ron's suggestion assumes that you can open the CSV file in excel and then split
it into smaller pieces.

With 170,000 lines, that won't work.

Since you're only splitting the file into 4 parts, why not just open the CSV
file in NotePad (or your favorite text editor) and split the file manually--copy
and paste 50k lines at a time to a new file and save that.

 

[Guest]

Dave,

I was able to do this and get these pieces into Excel, but all of the data
is in one column. How do I convert it to regular Excel format so that each
field of data is in one column?

 

[Guest]

Dave,

I think I've got it figured out.

Thanks for the help.

Regards,

Grand Blanc Campbell

 

[Dave Peterson]

I bet you selected the column and did data|text to columns.

If you have to do this lots of times, you may want to record a macro that does
that data|text to columns. Then you don't have to do it manually each time.
Just load up the data and re-run that recorded macro.

 

[Guest]

That's exactly what I did. However, I found that after I created one
spreadsheet in this fashion, then all I had to do after I saved the data, was
delete everything by the column headings and when I did a cut an paste from
notepad to excel, it put everything in the correct columns. Then I saved
under a new file name.

But I appreciate the help. I never used notepad before except for viewing
files which were much much smaller than what I had to deal with here.

Thanks again.

 

[Dave Peterson]

Excel likes to help by remember the settings you used in your previous Data|Text
to columns.

If you would have closed excel, you'd have to do the data|text to columns
another time.

(Just something to be watchful for.)

That's exactly what I did. However, I found that after I created one
spreadsheet in this fashion, then all I had to do after I saved the data, was
delete everything by the column headings and when I did a cut an paste from
notepad to excel, it put everything in the correct columns. Then I saved
under a new file name.

But I appreciate the help. I never used notepad before except for viewing
files which were much much smaller than what I had to deal with here.

Thanks again.

 

[Dave Peterson]

ps. I actually meant that if you had to do this same kind of thing once a day,
once a week, ... kind of thing.

The macro could prove useful.

That's exactly what I did. However, I found that after I created one
spreadsheet in this fashion, then all I had to do after I saved the data, was
delete everything by the column headings and when I did a cut an paste from
notepad to excel, it put everything in the correct columns. Then I saved
under a new file name.

But I appreciate the help. I never used notepad before except for viewing
files which were much much smaller than what I had to deal with here.

Thanks again.