Importing Text Data

Before we begin writing our own code to import data, let's record one more macro to see some of the settings available when we bring in data from a text file.

1. Create a new workbook and name it DataAccessSample02.xlsm.

2. Create a new macro and name it GetTextData.

3. On the Data ribbon, choose From Text.

4. Navigate to the myfilepath\maillist.csv file, and then choose the Import command. The Text Import wizard will open, as shown in Figure 2-3.

Figure 2-3. The Text Import wizard

The file is comma-delimited (the default selection in the Original Data Type section), so just click Next.

On Step 2 of the Text Import wizard, the default delimiter is Tab. The "Data preview" section should show us our columns separated by vertical lines. Since our file is not tab-delimited, the preview shows our raw data file (see Figure 2-4).

Figure 2-4. View of maillist.csv with Tab selected as delimiter

Select Comma from the Delimiters options. The data preview now shows your data in the correct columnar display (see Figure 2-5).

Figure 2-5. View of maillist.csv with Comma selected as delimiter

Click Next to continue to Step 3 of the wizard (see Figure 2-6), where we can choose the data type for each column of data.

Text Import Wizard - Step 3 of 3

This screen lets you select each column and set the Data Format. Column data format 0 General

Olext

O Do not import column (skip)

'General' converts numeric values to numbers, date values to dates, and all remaining values to text.

Data preview

smailaddreaa [email protected] 3 [email protected] [email protected] j [email protected] £h. com

phone

555-435—8799 555-435—S797 555-939-7878 555-456-7894

Figure 2-6. Step 3 lets you choose data types for each column.

The onscreen prompt tells us that the General format will convert numeric values to numbers, date values to dates, and so on. We will choose each column in turn, and choose the Text data type for our data. The Phone Number column contains numbers, but we want Excel to treat them as text. The column heading in the "Data preview" window shows us the data type selected for each column.

Click Finish after applying the Text data type to all columns (see Figure 2-7).

Click OK to let Excel place the data beginning in cell A1 (see Figure 2-8).

Click cell A1, and then stop the Macro Recorder. Figure 2-9 shows the data after it has been imported from the CSV file.

On the Developer ribbon, click the Visual Basic command or press Alt+F11 to open the Visual Basic window. Let's take a look at the code Excel generated for us. We'll examine the differences between importing Access data and text data in the Macro Recorder.

Text Import Wizard - Step 3 of 3

This screen lets you select each column and set the Data Format. Column data format O General

©jTextj

O Date: ImDY

O Do not import column (skip)

'General' converts numeric values to numbers, date values to dates, and all remaining values to text.

Data preview

lent

B

5 rr.a.il address j [email protected] .net 3 ami [email protected] t [email protected] j iiEisyd® £h. com

?hone

555-435—8799 555-435-3797 555-939-7373 555-45€-7394

Figure 2-7. "Data preview" window after applying the Text data type to all columns

Import Data

\im

Where do you want to put the data? 0 Existing worksheet:

O New worksheet

N

Properties... 11 OK

Cancel

Figure 2-8. Entering a location for the data

Figure 2-9. Data imported from maillist.csv
0 0

Post a comment