Listado de la etiqueta: CSV


Files and folders are the necessary parts of any operating system. But, when you need to work on databases, the CSV files come in handy. The CSV files are also another name for Microsoft Excel documents used to store data in the form of rows and columns. The Excel or CSV files can be used to import data within the tables of a database. Therefore, we have decided to cover the method of importing a CSV file containing data in columns to the SQLite database table. Make sure to have SQLite C-library of SQL installed in your Ubuntu 20.04.

Let’s get started with today’s article by launching the console application within Ubuntu 20.04 with the Ctrl+Alt+T shortcut. After the successful launch of the console application, you need to update your Linux system with the help of an apt package preceded by the “sudo” keyword. It will ask for your currently working user account password to resume the updating process through this query. We have added the password, pressed the Enter key, and continued.

After a while, your system will be up to date. After this, you need to upgrade your system and its packages, i.e., modules and libraries. Try out the same instruction with the keyword “upgrade” instead of the word “update”.

Make sure to have some CSV files within your Linux’s home folder to use within the SQLite database, i.e., import its data to a table. Thus, we have created a new CSV file named “data.csv” within the home folder to be used in the table by importing. Let’s start the SQLite database on the shell terminal of the Ubuntu 20.04 system using the keyword “sqlite3”. The query area of an SQLite will be launched as shown below:

We have been starting this guide using the “.tables” instruction in the database to display the list of tables. Right now, we don’t have any table in this database.

 

To import CSV files, we need a table in the database. Thus, we have to create a table with the same column names and types as we have in the CSV file. So, we have been creating a new table named DATA with the CREATE TABLE instruction containing two columns, i.e., “Country” of TEXT type and column “People” of integer type. This newly created table has no records yet.

CREATE TABLE DATA(«Country» TEXT, «People» INT);

SELECT * FROM DATA;

Let’s import the CSV file named “data.csv” within the DATA table using the “.import” instruction followed by the path to the file and the table’s name, as presented below:

.import /home/linux/DATA.csv DATA

After the successful query execution, we should confirm whether the import was proper and successful. For this, you need to try out the SELECT instruction for the table DATA merienda again on your SQLite shell. The output of this command shows that the import is successful. All the records from the data.csv file have been imported into the DATA table properly.

Let’s try out the condition within the SELECT query. We have added the WHERE clause to display all the records from the imported data of a table “DATA”, where the column “People” contains the values less than or equal to 550000. It returned a total of eight records.

SELECT * FROM DATA WHERE People >= 550000;

Let’s try a SELECT query with the WHERE clause in the SQLite database to display all the records of a table “DATA” where the column “People” have a value between 550000 and 750000. For this, we have been using the BETWEEN keyword and have only three results.

SELECT * FROM DATA WHERE People BETWEEN 550000 AND 750000;

Let’s look at another example of importing the same file with a different set of records. This time, the data set contains 15 rows with the three columns, i.e., Name, Age, and Salary. The column Name and Salary are Integer types, while the column “Name” is text type.

For this data, we have been creating a new table named “Detail” containing the same name in three columns as the data.csv file, i.e., Name, Age, and Salary. Before importing the data.csv file to the Detail table, we haven’t found any record within it per the SELECT instruction executed at the SQLite shell screen, i.e., empty table.

CREATE TABLE Detail(Name TEXT, Age INT, Salary INT);

SELECT * FROM Detail;

To import the data of the data.csv file within the Detail table, we need to try out the “.import” instruction followed by the path to the “data.csv” file, i.e., /home/Linux/, and the name of a table “Detail” at the end of this query. This query will return nothing showing that the import has been successful. So, we will be using the SELECT instruction merienda again to confirm that the import was made perfectly and efficiently. The SELECT instruction with the “*” character followed by the name of a table “Detail” displayed all the records of the data.csv file just imported to the table. This means the import was successful.

.import /home/linux/DATA.csv Detail

Let’s apply the WHERE clause on the imported CSV column “Salary” of the table Detail to display only the records where the value of the Salary column is less than 35000. This query with the WHERE condition returns two records as a result.

SELECT * FROM Detail WHERE Age < 20;

Let’s use the WHERE clause within the SELECT query to display the records of a table “Detail” where the imported data has values between 35 and 47 in the “Age” column. This time, we have five records, as presented below.

SELECT * FROM Detail WHERE Age BETWEEN  35 AND 47;

Conclusion

This article used CSV files to import data records to the SQLite database table using the “import” instruction at the shell. You need to ensure that the CSV column names and the table column names must be the same, i.e., uppercase or lowercase. We also discussed the ways to display the imported data in tables using some conditions.



Source link


When a program is dismissed, all data connected with it is deleted. As a result, we’ll need to save the data somewhere. Data is saved and shared indefinitely using files. Data contained in text files can be retrieved and modified using C#. In this article, we will read the data from a CSV file format created in Ubuntu text editor in c# programming language. For this, we have to know what is CSV file in C#.

What is a CSV File in C# in Ubuntu 20.04?

CSV (Comma Separated Values) is a frequently used data import and export layout for spreadsheets and databases. A data record is signified by each line in a CSV file. While CSV is a pretty simple file format, there can be a lot of variations, such as various delimiters, new lines, and quotation characters.

Structure of a CSV File in C# in Ubuntu 20.04

The arrangement of a CSV file is relatively basic. It’s a comma-delimited list of information. They can be much more complex, with thousands of lines, many entries per line, and long strings of text. Nearly CSV files don’t have headers at all, while more or less they utilize quote marks to enfold each chunk of data, but that’s the simple pattern.

The simplicity is an asset. CSV files are created to make data export and import simple. The generated data is easily understandable by humans and may be seen in a text editor.

Benefits of CSV File in C# in Ubuntu 20.04

How to Read the Content From a CSV File in the C# in Ubuntu 20.04?

In C#, the file class includes two static methods for reading CSV files, ReadAllText() and ReadAllLines().The ReadAllText() function returns a string encompassing the entire file’s text. This string must be saved in a variable and then used to display the data on the screen. On the other hand, ReadAllText()reads each line at a time from a file and returns it as a string. To store each line, we’ll need a string array. The same string array is used to present the data file. We have two programs of read CSV file in C#, and one of the programs read and write the CSV file content simultaneously.

Example # 1: Program to Read the CSV File Content in C# in Ubuntu 20.04

To deal with files in C#, the StreamReader class is utilized. It opens, reads, and assists with a variety of file functions. In addition, we can use this class to perform various actions on a CSV file. The CSV file is created separately as a “file.csv.” The CSV extension is used to create this file. After that, we have given a file path in the program.

We have C# library files at the top section of the program. The first file is a system.io namespace containing classes, structures, delegates, and enumerations related to IO. We can use these classes to read and write data to and from files and data streams. There are also classes for working with files and directories. The second one is the System.Collections.Generics defines interfaces and classes that describe generic collections, allowing users to design statically typed collections that are more type-safe and performant than non-generic statically typed collections. And the last library system library is used in our program, as indicated by the using System statement, which includes classes and functions such as the Console class and the WriteLine function/method.

After this, we established a class with the name “program,” and within the class, we have the main public function. Now, in the block of programs main, we have given a file path in a variable “filepath” of type string. The StreamReader class object is created as “Reader” and initialized with the null field. The if condition will verify the given CSV file path exists by using the Exists clause and reading the content of the file by defining the StreamReader.The OpenRead opens the file, and ReadLine is utilized to read the file content. Then, within the while loop, the EndOfStream property is called, which reads the entire stream from the current place to the end. It produces a string containing the remainder of the stream from the current point to the end; It produces an empty string if the current place in the stream is at the end. Note that whenever the compiler reads the coma in our CSV file, whenever the compiler reads the coma, it will split the line there. As we have called, the split function with the comma passed as a parameter inside the variable “values.”

To run this program, we have saved it as a “read.cs” file extension and then run it with the prompt shell “csc read.cs”. Then, using the command “chimpancé read.exe,” the content of the file will be read in the prompt shell of Ubuntu as follows.

Example # 2: Program to Read and Write the CSV File Content Simultaneously in C# in Ubuntu 20.04

We can write the data in the CSV file, and at the same time, we can read the data in the CSV file. In the preceding example, we have read the file data with the read methods. Now, with the write method, we are going to write data in the CSV file.

First of all, we have defined the class as “Program” with the main function declaration. Then, we have stored the file path within the variable “file.” The data that needed to be written in the CSV format was placed in the variable “content.” The WriteAllText function is used to write the entire data all at merienda. It accepts two arguments: the file’s path and the text to be written. The console writeLine expression will display the current data of the CSV file. To write the data within the variable “ContentLines1” line by line, we have a WriteAllLines function. It comprises two arguments: the file’s path and the text to be written (a string array). Then, we have another approach, StreamWriter, which is used to write a single line of data of “ContentLines2” in the CSV file.

The data we wrote in the above source code and then read that data of the CSV file is as follows.

Conclusion

The discussion about reading the CSV files in C# has come to an end. We have a detailed discussion about CSV file, their structure, and their benefits in efectivo applications. Then, we have two illustrations where we have implemented a code for reading the CSV files. Although, we have a program in which the read and write operation to the CSV file is possible in C#.



Source link