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

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


---
Message posted from http://www.ExcelForum.com/

0
hce.yf1nn (1)
12/14/2003 7:20:18 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
761 Views

Similar Articles

[PageSpeed] 12

What version of Excel are you using?

--
Regards,
Tom Ogilvy

hce <hce.yf1nn@excelforum-nospam.com> wrote in message
news:hce.yf1nn@excelforum-nospam.com...
> 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
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
twogilvy (1078)
12/14/2003 3:25:18 PM
You got one more reply back at the previous (not original) post.

hce wrote:
> 
> 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
> 
> ---
> Message posted from http://www.ExcelForum.com/

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
12/14/2003 4:07:42 PM
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
0
mikeargy (62)
12/14/2003 8:04:16 PM
Reply:

Similar Artilces:

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

sort data
i am trying, unsuccessfullty, to sort some data (a small example is below) so I can graph it. I want to put it into a pivot table format but don't want calculations, just the actual data. any ideas??/ Location DATE Result WAT002WA ALBANY BOTTOM 19-Jul-05 0.83 WAT002WA ALBANY BOTTOM 23-Jun-05 0.8 WAT002WA ALBANY BOTTOM 28-May-05 1.16 WAT002WA ALBANY BOTTOM 02-May-05 0.93 WAT002WA ALBANY BOTTOM 06-Apr-05 0.66 WAT002WA ALBANY BOTTOM 11-Mar-05 0.59 WAT002WA ALBANY BOTTOM 13-Feb-05 0.54 WAT002WA ALBANY BOTTOM 18-Jan-05 0.62 WAT002WA ALBANY TOP 09-Mar-07 0.73 ...

MDI App will no longer open file by double clicking associated filetype
OK, I had an app which was originally based upon an MDI app. It has been 'modded' to add various functionalities with code from www.codeguru.com and www.codeproject.com. Unfortunately, I just recently noticed that the ability to double click on the associated filetype (from a folder, desktop, etc) now no longer works. Instead, I get a messagebox: Title: (pathname of file) Message: Windows cannot find '(pathname of file).' Make sure you typed the name correctly, and then try again. To search for a file, click the Start button, and then click Search. This is on Windows XP. ...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

GP 10 Business Portal and .Net framwork 3.0 SP1
I applied SP1 to .NET framework 3.0. Now I have to reverse it because Dynamics GP 10 Business Portal doesn't recognize 3.0 SP1 What's the proper procedure to do the reversal since I have MOSS 2007 installed and configured properly with SP1 already. Thanks Bill ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

data migration of cdf tables to crm
hi, i am trying to implement crm for an organisation. the cdf tables for both accounts and contacts are ready and data is present int he info tables.but the next step of migration is not happening. executing the migration.exe tool shows process completed in the log but verifying the migration_info table shows "not processed" against the corresponding rows.it is not showing any errors but still not migrating. please help. i can use all the help i could get. thank you. "Yamini.P.S" <Yamini.P.S@discussions.microsoft.com> wrote in message news:94FD56E3-3B28-43D...

How to run Visual Basic from Excel sheet #3
How do I run a Visual Basic routine from a normal Excel cell. I would like to do something like this: =IF(RunIt=TRUE, Run my Visual Basic routine, Do nothing) So if some calculations results in the variable "RunIt" is set equal to TRUE, the Visual Basic routine must run. Kristian ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Don't want to open a new file when launching or returning to application
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Is it possible to set Word up so that it doesn't open a new, blank file every time you launch it or return to it from another application? I can't see how to do it in preferences. It's quite frustrating to have the desktop cluttered up in this way! The same thing applies in Excel, too. This is in compliance with Apple's guidelines for OS X. You might be able to avoid it on launch with Apple Script, but IMHO, that's more trouble than it's worth :-) The document doesn't "clutte...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

total size of files
I need a script to find out the number & total size of files based upon the file extention across about 30 servers. I'm searching for mdb files. The script will be run remotely against local drives on these servers. Thanks On Nov 18, 10:19=A0am, Tom1 <usernetu...@yahoo.com> wrote: > I need a script to find out the number & total size of files based > upon the file extention across about 30 servers. I'm searching for mdb > files. The script will be run remotely against local drives on these > servers. Thanks <warning> <aircode> with ...

How to share Outlook 2003 with 3 computers (peer to peer)
How to I share my Outlook folders, calendar, contacts, tasks, notes? NO Exchange Server! ( I am peer to peer with 3 computers in my office) help! David Create a Personal Folders file on a drive/share that all 3 computer have access to. This PST file can be opened via the File > Open > Outlook data file on each machine. Now for the bad news, Outlook opens the file for exclusive access. Therefore when Machine A has Outlook running, Machines B/C cannot open/work with this shared personal folders file. By the way, there are 3rd party solutions that let multiple machines share t...

sending data to com port
Hello, I am using VC++ 6.0 (of Visual studio 6.0) How can I send data to com port via VC++ code ? Need sample code, please. Thanks :) See this article on codeproject..it covers up most of the stuff... http://www.codeproject.com/system/serial_com.asp - Parhar "Eitan" <no_spam_please@nospam.com> wrote in message news:uVpJFP87DHA.1804@TK2MSFTNGP12.phx.gbl... > Hello, > I am using VC++ 6.0 (of Visual studio 6.0) > How can I send data to com port via VC++ code ? > Need sample code, please. > > Thanks :) > > See the "CSerial" class from Tom...

queries which pull data in multiple "rows" in one table and compar
I have a project to complete where the end user wants me to write several queries which pull data in multiple "rows" in one table and compare them to data in another using access. He also needs a report written based on the findings of which data meets certain criteria within those queries. I think the report part will be pretty easy, but can someone give me some suggestions on what would be the best way to create the queries in access? Thanks Da Chosen One On Sun, 10 Jan 2010 19:22:01 -0800, Da Chosen One <Da Chosen One@discussions.microsoft.com> wrote: You...

List Box #3
Hi, I am trying for the first time to build a list box. I need a list box that the user will be able to choose all the relevant items and that they will appear in another list box - with the option to remove from it (by selecting and pressing 'del'). I used the tool box to create a list box. I tried to put the data in it using AddString and SetDlgItemText (I created a variable for this control), but get an error message when I run it Could anyone please tell me how to do it? Many thanks > I used the tool box to create a list box. I tried to put the data in it > using Ad...

Outlook express files gone
I started up outlook express today and i have no view, and cannot get a view of all the files, inbox, outbox etc. The only way I can see my emails is by going into file and open then clicking next or previous! Any ideas?? Submitted via EggHeadCafe - Software Developer Portal of Choice Easy "NO SCRIPT" DataGrid Tooltips in ASP.NET http://www.eggheadcafe.com/tutorials/aspnet/c0d39393-80fe-481f-af5c-0e18fa4e2e43/easy-no-script--datagri.aspx View | Layout. Is: Folder List checked? -- Bruce Hagen MS-MVP [Mail] Imperial Beach, CA "Ian Va...

Data............Group
if I group columns and hide the columns with the + is it possible to give the diferent groups names where they are listed at the top left 1, 2, 3. Not quite sure what you want, but maybe.... View|Custom Views would allow you to hide/show the groups the way you want. Esrei wrote: > > if I group columns and hide the columns with the + is it > possible to give the diferent groups names where they are > listed at the top left 1, 2, 3. -- Dave Peterson ...

Excel 2003 step by step book files
Does Microsoft have a place where I can download the files for Excel 2003 Step by Step? I lost the CD. I looked here http://www.microsoft.com/mspress/books/5605.asp but I don't see anyplace for downloading the files. ...

Fill text boxes with data from another text box
I have a form that has two sections. Section 1 has orginator name, address, city state, zip. Section two has owner name, address, city, state, and zip. If orginator and owner are the same, I want to just hit a button or check mark to copy data from section one to section two. Can this be done, and how would you do it. Thanks for your help- Porkchop. Porkchop, To copy data from one control (and, hence, field, if the form control source is set to the name of a field in the form's underlying record source), put the following in the AfterUpdate event procedure of the checkbox: If ...

data points #3
I have a scatter chart with the following info : sales price, square footage, and subdivision. With the x and y axis being sales price and square footage. There are some entries where i will have several sales in one subdivision and i would like all those points to show up in the same color. How do i get several points to show up in the same color instead of excel assigning each entry a new label? Include all the related points in the same data series, rather than as a different data series for each point. -- David Biddulph "affordablegreen" <affordablegreen@discussion...

Archive hosed my Money 2003 File!
Around the first of the year, I archive my Money 2003 file and it messed up my checking account to the point where I have to change the starting balance when I reconcile. It seems that Money archived all transactions older than 1/1/2005, regardless of whether they were reconciled yet or not! I could complain about that forever it ticks me off so much. I manually added back the transactions that were still outstanding and if I fudge the starting balance each month, I am able to reconcile as long as I take the "ignore differences" option when I am balanced by my reckoning. My quest...

How do I activate sort in Excel? #3
I have worksheets set up in Excel. I am wanting to sort 1 column of these worksheets, but my sort button seems to be grayed out & I cannot select it. How do I activate the sort function again. Any ideas? Thanks for any help!! The only time I have ever seen the A-Z or Z-A buttons dimmed is when editing cell content. Make sure the word 'Ready' appears at the left end of the Status Bar, and not the word 'Enter' or 'Edit'. If this doesn't help, please post back with more specific detail including version. LOL |:>) "marygries@passport.com" w...

Exmerge #3
Where can I download the above for Exch 5.5? Hugh .. Try here .. http://www.mvps.org/exchange/Exmerge.htm -- David Kilpatrick "Hugh" <anonymous@discussions.microsoft.com> wrote in message news:e9c501c3f096$1c0d2510$a501280a@phx.gbl... > Where can I download the above for Exch 5.5? http://groups.google.com/groups?as_q=exmerge%20download&safe=images&ie=ISO-8859-1&as_drrb=q&as_qdr=m&lr=&hl=en Hugh wrote: > Where can I download the above for Exch 5.5? ftp://ftp.microsoft.com/PSS/Tools/Exchange%20Support%20Tools/Exmerge/Intel/ -- Hope that ...