This is the first part of our discussion on how to export data to Excel. We will start with the simplest one; how to transfer data from a flat file (CSV, Tab delimited, PSV, etc.) to Excel. Let's know first what is a flat file...
What is a flat file?
Wikipedia described it best...
A flat file is a file that contains records, and in which each record is specified in a single line. Fields from each record may simply have a fixed width with padding, or may be delimited by whitespace, tabs, commas (CSV) or other characters. Extra formatting may be needed to avoid delimiter collision. There are no structural relationships. The data are "flat" as in a sheet of paper, in contrast to more complex models such as a relational database.In short it's an ordinary file that stores records in sequential order.
This simple pattern of records saved in a file makes it possible for Excel to extract them and load into worksheet cells. A function is available in Excel to do it with a wizard tool.
Export To Excel
1.) Before we can export records to Excel, we need to have source file. So, let's create one by opening Windows Notepad and enter the set of data exactly as shown in the screen grab below:
The sample records I created are list of customers. Each field should be separated by a Pipe ("|") character. After entering the string above, save the file. In my case, I named it Customer. The filename would have a .TXT extension, but since our data is pipe delimited, we should rename the extension to .PSV (Pipe Separated Value). We have now Customer.PSV.
2.) Create a new Excel worksheet. On the menu, click Data>Import External Data>Import Data as shown below:
3.) This will bring out the Select Data Source window:
In this window, you will see many choices in the "Files of type" pull down list but you need only to choose All files (*.*) because PSV format is not included in the list. Locate the Customer.PSV then double click it.
4.) The Text Import Wizard appears as shown below. Do not change the default Delimited in the Original data type as our records are delimited by a pipe character. Click Next button.
5.) The second screen of the wizard appears. Check the Other check box and put a pipe in the text field next to it. See below how. Then click Next button.
6.) The third screen of the wizard appears (see below). Here you usually indicate the data type of each column. But in the case of our sample records, we will just click Finish button.
7.) The Import Data window pops out, choose Existing worksheet since we juist created a new one and click Ok to store all the records in the worksheet as shown in the screen grab below:
We are done.
Now you know how to export records from a text file into Excel and having these records in the worksheet you can now manipulate the data the way you want. You can pivot them to summarize and come up with useful information, or you can consolidate them with other data extracted from other source, etc.
In my next post, we will discuss consolidation of records from different files using VBA programming.
Happy Weekend :)
Thinking of designing your own website?
Try www.esolz.net , a professional company which I came across while developing my website. Although I was skeptical about outsourcing my project to an offshore company, my mind was put at rest upon reviewing their proposal. It was clear that www.esolz.net is a team made up of solid web designers who provide quality, value, and support. From day one they consulted with me and involved me in every aspect of the site development.
They completed the project within the agreed 30 days and have exercised extreme diligence in maintaining the website since then.
Check out my website at www.ventureden.com
Ventureden is a global meeting place where entrepreneurs connect with angel investors and venture capitalists in order to secure startup or expansion funding for their business venture.
Entrepreneurs post their business idea or invention and instantly gain worldwide exposure.
Angel investors and venture capitalists leisurely explore investment opportunities, all from the comforts of office or home.
No matter if you?re an entrepreneur or investor, thanks to www.esolz.net our system is simple to use. Make www.esolz.net your outsourcing partner, you won?t regret it.
Regards,
Dan
www.ventureden.com
www.esolz.net
Name: Daniel Cimera
Address: 2116 Lynchmere ave.
Mississauga
Ontario
Canada
L5B 1W8
Phone: 416-741-6204
Email: salesinfo@ventureden.com
Thinking of designing your own website?
Try www.esolz.net , a professional company which I came across while developing my website. Although I was skeptical about outsourcing my project to an offshore company, my mind was put at rest upon reviewing their proposal. It was clear that www.esolz.net is a team made up of solid web designers who provide quality, value, and support. From day one they consulted with me and involved me in every aspect of the site development.
They completed the project within the agreed 30 days and have exercised extreme diligence in maintaining the website since then.
Check out my website at www.ventureden.com
Ventureden is a global meeting place where entrepreneurs connect with angel investors and venture capitalists in order to secure startup or expansion funding for their business venture.
Entrepreneurs post their business idea or invention and instantly gain worldwide exposure.
Angel investors and venture capitalists leisurely explore investment opportunities, all from the comforts of office or home.
No matter if you?re an entrepreneur or investor, thanks to www.esolz.net our system is simple to use. Make www.esolz.net your outsourcing partner, you won?t regret it.
Regards,
Dan
www.ventureden.com
www.esolz.net
Name: Daniel Cimera
Address: 2116 Lynchmere ave.
Mississauga
Ontario
Canada
L5B 1W8
Phone: 416-741-6204
Email: salesinfo@ventureden.com