Load multiple CSV files in a spreadsheet faster

**grid** · 01-05-2010, 04:34 PM

Hello,

In my spreadsheet I load 26 CSV files. Each CSV file has 3 columns and 7 rows. It uses the connection query in Excel. It is only relatively slow to refresh the data. To refresh the data, I can press the refresh data button in Excel.

But in practice I use this code every 5 seconds:

Please Login or Register  to view this content.

Every time the update happened, the worksheet is very slow. Sometimes I have to wait 1 or 2 seconds. Then freezes my entire Excel here. It may be faster. The files need only be read.

The code I personally made with the Macro Recorder:

Please Login or Register  to view this content.

Can someone give me another code to load CSV into my spreadsheet? (maybe no connection query etc). I do not know the other possibilities. But in theory it can go much faster.

Best regards,

grid

**Leith Ross** · 01-05-2010, 04:40 PM

Hello grid,

Since csv files are generally are static and they are located on your hard drive, why do they need to be refreshed every 5 seconds?

**grid** · 01-05-2010, 04:50 PM

Hello Leith Ross,

The prices come from the Internet. With Excel I can perform calculations on these prices. The program that writes the CSV file, can not perform these calculations. Prices are updated every 5 seconds by the program.

Best regards,

grid

**grid** · 01-05-2010, 05:12 PM

Hello

Maybe someone can edit this code. To qualify as an example. These documents load faster with this code.

Not yet:

This code does not recognize my CSV file separators. I use ;

I have not set the range where I would place the CSV file. So for example B1 to H3 on the spreadsheet.

Furthermore:

Perhaps someone can edit the code? (another fast load from a code is also welcome)

Please Login or Register  to view this content.

This code is on this page:

http://www.cpearson.com/excel/ImpText.aspx

Best regards,

grid

**broro183** · 01-05-2010, 06:41 PM

hi Grid,

I recommend turning off calculation (alongside the screenupdating) while loading the files.
Have you tried/could it be quicker to bring in the "whole file" & then process it in Excel?

Ron DeBruin has a variety of techniques for merging files about 1/2 way down the page of this link: http://www.rondebruin.nl/tips.htm
Including this one (http://www.rondebruin.nl/csv.htm) which is super quick but may not be flexible enough for your needs.

hth
Rob

**grid** · 01-06-2010, 09:05 AM

Hello broro183,

I know that page and also the script of Ron de Bruin. Because I always look first at Google. That option was already in my mind.

I'm still looking for a csv file to load through a different way than the query connection.

Best regards,

grid

**broro183** · 01-06-2010, 04:25 PM

hi Grid,

Originally Posted by grid

...
This code does not recognize my CSV file separators. I use ;

Please Login or Register  to view this content.

Edit: Can you please post a reference to the source of your code in post number 4?
btw, Chip Pearson's page (http://www.cpearson.com/excel/ImpText.aspx), shows this (or a very similar) macro, so I recommend you read through the page which explains what the "Sep" variable does.

hth
Rob

**broro183** · 01-06-2010, 05:15 PM

hi all,
For any other helpers who may have some suggestions for Grid, here are some further details from Grid's pm. ...

Originally Posted by grid

Hello broro183,
This way we get a different subject. I would first discuss the other possibility.
I know that page and also the script of Ron de Bruin. Because I always look first at Google. It is useful to merge the file indeed.
The problem is that the script of Ron de Bruin a folder must be manually entered. I want it automatically goes. I want the location of the folder in the VBA script. I do not want input forms. If you can program it for me. Be my guest

I would also not an Excel file as a result. I want a text / csv file. It has to be compiled in Excel. It is a part of my other spreadsheet.
Best regards,
grid

re "problem",
You could always ask a question on how to change the code so you don't need to manually enter the folder...
Have you tried any of Ron's code examples?
If so, does it seem faster (ignoring the user interaction) than what you are currently using?

re "I want a text / csv file. It has to be compiled in Excel."
Have you tried recording a macro of saving the file as a text/csv file?
Which file type do you want, ".txt" or ".csv"?
Also, how can it be "part of your other spreadsheet" when you want a text/csv file?
-------
Leith (or others), are better placed (than me) to provide opinions on my next questions, because I have zilch personal experience with connections, but I'll ask anyway...
Why are you using csv files as such short term "middlemen"?
Can you bypass "the program" completely & query the internet directly from Excel?

Rob

**grid** · 01-07-2010, 07:15 PM

Hello broro183,

I puzzle with the code from Chip Pearson. This code uses no connection query. The problem is the placement of my CSV in a specific range. It must further be programmed.

With a macro I can set the specific range. If I work somewhere else in the worksheet, it does not work well with the Macro.

My interpretation of a part of my spreadsheet is simply that the CSV is imported. The files are stored on my hard disk. From the internet I can not pick my files with a query connection.

Anyway, I'm not looking for a solution to a file merge. I only want a script (maybe the one made by Chip Pearson) to use a file txt / csv to import into a specifical range. So as an example starting with B3.

Best regards,

grid

**broro183** · 01-10-2010, 03:29 PM

hi Grid,

Sorry I haven't been back to this thread for a while.
Can you please post a sample xls file with dummy data which includes your existing code?
Also, can you please attach an example csv file for testing?

In your first post you mentioned "26 csv files" but in later posts your comments seem to relate to only a single csv file. Can you please explain exactly what you need & provide explanatory comments in the example file that you upload?

Rob