Solved

load a vba array from csv file without physically opening the csv file

Posted on 2004-04-21
3
1,251 Views
Last Modified: 2008-01-09
Hi,

I have a csv file that is over 256 columns wide (unforturnate). I'm looking for vba coding so that I can load up a vba array, eg. array(377, k), without having to physically open the csv file, and loop through the line items to pick the values and place them into my array. After I have them in an array, I know what to do with it from there. Any help is appreciated. thks.
0
Comment
Question by:Raymond2004_A
3 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 10880159
if you don't want to 'Physically open the csv file", how do you expect to get the records out of the file?

That is sort like saying "I want a pickle out of that closed jar, but I do not want to take the lid off the jar".

In order to get the records out of the file, you must open the file.

AW
0
 

Author Comment

by:Raymond2004_A
ID: 10880465
Let me clarify,

When i get data from an xml file or a txt file, I do not need to physically open the file. I open a vba session with the file opened in the computers memory somewhere and have access to it, but the file is not opened in any program that i can see.  Sort of like this:
Open MyCSVFileName For Input As #InputFileHandle

Right now i physically open the csv file in excel but it does not completely open because of the limited columns in excel. I want to read the csv file in a similar way like an xml or txt file.
0
 
LVL 14

Accepted Solution

by:
JohnK813 earned 75 total points
ID: 10881371
Using VB/VBA to access a file is considered "opening" that file, so that's where some confusion is.

You're on the right track with this:

Open MyCSVFileName For Input As #InputFileHandle

Here's what you can do to load everything into an array.

Dim csvarray()
Dim split_array
Dim sBuffer as String
#InputFileHandle=freefile
Open MyCSVFileName For Input As #InputFileHandle
While not EOF(#InputFileHandle)
  Line Input #InputFileHandle, sBuffer 'sBuffer now holds a line of your file
  split_array = split(sBuffer, ",") 'split_array is an array of one line of your file - each element is a field
    ReDim Preserve csvarray(ubound(csvarray) + 1) 'increase array size by 1
  csvarray(ubound(csvarray)) = split_array 'load array from file into new line of master array
Wend
close #InputFileHandle

Now, to access word 2 of line 3, all you need to do is say csvarray(3)(2).  VB has the power to store arrays as elements of arrays, and this is probably your best bet here, unless you want to say,

For j = 0 to ubound(split_array)
  csvarray(i,j) = split_array(j)
Next i

which is very time consuming.

I understand that VB may have some of its own functions for CSV file I/O, but I don't know them.  Maybe someone else here does, but you have at least 1 possible solution for now.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This is an explanation of a simple data model to help parse a JSON feed
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now