Are you familiar with flat files?
The term appears to have various meanings, but here it refers to data with fixed-length fields, which are often used in older systems. Since these files do not have fields separated by commas or similar delimiters, you need to manually specify how many characters each field contains either when importing the file or after it has been imported.
This time, I would like to try importing this flat file into Designer.
How to import a flat file
When importing a flat file in Designer, if the extension is TXT or similar, a dialog like the one below will appear.
Here, you need to select “Read it as a fixed-width text file,” as shown above. When you click OK, the next screen lets you specify where the field breaks are located.
If you click a position on the ruler with the mouse, that position is recognized as a delimiter and a red line is drawn. You can change the overall record length, but please note that you cannot change the length of each field using the keyboard.
Once you change the field names and data types to appropriate ones, the setup is complete. Click the OK button to import the file. Incidentally, because the fifth row contains data with two fields missing, a warning will appear unless you check the “Allow short lines” option.
As a result, we were able to confirm it successfully in the preview area.
When the character encoding is Japanese ShiftJIS
Not only Japanese, but also Chinese and Korean use 2-byte character encoding. What happens in those cases? This time, let’s try it using the Japanese ShiftJIS encoding, which is commonly used in Japan.
In this case, because the default character encoding is set to “ISO 8859-1 Latin 1,” garbled characters will naturally occur. Even before that, the display will be misaligned during import. Also, you can’t select “V_WString” as the data type.
But don’t worry. Although it looks misaligned, full-width Japanese characters are treated as two characters, and the data is properly split according to the string length. Let’s press OK in this state.
Judging from the preview, it appears to have been split appropriately.
Incidentally, Japanese Shift-JIS cannot be selected in the code page, so leave it as is. After that, place a Multi-Field Formula tool for character encoding conversion and configure it as follows.
Check the fields for which you want to perform character encoding conversion, check “Change Output Type,” and select “V_WString.” Set the size large enough to store the values. Then use the following expression as the formula.
ConvertFromCodePage([_CurrentField_], 932)Note that if you use the Formula tool, you cannot change the data type of existing fields, so using the Multi-Field Formula tool is easier.
As a result, the garbled text shown below
becomes as follows.
When the character encoding is UTF-8
Because UTF-8 uses variable-length encoding, files containing a mix of Japanese and English cannot be imported correctly. When importing a flat file, the number of characters is treated as one byte. Full-width characters may appear to take up the space of two characters visually, but internally they may actually contain data equivalent to three characters or more. In such cases, the result becomes misaligned. In fact, if Japanese text is mixed in, the total length itself cannot be measured correctly.
However, UTF-8 was not used in old systems because it did not exist at the time, so I think it is safe to assume that UTF-8 flat files basically do not exist—probably.
If you do have such data, you need to import it first and then handle it afterward. For example, you can use regular expressions as shown below.
In this case, both the first and last columns are fixed at four characters, so the fields could be split relatively easily. However, if the pattern does not work well, some ingenuity may be required.
If you need to strictly define, for example, that up to XX characters should be treated as field XX, proceed as follows.
- Assign a row ID using the Record ID tool
- Split the text into individual characters using the RegEx tool
- Treat full-width characters as 2 and narrow-width characters as 1
- Use the Multi-Row Formula tool to calculate the character position for each row
- Assign field names according to the character position
- Concatenate the strings using a Cross Tab and store them in the appropriate fields
In step 5 of this procedure, use a formula like the one below to assign an appropriate field name to each character based on its character position. This part depends on the file, so it is not generic.
IF [length]<=4 THEN "No"
ELSEIF [length]<=16 THEN "Name"
ELSE "UnitPrice" ENDIF
Please note that a macro is required to make this process generic.
Conclusion
- I’ve shown you how to read flat files (fixed-length field files).
- A little caution is needed if 2-byte characters are included (character encoding conversion).
- Since the character encoding is almost never UTF-8, please consider the UTF-8 part as an extra (however, the same method as for UTF-8 is required for EUC_JP).
Sample Workflow Download
The Next Post will be…
The next post will be “Read all tables with the same schema from the database at once.”
コメント