Solved

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

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

738 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