Solved

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

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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

In this post we will learn different types of Android Layout and some basics of an Android App.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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 …

695 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