how to load csv files faster in excel vba?

Question

how to import the data/files in csv format to excel vba in a much faster way? so far,i could get the files n display in excel but it is one by one. this will take a lot of time.

how do we make it in faster way?

Answer 1

You could write a simple console application to parse a batch of csv files and save them to excel files.
Not the easiest solution but it could be an option.

Answer 2

Please can you give an example of the CSV files in question? Are they comma separated values with text in double quotes, or without double quotes? I have some VBScript and VB6 code which will parse CSV, and I have other means for data without double quotes.

Does each column have a heading?

Answer 3

Next question. Are the headings single words? The direction I'm heading is to create arrays named after the headings (using Eval() and Execute()) and fill these with data from the file. It'd be an implementation similar to [VBScript] CSV to variables using Execute over on my Codeaholic weblog.

Answer 4

It occurs to me that you should be able to read the whole CSV into Excel anyway, as Excel has a CSV filter. But you are wanting to do it programmatically?

Answer 5

This will get a file into a one dimensional array quickly

Open "myfile.csv" For Input As 1
Dim Txt As String
Txt = Input(LOF(1), 1)
Close #1
Dim V As Variant
V = Split(Txt, ",")

Then V contains all the items in a single column, NB first item is V(0)

Answer 6

So let me see if I have got this figured right.

1. You're in Excel
2. You have a list of CSV files
3. You want to traverse that list
4. You want to open each item and import its data into Excel

Have I got it right thus far?

Answer 7

I am a bit puzzled in that most versions of Excel will open .csv files with out any problems.

strPath = "C:\Docs\"
strFile = Dir(strPath & "*.csv")

Do While strFile <> ""
    Workbooks.Open Filename:=strPath & strFile
    ActiveWorkbook.SaveAs Filename:=strPath & Mid(strFile, 1, InStr(strFile, ".") - 1) _
        & "Conv.xls", FileFormat:=xlNormal

    strFile = Dir
Loop

Answer 8

you can also use workbooks.opentext

Answer 9

The idea I had originally was as follows. Given this data

Dog Names,Dog Ages,Collar Size
Woof,3,4
Bowser,2,5
Ruffy,4.5,6
Angel,1,7
Demon,7,8
Dog,9,2

create three global arrays, called Dog_Names, Dog_Ages and Collar_Size and populate them with the data in the CSV file.

This bit of VBScript does that job and displays the results. Remove the comment mark from the wscript.echo in the x subroutine to see it all happen.

Option Explicit

Dim FSO
Set FSO = CreateObject( "Scripting.FileSystemObject" )

Dim oStream
Dim sData
Dim aData

Set oStream = fso.OpenTextFile("data.csv")
sData       = oStream.ReadAll
aData       = Split( sData, vbNewLine )

Dim sLine
sLine = aData(0)

Dim aContent
aContent = Split( sLine, "," )

Dim aNames()
Dim nArrayCount
nArrayCount = UBound( aContent )
ReDim aNames( nArrayCount )

Dim i

For i = 0 To nArrayCount
  aNames(i) = Replace( aContent( i ), " ", "_" )
  x "dim " & aNames(i) & "()"
Next

For j = 0 To nArrayCount
  x "redim " & aNames(j) & "( " & UBound( aData ) - 1 & " )"
Next

Dim j
Dim actual
actual = 0

For i = 1 To UBound( aData )
  sLine = aData( i )
  If sLine <> vbnullstring Then
    actual = actual + 1
    aContent = Split( sLine, "," )
    For j = 0 To nArrayCount
      x aNames(j) & "(" & i - 1 & ")=" & Chr(34) & aContent(j) & Chr(34)
    Next
  End If
Next

For j = 0 To nArrayCount
  x "redim preserve " & aNames(j) & "(" & actual - 1 & ")"
Next

For i = 0 To actual - 1
  For j = 0 To nArrayCount
    x "wscript.echo aNames(" & j & ")," & aNames(j) & "(" & i & ")"
  Next
Next

Sub x( s )
  'wscript.echo s
  executeglobal s
End Sub

The result looks like this

>cscript "C:\Documents and Settings\Bruce\Desktop\datathing.vbs"
Dog_Names Woof
Dog_Ages 3
Collar_Size 4
Dog_Names Bowser
Dog_Ages 2
Collar_Size 5
Dog_Names Ruffy
Dog_Ages 4.5
Collar_Size 6
Dog_Names Angel
Dog_Ages 1
Collar_Size 7
Dog_Names Demon
Dog_Ages 7
Collar_Size 8
Dog_Names Dog
Dog_Ages 9
Collar_Size 2
>Exit code: 0    Time: 0.338

Answer 10

boost - as I understand it ExecuteGlobal (and Execute) are only available in VBScript but not in VBA?