Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I break a large .CSV file into several small Excel files?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combining data from several excel files into one file | Excel Discussion (Misc queries) | |||
How do I unlock FILE access? | Excel Discussion (Misc queries) | |||
CHK file in excel link files | Excel Worksheet Functions | |||
VBA for Excel 2000 file is corrupt | Excel Discussion (Misc queries) | |||
Want Excel to break links between worksheets in same file | Excel Worksheet Functions |