how to extract data from csv file?

[hce]

thanks to all who had replied the other time... but wat i really need is
that in csv files, the data is separated by commas and not columns. i
don't know i can specify only to extract the data in the "6th comma" -
if in excel terms, then it would be Column F. One person suggested to
link the data i want and update the links whenever i need but with this
soln, i would need to open the csv file and make it an excel file then
it can work so it doesn't really help much though it would save me the
trouble of copy and paste the data i want. another wrote a vb command
for me to try but all the data got pasted and was never separated so it
was all in one column. on my own, i managed to write a vb command to
extract only the data i wanted but it was not 100% successful because
some data were missing and the data that appeared were all like this
"data" - in quotes which i dun understand y? is there really no way i
can open an excel workbook, run a vb command, and then the data would
be extracted and pasted onto the specified worksheet, column in one
step without me opening the csv file? meanwhile i just have to keep
doing the same stuff... open the csv, make it delimited, comma, finish,
copy and paste, close csv, save xls workbook.... and so on... anyway i
really appreciate all the help given...

cheers

 

[Dave Peterson]

If you open a comma delimited file in excel and it all goes into one column,
then I'd guess that your regional settings didn't have a comma as the list
separator. VBA is USA centric and the USA likes commas for the separator.

Windows Start button|Settings|control panel|Regional settings Applet|Number Tab

Make the list separator character match your data's list separator.

Another option would be to rename your *.csv to *.txt and then record a macro
when you open the files. You can specify your separator explicitly.

 

[Tom Ogilvy]

Dave,
He wants to bring in a selected column or two from the CSV file (three
actually) without opening the whole CSV file and copying the selected
columns. Same as the first time you attempted to answer his question.

 

[hce]

hi dave

thanks so much for your advice and help... i really appreciate it...
have tried your method to change the separator as u said but in fact
mine was already set to the us standard... i run the command again bu
again the data is all in one column... i know i can record a macro t
do the stuff that i need but because i'm working as an analyst and
have to extract data from any csv files as and when needed and the
might not have the same structure and it would be faster and mor
efficient for me if i can run what i need from just one command... wha
i tried in my command is to try and count the no of characters in eac
column until the column i need... for example, the data i need start
from the 110th character so i specified in vb to start extracting fro
110th to 130th... but when the data comes out, some are missing and al
are in quotes eg. "12345" which i dunno why but i think it's because i
some rows, the number of charcters is different...... of course i ca
run a macro to get rid of the quotes but i dunno how i can solve th
problem of missing data...? if i can somehow specify the column for eg
worksheet.cells(4,1) like in excel then it would be great but i stil
can't figure out the right vb code... do you? have a nice day

cheer

 

[Dave Peterson]

I really thought that opening the 3 csv files and extracting the columns would
be easier than reading the text file and doing the parsing yourself. (So I
avoided your real question like the plague!)

And if they're really comma separated values, I bet that the data the you need
_sometimes_ appears in column 110. But usually with CSV files, this will vary.

(If the file were fixed width fields, then you'd need something different--just
read the input record and strip out the stuff you want with mid()'s, right()'s
and (left()'s).)

I really wasn't sure which "columns" to retrieve. (and no, since the .csv file
isn't an excel file, you can't refer to it by cells(x,y).) So I retrieved
6,7,8. (Notice in the code, that I referred to mysplit(5), mysplit(6) and
mysplit(7).

It turns out that mySplit should be an array with the first element 0. (0 to
whatever). So you have to offset it by one.

And I wasn't sure where to put all the stuff being retrieved. So I used the
first row of the activesheet. I went to the far right and came back to the last
used cell. Then over one to the right.

Here's something that might get you started.


Option Explicit
Sub testme01()

Dim wks As Worksheet
Dim oCol As Long
Dim mySplit As Variant

Dim CalcMode As Long
Dim mstrWks As Worksheet
Dim CSVNames As Variant
Dim CSVWkbks() As Workbook
Dim iCtr As Long
Dim myPath As String

myPath = "c:\my documents\excel"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

CSVNames = Array("book1.csv", "book2.csv", "book3.csv")

For iCtr = LBound(CSVNames) To UBound(CSVNames)
Call GetCols(myPath & CSVNames(iCtr), ActiveSheet)
Next iCtr

End Sub
Function GetCols(wkbkName As String, wks As Worksheet)

Dim myFileName As Variant
Dim myFileNum As Long
Dim myLine As String

Dim oCol As Long
Dim oRow As Long
Dim mySplit As Variant

myFileNum = FreeFile()
Close #myFileNum
Open wkbkName For Input As #myFileNum

'put first retreived column in row 1 of first open column to the far right
With wks
oCol = .Cells(1, .Columns.Count).End(xlToLeft).Offset(0, 1).Column
End With
oRow = 1
Do While Not EOF(myFileNum)
Line Input #myFileNum, myLine
mySplit = Split97(myLine, ",")
With wks
'mysplit is (0 to ###).
'to get the 6th column, use mysplt(5)
If UBound(mySplit) - LBound(mySplit) > 6 Then
.Cells(oRow, oCol).Value = mySplit(5)
.Cells(oRow, oCol + 1).Value = mySplit(6)
.Cells(oRow, oCol + 2).Value = mySplit(7)
End If
End With
oRow = oRow + 1
Loop
Close #myFileNum

End Function
Public Function ReadUntil(ByRef sIn As String, _
sDelim As String, Optional bCompare As Long _
= vbBinaryCompare) As String
Dim nPos As String
nPos = InStr(1, sIn, sDelim, bCompare)
If nPos > 0 Then
ReadUntil = Left(sIn, nPos - 1)
sIn = Mid(sIn, nPos + Len(sDelim))
End If
End Function
Public Function Split97(ByVal sIn As String, Optional sDelim As _
String, Optional nLimit As Long = -1, Optional bCompare As _
Long = vbBinaryCompare) As Variant
Dim sRead As String, sOut() As String, nC As Integer
If sDelim = "" Then
Split97 = sIn
End If
sRead = ReadUntil(sIn, sDelim, bCompare)
Do
ReDim Preserve sOut(nC)
sOut(nC) = sRead
nC = nC + 1
If nLimit <> -1 And nC >= nLimit Then Exit Do
sRead = ReadUntil(sIn, sDelim)
Loop While sRead <> ""
ReDim Preserve sOut(nC)
sOut(nC) = sIn
Split97 = sOut
End Function


The last two subs are stolen from an MSKB.
http://support.microsoft.com/default.aspx?scid=kb;en-us;188007
HOWTO: Simulate Visual Basic 6.0 String Functions in VB5

If you're using xl2k or higher, you can dump these two routines and change
split97 to split. (Split was added in xl2k.)


======
(I still think opening the .csv files with excel and just copying the columns
would be easier (and maybe quicker???)--well, if you could get past that "all in
one column" stuff.)

good luck,

 

[Dave Peterson]

And I just read your later post. You have xl2002/xl2003. You can change that
split97 to split.