Can You Upload Excel Information to Box

Importing a Subset of Records

Gordon wonders how he can import a subset of a text file into Excel, depending upon the value of a particular field. As an example, he may want to only import records that contain a "y" in column five of each record in the text file.

At that place are several means you can arroyo this task. One is that you could merely import the whole text file, sort the records, and delete those y'all don't desire. This is, perhaps, the simplest option if you only need to procedure a single file and the entire file can fit within a single worksheet.

Another approach is to use a macro. (This is the one that I find the fastest and easiest, particularly if yous need to import the same blazon of file quite a bit.) The macro can open the text file, read each line, and and then make a determination as to whether the information in that line should exist added to the worksheet or non. Here'south an example that will open a file named "MyCSVFile.txt" and and then stick the data into a new worksheet starting at the first row.

Sub ReadMyFile()     Dim R As Integer     Dim C Equally Integer     Dim sDelim As Cord     Dim sRaw As String     Dim ReadArray() As Cord      sDelim = ","     ' Set to vbTab if tab-delimited file      Worksheets.Add     Open up "myCSVFile.txt" For Input As #1     R = i     Practice While Not EOF(1)         Line Input #1, sRaw         ReadArray() = Split(sRaw, sDelim, 20, vbTextCompare)         If ReadArray(iv) = "y" Then             For C = 0 To UBound(ReadArray)                 Cells(R, C + one).Value = ReadArray(C)             Next C             R = R + i         Terminate If     Loop     Shut #1 Cease Sub        

To utilize the macro, simply change the proper name of the file to match the file y'all want to process. You'll also want to change the sDelim variable to brand sure information technology matches whatsoever is being used as a delimiter in your records. As written, it assumes the delimiter is a comma (which it would exist in a CSV file), but you could change information technology to vbTab if you are really working with a tab-delimted file. Later on the macro is consummate, only those records with a unmarried, lowercase "y" graphic symbol are in the new worksheet.

Another approach is to use the Power Query characteristic of Excel. This is a complimentary add-in, from Microsoft, that is available for some variations of Excel 2010 and Excel 2013. You can download (and find out which variations are supported) at this location:

http://world wide web.microsoft.com/en-united states/download/details.aspx?id=39379        

If you lot are using Excel 2016, then Power Query is built into the programme. If you accept Ability Query installed or available in your version of Excel and that version of Excel happens to be Excel 2010 or Excel 2013, then follow these steps:

1. Brandish the Power Query tab of the ribbon.
2. Click From File | From CSV. Excel displays the Comma-Separated Values Scan dialog box, which looks very much similar a standard Open dialog box.
3. Locate and select the CSV file you want to import into Excel.
4. Click Open. Excel loads the data in a Power Query window with filtering buttons available for each field.

If you are using Excel 2016 or a subsequently version, the steps are a scrap unlike:

1. Display the Data tab of the ribbon.
2. Click the New Query tool (Excel 2016) or the Get Data tool (later versions of Excel) in the Become & Transform group. Excel displays some options.
3. Click From File | From Text/CSV. Excel displays the Import Data dialog box, which looks very much like a standard Open dialog box.
4. Locate and select the CSV file yous want to import into Excel.
5. Click Open. Excel loads the data in a Power Query window with filtering buttons available for each field.

At this point—regardless of the version of Excel yous are using—y'all can use the controls to specify a query (significant, setting up a definition of which records should be imported). When you click Close and Load, the records are retrieved from the file, and the query can exist saved for futurity use.

A fourth approach is to utilise Microsoft Query. To practice and so, you'll demand to follow this very lengthly series of steps. (Nobody ever said that Microsoft wanted to make Microsoft Query easy to use, and you lot'll agree after you get through these steps.)

1. Brandish the Information tab of the ribbon.
2. Click the Get Information tool in the Go & Transform Data group, and then choose From Other Sources (in the Become External Data group before versions of Excel) and then choose From Microsoft Query. Excel displays the Cull Data Source dialog box. (Come across Figure ane.)



Figure ane. The Choose Information Source dialog box.

3. Select the New Information Source option and click OK. Excel displays the Create New Data Source dialog box. (See Figure 2.)



Figure 2. The Create New Data Source dialog box.

4. Provide a name for your data source, such as "CSV Files".
5. Using the drop-down list for item 2, choose Microsoft Text Driver.
6. Click Connect. Excel displays the ODBC Text Setup dialog box.
7. Immediately click OK to shut the dialog box.
8. Click OK to close the Create New Information Source dialog box. Excel updates the Choose Data Source dialog box to include the name you specified in step 4.
9. Select the information source you just created and and then click OK. Excel displays a warning that there are no data tables in the source. (That's OK; you lot oasis't defined any.)
10. Click OK to dismiss the warning. Excel displays the Query Sorcerer dialog box.
11. Since yous can't do anything with an empty Query Wizard dialog box, click Cancel. Excel displays a alert asking if you want to remain in Microsoft Query.
12. Click Yes. Excel displays the Add Table dialog box.
13. Using the controls in the dialog box, locate and select your CSV file.
14. Click the Add push button. Excel looks similar it doesn't exercise anything, only it actually added the reference to the CSV file.
15. Click the Shut button to dismiss the Add Table dialog box. Your CSV file is shown in the Microsoft Query window.
16. Using the list of fields for the CSV file, drag each field yous want imported into the worksheet into the bottom area of the Microsoft Query window. (If you desire all of the fields, only drag the asterisk into the bottom area of the window.)
17. Click Criteria | Add Criteria. Excel displays the Add Criteria dialog box. (Encounter Figure 3.)



Figure iii. The Add Criteria dialog box.

18. Using the controls in the dialog box, specify that you want field 5 (whatsoever its proper noun is) to be equal to "y."
19. Click the Add button to actually add the criteria to the query.
20. Click Close to dismiss the Add Criteria dialog box.
21. Click File | Render Information to Microsoft Excel. Excel displays the Import Data dialog box. (See Figure iv.)



Figure four. The Import Information dialog box.

22. Change the settings in the dialog box, equally desired, to indicate how you want the CSV data returned to Excel.
23. Click OK.

(Told you the steps were lengthly.) You can at present work with the data in Excel and, if desired, apply the tools on the Blueprint tab of the ribbon to refresh the data from the CSV file.

If y'all would like to know how to apply the macros described on this page (or on any other folio on the ExcelTips sites), I've prepared a special folio that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for toll-effective Microsoft Excel training. This tip (10384) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.

Author Bio

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Assembly, a figurer and publishing services company. Larn more most Allen...

MORE FROM ALLEN

Editing Wrap Points

If you have a graphic that has text wrapping around it, you might want a mode to modify the wrapping path used past Give-and-take. ...

Detect More than

Forcing Worksheets to Impress on a New Sheet

Excel allows you to easily print an unabridged workbook. Doing so, however, may non give the desired results if you a ...

Notice More

Adding a Drop Shadow to a Text Box

One manner to brand your text boxes "stand off" the page is to add together a drop shadow to them. This tip shows just how like shooting fish in a barrel it is ...

Observe More than

colonthatimensfa.blogspot.com

Source: https://excelribbon.tips.net/T010384_Importing_a_Subset_of_Records.html

Share this post