View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Grand Blanc Campbell Grand Blanc Campbell is offline
external usenet poster
 
Posts: 4
Default How do I break a large .CSV file into several small Excel file

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" wrote:

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.

Grand Blanc Campbell wrote:

Dave,

I think I've got it figured out.

Thanks for the help.

Regards,

Grand Blanc Campbell

"Grand Blanc Campbell" wrote:

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?

"Dave Peterson" wrote:

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.

Grand Blanc Campbell wrote:

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.

"Ron de Bruin" wrote:

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 (StartRun 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


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Grand Blanc Campbell" <Grand Blanc wrote in message
...





--

Dave Peterson


--

Dave Peterson