PDA

View Full Version : OT- Excel problem/question



Chuck Wintle
06-14-2009, 8:23 AM
How does one go about getting excel to import csv. data into specific columns and cells. I know there is a way to do that using the macro but I don't know how. Thnaks

Greg Cuetara
06-14-2009, 10:21 AM
Did you try just opening the file directly from excel. I think if you try opening it up the macro will pop up where you can break out the columns.

Greg

Steve Rozmiarek
06-14-2009, 1:04 PM
I think Greg is right. Which version of Excel are you using though?

Chuck Wintle
06-14-2009, 3:04 PM
Excel version 2003. The reason I am asking is i saw someone once use a macro to import values right into a a spread sheet.

Todd Willhoit
06-14-2009, 3:11 PM
As mentioned above, Excel will open a csv file directly from the File>Open menu selection or by double-clicking the file. Macros are not involved. By definition, a csv file is formatted such that cell data are separated with commas. You can open one from Notepad to see what it looks like.

Edit: If a macro were used, the data was likely in another format (not csv), was imported into an existing worksheet, or required some manipulation specific to the existing worksheet.

Todd

Jim Rimmer
06-14-2009, 4:32 PM
I think the other comments are right but you might try:
Copy and Paste data into spreadsheet
Click on Data and select Data to Columns from drop down menu

I think it gives an option for CSV data after you select it.

Peter Stahl
06-15-2009, 6:56 AM
How does one go about getting excel to import csv. data into specific columns and cells. I know there is a way to do that using the macro but I don't know how. Thnaks

Charles,

I've written simple macros and usually use bits and pieces from ones I find on the web or in some others have written. Here's a link to one someone has written but may not be exactly what you want. (http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23343409.html) Do a Google search (e.g. "excel macro to import csv" (what I used)), more info there.

Doug Shepard
06-15-2009, 8:44 AM
If you actually have to break text out and specify columns, it's real tedious (akin to coding COBOL:(). You'll have to assign dummy fields for spaces between the text, deal with tabs, specify which fields to treat as numbers/dates, etc. i haven't had to do it for a few years but it took a number of attempts before I got it all fine tuned so that it imported into the the right fields. Have fun.

Eric DeSilva
06-15-2009, 9:35 AM
If you actually have to break text out and specify columns, it's real tedious (akin to coding COBOL:(). You'll have to assign dummy fields for spaces between the text, deal with tabs, specify which fields to treat as numbers/dates, etc. i haven't had to do it for a few years but it took a number of attempts before I got it all fine tuned so that it imported into the the right fields. Have fun.

Excel gives a few options for importing. Sounds like you are taking about fixed length fields, which aren't very common and your experience shouldn't be applicable to .csv files. Delimited files, like .csv (could also be tab-deliminated or pipe-delimited), are more common and easily imported into Excel without any macros. Using import allows you to tell Excel what the delimiter is, so its very painless.

Frank Hagan
06-15-2009, 12:38 PM
Sometimes I get weird results using Excel. I have Excel 2002, and the most reliable way to import tab or comma delimited files is to rename them with a .TXT extension rather than .CSV and use File * Open and select "Text Files (*.prn; *.txt; *.csv)" from the "Files of Type" box at the bottom of the open file dialog. You should then get the import filter, that allows you to specify the columns to import, data types for each column, etc.

Typically, I get a column of numbers formatted as text. Reformatting the column doesn't change them all, but "Tools * Text to Columns * Finish" fixes it, and my numbers are numbers again.