logo
small logo
  • Products
  • Buy
  • Support
  • Articles
  • Forgot password?
    • en
    • ru
    • pt
    • es
    • JP
    • ZH
  • Home
  • /
  • Articles
  • /
  • How to make a csv file conversion: remove columns, sort, filter
  • How to make wedding invitations from Excel

    August 25, 2017

    Do you have a celebration on the occasion of the wedding? Do you need to

    read more
  • How to build daily graphics from csv files

    August 24, 2017

    Suppose you keep a record of employee sales in an Excel file. Would you like

    read more
  • How to print envelopes from the address list

    August 24, 2017

    As a follow - up to the previous article about wedding invitations, I would like

    read more
  • How to make a PDF document from a text file

    August 25, 2017

    Today we will talk about the cases when you need to make a PDF document,

    read more
  • How to merge several csv files into one

    August 29, 2017

    This article aims to provide the insight into how to use FastReport.Net to merge two

    read more

How to make a csv file conversion: remove columns, sort, filter

August 24, 2017

MS Excel spreadsheet editor handles editing CSV files perfectly. You can sort and filter data, remove unnecessary columns, add numbering etc. But what if you do not have a spreadsheet editor? Editing a CSV file becomes a nightmare. One has to track the delimiters to understand what column this or that data refers to. Process of sorting in general becomes almost impossible, especially when working with large amount of data.

How can this situation be solved? We have FastReport.Net or FastReport Desktop, so we can use CSV as a data source for our report. First, inside the report, perform data conversions. Then, export back to the CSV file.

Let us overview a small CSV file with a list of names and addresses:

Determine the transformations that we want to make:

1. Remove the PostalCode column;

2. Remove from the address the name of the city in a separate field;

3. Sort records by name;

4. Remove the surname Robert King from the data set.

Create a new report. Add a new CSV data source file.

On the report page, place the FIO and Address fields, where add the Address field twice. The first of the added Address fields is edited the following way: [Substring ([Addresses .Address], 0.6)]. Consequently, we single Moscow out of the rest of the addresses. If the names of the cities were different, we would need more complex processing. To do this, you need to use the report script. For the text object, added to the band, you need to create the BeforePrint () event. To the script we add the usage of the library: using System.Text.RegularExpressions;

Here is the event handler code:

1
2
3
4
5
6
7
8
9
private void Text5_BeforePrint(object sender, EventArgs e)
 {
 Regex rgx = new Regex("([^,]|\n)+", RegexOptions.IgnoreCase);
 Match match = rgx.Match(Report.GetColumnValue("Адреса.Address").ToString());
 if (match.Success)
 {
 Text5.Text = match.Value.ToString();
 }
 }

 As it is seen, we used a regular expression to select the data before the comma.

Now let us get back to the second Address field, which has been added. Since we divided the name of the city into a separate column, the rest of the addresses must remain here. Let us use the script for the Text3 object.:

1
2
3
4
5
6
7
8
9
private void Text3_BeforePrint(object sender, EventArgs e)
 {
 Regex rgx = new Regex(@"(?<=,\s).*", RegexOptions.IgnoreCase);
 Match match = rgx.Match(Report.GetColumnValue("Addresses.Address").ToString());
 if (match.Success)
 {
 Text3.Text = match.Value.ToString();
 }
 }

Now we sort the data according to names. To do this, double - click the "Data" band. Select the "Sort" tab. We have three fields for sorting. Specify only the first value - Name:

The only we need is to filter the data by name Robert King. To do this, again double - click the Data band. In the window that appears, select the "Filter" tab and enter the expression

[Addresses.Name]!="Robert King"

 Run the report in preview mode:

So, we removed the ZIP column, sorted the list by name, divided the address into a city and street, removed the line for the name Robert King. Now let us export to CSV. For this, in the preview mode press the button: .

Choose "CSV format ...". In the dialog box, click "Ok" and select the location, where the file will be saved. In the end, we get the following CSV file:

 

As you can see, it was accessible to convert a document using FastReport. It is commensurate with work in Excel in time. Thus, FastReport becomes an alternative tool for processing CSV files.

about product download buy
avatar
Dmitriy Fedyashov
Head of QA
.NET FastReport Desktop CSV

Add comment
logo
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314
  • Buy
  • Download
  • Documentation
  • Testimonials
  • How to uninstall
  • Ticket system
  • FAQ
  • Tutorial Video
  • Forum
  • Articles
  • Our News
  • Press about us
  • Resellers
  • Our team
  • Contact us

© 1998-2021 by Fast Reports Inc.

  • Privacy Policy