how to extract data from csv file? (part 3)

  • Thread starter Thread starter hce
  • Start date Start date
H

hce

hi dave

thanks so much for your advice and help... i really appreciate it... i
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 but
again the data is all in one column... i know i can record a macro to
do the stuff that i need but because i'm working as an analyst and i
have to extract data from any csv files as and when needed and they
might not have the same structure and it would be faster and more
efficient for me if i can run what i need from just one command... what
i tried in my command is to try and count the no of characters in each
column until the column i need... for example, the data i need starts
from the 110th character so i specified in vb to start extracting from
110th to 130th... but when the data comes out, some are missing and all
are in quotes eg. "12345" which i dunno why but i think it's because in
some rows, the number of charcters is different...... of course i can
run a macro to get rid of the quotes but i dunno how i can solve the
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 still
can't figure out the right vb code... do you? have a nice day

cheers
 
An alternative to a VBA approach would be specifying the csv file as a
data source.

Start a new workbook.
Data|Get External Data...>Import text file

Now go through the import process just like opening a csv file. You
can specify which columns to skip, and whether the column is a date,
text, etc.

Once the data is in your workbook, right-click somewhere in the data
and choose data range properties. Here you can set Excel to prompt or
not prompt for a file name, when the data will be refreshed, etc.

There is a refresh data button on the data toolbar, and in the context
(right-click in the returned data) menu.





Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 
thanks for your advice. it worked but is there a way to specify only a
particular column of data instead of the whole file?
 
Newsgroups: microsoft.public.excel.misc
NNTP-Posting-Host: 69-56-172-122.theplanet.com 69.56.172.122
Path: internal1.nntp.ash.giganews.com!border2.nntp.ash.giganews.com!border1.nntp.ash.giganews.com!firehose2!nntp4!intern1.nntp.aus1.giganews.com!border1.nntp.aus1.giganews.com!nntp.giganews.com!cyclone-sf.pbi.net!216.218.192.242!news.he.net!news-hog.berkeley.edu!ucberkeley!newshub.sdsu.edu!msrn-out!msrtrans!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
Lines: 1
Xref: intern1.nntp.aus1.giganews.com microsoft.public.excel.misc:332662

thanks for your advice. it worked but is there a way to specify only a
particular column of data instead of the whole file?
 
Back
Top