CODEWITHSUNDEEP
vbacsvexcel
tyrex
tyrex

Reputation: 8869

Excel VBA: Best way to work with data of CSV file

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

Upvotes: 2

Views: 13561

Answers (1)

jdh
jdh

Reputation: 1655

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.

Upvotes: 1

Related Questions