[Translation] Read text files (txt, CSV, log, tab, fixed length) (below)

Other file formats

We have discussed how to read comma-separated files (CSV. So how can we process tab-Separated Files and fixed-length files?

It mainly lies in the connection string we provide. Let's take a look at the details of the connection string.

Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ FolderName;
Extended Properties = "text; HDR = YES; FMT = Delimited

Provider-indicates the database type. We use the OLEDB connection type.

The Data Source-folder is treated as a database.

Extended Properties-defines a set of attributes for reading files.

The first part defines the file type. In our case, although the file formats are classified as comma-separated files, Tab files, or fixed-length files, they are all text files. To read an Excel file, use Excel x. x and x. x as the version number.

HDR (Header)-used to specify whether the Header is available. YES-the first line of the input file is treated as the header and other rows are treated as data. NO-the first row is treated as data.

FMT (Format)-specifies the Format type. You can have the following values:

Delimited	Files are separated by commas. Comma is the default delimiter.
Delimited (x)	The file is treated as 'X' as the separator.
TabDelimited	Files separated by tabs
FixedLength	Read data by specifying the fixed length of the field. You must use Attribute Sets (such as Col1 and Col2) to specify the column width and type. More MSDN

 

If the format type is set to 'delimited', the default character is comma (,). This is stored in the registry. You are in the RegistryHKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ jet \ 4.0 \ engines \ Text \ formatModify.

As people say, do not mess up the registry. Microsoft provides an alternative-by using the Schema. ini file. To read a FixedLength file, you must use the Schema. ini file. We specify the length of the field in the schema file.

 

Read from multiple files

If you have multiple files and data that need to be merged or filtered based on the Common columns, we use the same method as database operations. We can join a table to obtain the merged data. Remember that the output result will be two file columns for cross join. Make sure that you filter data based on the Common columns.

Example:

Examples:

Samplefile1.csv-(EmpID,Name,Address)
SampleFile2.CSV-(EmpID,Salary,Month)

// Where clause is used to get 'natural join 'string myselectquery = "select * From samplefile. CSV as sample1, _ samplefile2.csv as sample2 "+ _" where sample1.number = sample2.number "; 'where clause is used to get 'natural join' objrecordset. open "select * From samplefile. CSV as sample1, _ samplefile2.csv as sample2 "& _" where sample1.number = sample2.number ", _ objconnection, adopenstatic, adlockoptimistic, adshorttext

Note that aliases are required in join queries, especially for columns with the same name in the table. In our case, the table name (file name) is a bit (.). Therefore, the query analyzer is misled by the points in the table and may use 'csv. number' as the column name.