Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I am looking for a direct and efficient method to read out csv-files and handily work with the data in Excel/VBA?

The best thing would be: direct access of data by specifying row and column. Can you tell me of your preferred option? Do you know an additional option to the following two?

A: Use Workbooks.Open or Workbooks.OpenText to open the csv-file as a workbook. Then work with the workbook (compare this thread).

B: Use Open strFilename For Input As #1 to write the data into a string. Work with the string (compare this thread).

Thanks a lot!

==========EDIT=========

Let me add what I have learned from your posts so far: The optimal option to do the task depends too much on what you want to do exactly, thus no answer possible. Also, there are the following additional options to read csv files:

C: Use VBScript-type language with ADO (SQL-type statements). I still am figuring out how to create a minimal example that works.

D: Use FileSystemObject, see e.g. this thread

share|improve this question
3  
Depends what you want to do... There is no absolute answer to this question. If you need the Excel interface, use method A. If you are loading many files and need speed, do B. –  Jean-François Corbett Mar 5 '12 at 16:02
2  
You can also use the FileSystemObject (add a reference to the Microsoft Scripting Runtime). That is always my preference. –  creamyegg Mar 5 '12 at 16:12
 
I agree with JFC. –  Siddharth Rout Mar 5 '12 at 16:23
1  
My answer to a similar question this morning (option B for performance): stackoverflow.com/questions/9564908/… –  assylias Mar 5 '12 at 16:50
 
Thx assylias! I somehow missed that thread. –  FMan Mar 5 '12 at 16:58
add comment

1 Answer

up vote 1 down vote

The fastest and most efficient way to add CSV data to excel is to use Excel's text import wizard. This parses CSV file, giving you several options to format and organize the data. Typically, when programming one's own CSV parser, one will ignore the odd syntax cases, causing rework of the parsing code. Using the excel wizard covers this and gives you some other bonuses (like formatting options). To load csv, (in Excel 2007/2010) from the "data" tab, pick "From Text" to start the "Import Text Wizard". Note the default delimiter is tab, so you'll need to change it to comma (or whatever character) in step 2.

share|improve this answer
1  
Have you tested the performance of the various options? –  assylias Mar 5 '12 at 16:54
1  
The efficiency I am suggesting, is in programming time. Writing your own version takes a few cycles of getting it right. Using Excel's built-in solution already handles these cases. For example, the solution and in the links, does not handle these valid csv with a quoted commma: chicago, LA, "NY,NY" –  jdh Mar 5 '12 at 18:39
 
from a programming time I agree. –  assylias Mar 5 '12 at 18:44
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.