Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
too many rows in csv file
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
too many rows in csv file
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. -- Kevin Backmann "dan" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
too many rows in csv file
Nice idea but I'd have about 700 text files!
I need an automated (macro) method Thanks for trying though.... "Kevin B" wrote: 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. -- Kevin Backmann "dan" wrote: 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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
too many rows in csv file
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
too many rows in csv file
Ta very much - just the ticket
(please excuse the language -- I'm British)! Cheers "Dave O" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
too many rows in csv file
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cannot open excel file, please help!!! | Excel Discussion (Misc queries) | |||
copy excell file to a CSV file and than to Notepad | Excel Discussion (Misc queries) | |||
Importing a text file into Excel with too many rows? | Excel Discussion (Misc queries) | |||
Insert rows | Excel Worksheet Functions | |||
Adding Rows to Master Sheet | New Users to Excel |