?
Solved

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

Posted on 2004-04-21
3
Medium Priority
?
1,296 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 225 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Six Sigma Control Plans

801 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