2

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

Community
  • 1
  • 1
tyrex
  • 8,208
  • 12
  • 43
  • 50
  • 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 05 '12 at 16:02
  • 2
    You can also use the FileSystemObject (add a reference to the Microsoft Scripting Runtime). That is always my preference. – markblandford Mar 05 '12 at 16:12
  • 1
    My answer to a similar question this morning (option B for performance): http://stackoverflow.com/questions/9564908/open-csv-file-via-vba-performance/9565276#9565276 – assylias Mar 05 '12 at 16:50
  • Thx assylias! I somehow missed that thread. – tyrex Mar 05 '12 at 16:58

1 Answers1

1

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.

jdh
  • 1,637
  • 14
  • 12
  • 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 05 '12 at 18:39
  • from a programming time I agree. – assylias Mar 05 '12 at 18:44