+ Reply to Thread
Results 1 to 10 of 10

Too Many Rows With .csv file. Need to create macro to reduce row size or file size

  1. #1
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Hello,


    I have a folder: c:\data, that contains 800 seperate .csv files

    i.e.

    C:\ data\
    a.csv
    bc.csv
    cf.csv
    dg.csv
    ez.csv

    etc....

    The files are too large and I cannot open them correctly. I only need the most recent 50,000 rows. Is there a way to create a macro that can access the data from the folder and extract the data I need from each unique file name like that listed above ?

    I tried several macros but cannot get them to work correctly

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Will you be importing all 800 files? Its easy to reduce the amount of rows to import but could be a headache to list 100 or more files to import. I would set up a sheet an in Column A create a list of files.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Thanks Mike;

    So if I create a list of files I want to import from a folder, lets say 25 seperate csv files, how would I create a macro to extract those files and limit the output to 50,000 rows ?

  4. #4
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Is the 50,000 rows of each csv file the first or the last? Also 25 files @ 50,000 rows will not fit into one sheet.
    Last edited by mike7952; 11-24-2012 at 10:16 AM.

  5. #5
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Hello Mike;

    Presently it will only open the oldest data first but what I need is the newest data which is at the end of the file and that you cannot see when I try to import it The actual beginning date is January 1998 and ending date is October 2012

    So lets just say I need the last 500,000 rows instead of 50,000. I made a typo.

    I can make a table separating the names of each file no problem ; each file would get its own worksheet

    Thankyou

  6. #6
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Im not sure about adding all that data to one workbook. You might have the same problem with trying to open them. Might want to create different workbooks.
    Are all csv files formatted the same? How many fields? Are there hearders? If so whats the name of the data field header?

  7. #7
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Hello Mike;

    Sample data below; It is a stock; I am going to make a worksheet for each individual stock in a workbook.

    Date,Time,Open,High,Low,Close,Volume
    04/29/2011,09:31,46.67,46.67,46.67,46.67,576
    04/29/2011,10:28,46.3,46.38,46.3,46.38,500
    04/29/2011,10:32,46.41,46.41,46.41,46.41,100
    04/29/2011,10:40,46.3,46.3,46.3,46.3,100
    04/29/2011,10:45,46.22,46.22,46.22,46.22,100
    04/29/2011,10:48,46.25,46.25,46.25,46.25,100
    04/29/2011,10:55,46.22,46.22,46.22,46.22,100
    04/29/2011,10:56,46.31,46.31,46.31,46.31,100
    04/29/2011,10:57,46.265,46.265,46.265,46.265,100
    04/29/2011,11:05,46.3,46.3,46.3,46.3,100
    04/29/2011,11:08,46.17,46.18,46.17,46.18,200
    04/29/2011,11:11,46.11,46.11,46.11,46.11,100
    04/29/2011,11:13,46.23,46.23,46.23,46.23,100
    04/29/2011,11:15,46.23,46.23,46.23,46.23,200
    04/29/2011,11:17,46.22,46.22,46.22,46.22,100
    04/29/2011,11:26,46.135,46.18,46.135,46.18,200
    04/29/2011,11:32,46.22,46.22,46.17,46.17,300
    04/29/2011,11:33,46.2,46.2,46.15,46.15,400
    04/29/2011,11:37,46.135,46.135,46.135,46.135,100
    04/29/2011,11:38,46.13,46.13,46.13,46.13,100
    04/29/2011,11:48,46.193,46.22,46.193,46.22,200
    04/29/2011,11:52,46.15,46.15,46.15,46.15,100
    04/29/2011,12:00,46.23,46.31,46.23,46.31,500
    04/29/2011,12:05,46.18,46.25,46.18,46.25,200
    04/29/2011,12:08,46.32,46.32,46.32,46.32,100
    04/29/2011,12:12,46.2,46.2,46.2,46.2,100
    04/29/2011,12:18,46.33,46.33,46.33,46.33,100
    04/29/2011,12:19,46.28,46.29,46.26,46.26,400
    04/29/2011,12:30,46.36,46.36,46.36,46.36,100
    04/29/2011,12:33,46.39,46.39,46.34,46.34,200
    04/29/2011,12:34,46.38,46.38,46.305,46.305,924
    04/29/2011,12:35,46.39,46.39,46.39,46.39,100
    04/29/2011,12:36,46.32,46.32,46.32,46.32,100
    04/29/2011,12:40,46.4,46.4,46.4,46.4,100
    04/29/2011,12:47,46.37,46.37,46.37,46.37,100
    04/29/2011,12:50,46.36,46.36,46.36,46.36,100
    04/29/2011,12:54,46.3,46.3,46.3,46.3,100
    04/29/2011,12:59,46.44,46.44,46.35,46.35,200
    04/29/2011,13:01,46.3,46.3,46.3,46.3,100
    04/29/2011,13:02,46.41,46.41,46.41,46.41,100
    04/29/2011,13:11,46.42,46.42,46.41,46.41,300
    04/29/2011,13:13,46.44,46.44,46.44,46.44,100
    04/29/2011,13:18,46.36,46.36,46.25,46.26,800
    04/29/2011,13:20,46.25,46.25,46.25,46.25,300
    04/29/2011,13:22,46.25,46.34,46.25,46.28,800
    04/29/2011,13:23,46.33,46.33,46.33,46.33,100
    04/29/2011,13:26,46.4,46.4,46.4,46.4,200
    04/29/2011,13:29,46.32,46.32,46.32,46.32,100
    04/29/2011,13:31,46.27,46.27,46.27,46.27,100
    04/29/2011,13:33,46.35,46.35,46.35,46.35,200
    04/29/2011,13:37,46.26,46.26,46.26,46.26,100
    04/29/2011,13:43,46.21,46.21,46.21,46.21,100
    04/29/2011,13:46,46.32,46.32,46.32,46.32,100
    04/29/2011,13:47,46.39,46.39,46.39,46.39,100
    04/29/2011,13:55,46.35,46.35,46.35,46.35,100
    04/29/2011,13:56,46.35,46.35,46.35,46.35,200
    04/29/2011,13:59,46.45,46.45,46.41,46.41,200
    04/29/2011,14:00,46.43,46.43,46.43,46.43,100
    04/29/2011,14:03,46.44,46.44,46.44,46.44,100
    04/29/2011,14:04,46.48,46.55,46.48,46.55,200
    04/29/2011,14:05,46.55,46.55,46.55,46.55,100
    04/29/2011,14:12,46.6,46.68,46.6,46.68,300
    04/29/2011,14:15,46.67,46.67,46.67,46.67,100
    04/29/2011,14:17,46.67,46.68,46.67,46.68,300
    04/29/2011,14:18,46.67,46.67,46.67,46.67,100
    04/29/2011,14:19,46.68,46.68,46.68,46.68,200
    04/29/2011,14:20,46.72,46.72,46.72,46.72,200
    04/29/2011,14:21,46.77,46.77,46.77,46.77,100
    04/29/2011,14:26,46.76,46.76,46.68,46.68,200
    04/29/2011,14:31,46.76,46.76,46.76,46.76,100
    04/29/2011,14:34,46.76,46.76,46.76,46.76,300
    04/29/2011,14:36,46.77,46.77,46.76,46.76,400
    04/29/2011,14:37,46.77,46.77,46.77,46.77,100
    04/29/2011,14:38,46.77,46.77,46.77,46.77,100
    04/29/2011,14:39,46.78,46.79,46.78,46.79,200
    04/29/2011,14:40,46.96,46.96,46.96,46.96,100
    04/29/2011,14:42,46.92,46.92,46.92,46.92,100
    04/29/2011,14:43,46.89,46.89,46.87,46.87,200
    04/29/2011,14:50,46.83,46.83,46.83,46.83,100
    04/29/2011,14:54,46.82,46.82,46.82,46.82,100
    04/29/2011,14:57,46.87,46.87,46.87,46.87,100
    04/29/2011,14:59,46.85,46.85,46.85,46.85,200
    04/29/2011,15:02,46.83,46.99,46.83,46.87,400
    04/29/2011,15:07,46.85,46.98,46.85,46.98,300
    04/29/2011,15:08,46.97,46.97,46.97,46.97,100
    04/29/2011,15:12,46.96,46.96,46.96,46.96,100
    04/29/2011,15:13,46.93,46.93,46.93,46.93,100
    04/29/2011,15:15,46.92,46.92,46.92,46.92,100
    04/29/2011,15:16,46.88,46.88,46.87,46.87,200
    04/29/2011,15:19,46.877,46.877,46.877,46.877,109
    04/29/2011,15:20,46.76,46.76,46.76,46.76,100
    04/29/2011,15:23,46.723,46.723,46.723,46.723,115
    04/29/2011,15:26,46.75,46.75,46.75,46.75,100
    04/29/2011,15:31,46.81,46.81,46.81,46.81,100
    04/29/2011,15:33,46.82,46.82,46.82,46.82,100
    04/29/2011,15:35,46.82,47.0,46.82,47.0,400
    04/29/2011,15:37,47.0,47.0,47.0,47.0,100
    04/29/2011,15:38,46.98,46.98,46.98,46.98,100
    04/29/2011,15:39,46.92,46.92,46.9,46.9,200
    04/29/2011,15:41,46.89,46.89,46.89,46.89,100
    04/29/2011,15:45,46.89,46.89,46.89,46.89,100
    04/29/2011,15:47,46.89,46.93,46.89,46.93,400
    04/29/2011,15:48,46.93,46.97,46.91,46.91,300
    04/29/2011,15:52,46.92,46.92,46.915,46.915,200
    04/29/2011,15:53,46.91,46.91,46.91,46.91,200
    04/29/2011,15:54,46.89,47.01,46.89,47.01,400
    04/29/2011,15:56,46.94,46.94,46.94,46.94,100
    04/29/2011,15:57,46.92,46.93,46.92,46.93,300
    04/29/2011,15:58,46.87,46.87,46.87,46.87,200
    04/29/2011,15:59,46.84,46.84,46.72,46.72,1300
    04/29/2011,16:00,46.72,46.73,46.68,46.68,1000
    04/29/2011,16:01,46.72,46.72,46.72,46.72,4730
    05/02/2011,09:31,46.76,47.01,46.76,47.01,558
    05/02/2011,09:36,46.91,46.91,46.91,46.91,100
    05/02/2011,09:37,46.9,46.9,46.9,46.9,100
    05/02/2011,09:40,46.97,47.01,46.91,46.91,300
    05/02/2011,09:46,46.87,46.87,46.87,46.87,100
    05/02/2011,09:48,46.88,46.88,46.88,46.88,100
    05/02/2011,09:49,46.91,46.91,46.91,46.91,100
    05/02/2011,09:57,46.86,46.86,46.86,46.86,100
    05/02/2011,10:01,46.81,46.82,46.81,46.82,500
    05/02/2011,10:06,46.8,46.8,46.8,46.8,100
    05/02/2011,10:07,46.81,46.85,46.81,46.85,200
    05/02/2011,10:08,46.85,46.85,46.8,46.8,200
    05/02/2011,10:10,46.8,46.8,46.8,46.8,400
    05/02/2011,10:13,46.76,46.76,46.76,46.76,100
    05/02/2011,10:15,46.76,46.76,46.76,46.76,100
    05/02/2011,10:18,46.73,46.73,46.73,46.73,100
    05/02/2011,10:19,46.72,46.72,46.72,46.72,3307
    05/02/2011,10:20,46.77,46.91,46.77,46.91,800
    05/02/2011,10:21,46.94,46.99,46.94,46.99,300
    05/02/2011,10:22,47.02,47.02,46.92,46.92,495
    05/02/2011,10:24,46.88,46.89,46.88,46.89,200
    05/02/2011,10:26,47.03,47.03,47.03,47.03,200
    05/02/2011,10:44,47.06,47.06,47.06,47.06,500
    05/02/2011,10:46,47.06,47.06,47.06,47.06,100
    05/02/2011,10:49,47.06,47.06,47.06,47.06,300
    05/02/2011,10:52,47.08,47.08,47.08,47.08,100
    05/02/2011,11:00,47.03,47.18,47.03,47.18,500
    05/02/2011,11:11,47.23,47.23,47.18,47.23,305
    05/02/2011,11:14,47.23,47.23,47.23,47.23,100
    05/02/2011,11:20,47.21,47.41,47.21,47.36,1600
    05/02/2011,11:21,47.4,47.45,47.4,47.45,300
    05/02/2011,11:22,47.35,47.35,47.35,47.35,100
    05/02/2011,11:39,47.2,47.2,47.2,47.2,100
    05/02/2011,11:40,47.21,47.21,47.16,47.16,300
    05/02/2011,11:41,47.16,47.16,47.16,47.16,100
    05/02/2011,11:44,47.1,47.1,47.1,47.1,100

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    you'd be much better off importing it to a database, there are no row limits and you can analyse it properly and import just the bits you need into Excel

  9. #9
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    Sounds like a better idea. Thankyou for all of your help !!!

  10. #10
    Registered User
    Join Date
    05-18-2023
    Location
    London, England
    MS-Off Ver
    2019
    Posts
    1

    Re: Too Many Rows With .csv file. Need to create macro to reduce row size or file size

    I know this is years later, but just in case it's helpful for anyone, I'd recommend using Power Query to split up the CSV and import into two tabs or if you don't want to download any apps, just use PowerShell.

    See below. If you aren't comfortable using PowerShell, I made a video recently that shows 4 ways to split a massive CSV into chunks, but since I'm new I can't post links yet. Google "TLDW Tutorials 4 Methods for Splitting a Dataset Too Large to Open in Microsoft Excel" and you should find my video.

    # PowerShell Code

    # Set the path to the CSV file
    $csvFilePath = "C:\dataset.csv"

    # Set the number of rows to split the file
    $rowCount = 500000

    # Load the CSV file into a variable
    $data = Import-Csv $csvFilePath

    # Determine the number of rows in the CSV file
    $rowTotal = $data.Count

    # Determine the number of files to split the data into
    $fileCount = [math]::Ceiling($rowTotal / $rowCount)

    # Split the CSV file into multiple files
    for ($i = 0; $i -lt $fileCount; $i++) {
    $startRow = $i * $rowCount
    $endRow = ($i + 1) * $rowCount - 1
    if ($endRow -ge $rowTotal) { $endRow = $rowTotal - 1 }
    $fileName = "file_" + ($i + 1) + ".csv"
    $data | Select-Object -Skip $startRow -First ($endRow - $startRow + 1) | Export-Csv $fileName -NoTypeInformation
    }

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1