Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default How do I break a large .CSV file into several small Excel files?


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,123
Default How do I break a large .CSV file into several small Excel files?

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
...



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default How do I break a large .CSV file into several small Excel file

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
...




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default How do I break a large .CSV file into several small Excel file

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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default How do I break a large .CSV file into several small Excel file

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



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default How do I break a large .CSV file into several small Excel file

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

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default How do I break a large .CSV file into several small Excel file

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
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
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

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default How do I break a large .CSV file into several small Excel file

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.)



Grand Blanc Campbell wrote:

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


--

Dave Peterson
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default How do I break a large .CSV file into several small Excel file

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.

Grand Blanc Campbell wrote:

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


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
combining data from several excel files into one file bobman Excel Discussion (Misc queries) 3 March 12th 06 09:34 AM
How do I unlock FILE access? rcmodelr Excel Discussion (Misc queries) 7 November 12th 05 10:55 PM
CHK file in excel link files srinivasan Excel Worksheet Functions 1 November 7th 05 09:40 PM
VBA for Excel 2000 file is corrupt nkamp Excel Discussion (Misc queries) 0 May 26th 05 03:37 PM
Want Excel to break links between worksheets in same file eclectic_kx Excel Worksheet Functions 1 May 25th 05 06:38 PM


All times are GMT +1. The time now is 04:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"