Solved

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

Posted on 2004-04-21
3
1,264 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Exe program is not a valid Win 32 application 15 104
topping2 challenge 13 80
Advice on Xojo as a development tool over VB. 4 35
T-SQL:  Sigh---Boy, this is fun.... 12 28
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
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 …
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 fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

911 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

22 Experts available now in Live!

Get 1:1 Help Now