HMI log file encoding doubles size and blocks the SQL import

The file is wide-character text

In Notepad, WordPad and Excel the log looks perfect, because those programs decode wide text for display. Open the same file in DOS EDIT, which shows the bytes with less interpretation, and the first record reads:

0 0 : 0 0 : 5 9 0 5 - 1 9 - 2 0 1 0 0 . 0 3 5 0 0

A space after every character is the signature of two bytes per character: the visible character in one byte, a second byte with no printable symbol beside it. That is UTF-16 family text, which stores each character in one or two 16-bit code units, so even a plain digit costs two bytes on disk. A parser set for single-byte text takes that second byte as data, the row terminator it hunts for never matches, and the import stops at the end of the file with the EOF termination error. The size follows the same root: 660 KB as logged, 330 KB once the same text is written one byte per character.

What each symptom points to

The same trap shows up on other paths. A log that looks wrong through one route and right through another is a representation question, the way a data log that looks mixed over FTP reads fine once it is saved locally.

The check that settles it

1. Copy the log off the panel and keep the original untouched. If it lives on a USB stick, remove the stick with an eject button instead of pulling it mid-write.
2. Open the copy in a viewer that shows bytes. DOS EDIT is the one that made this visible; any hex viewer does the same job.
3. Read one record and count the gaps between characters.
4. Paste the whole file into a new text file and compare the two sizes.

Divide the logged size by the pasted size. Near 2.0 means wide text. Near 1.0 with the same error means the row terminator or the field layout, and that is the only case where delimiter work pays.

Read the file wide on the import side

The direct fix is one switch in the bcp command: the wide-character option, the mode that tells the bulk copy the text is Unicode. Set it and the logged file loads with no conversion step between the panel and the database, which is the point of automating the import. The import-export toolset has Unicode formats of its own; the native Unicode format is documented for copying data between server instances, so it is the wrong mode for a text log, and the wide-character mode on the character side is the right one.

If you drive the import with a format file, each field carries a host-file data type, and that type has to match the bytes on disk. A format file written for single-byte data specifies the narrow character type for every field; a two-byte log needs the wide counterpart for every field, terminator included. Your import tool's own documentation prints the exact type name to use.

The other pipeline is a conversion step that decodes the wide file and writes single-byte text ahead of the import. It works, the paste proved it, and it costs an extra step plus a chance for a rewrite to drop trailing zeros. Take the switch when the import tool offers one.

Worked example: the 660 KB log and its 27-character first record

Inputs:

• First record as the log writes it, read back from the pasted copy: 00:00:59 05-19-2010 0.03500.
• Character count of that record: 8 in the time, 1 space, 10 in the date, 1 space, 7 in the value, so 27 characters.
• File sizes from the same case: 660 KB as logged, 330 KB after the paste.
• Encoding basis: UTF-16 stores each character in one or two 16-bit code units, so an ASCII character takes 2 bytes.

Rule in words: the bytes in a record equal its characters times the bytes per character, and the whole-file ratio is the logged size divided by the single-byte size.

Arithmetic:

• Single-byte record: 27 characters x 1 byte = 27 bytes, plus one line terminator.
• Wide record: 27 characters x 2 bytes = 54 bytes, plus a terminator of the same width.
• Whole file: 660 KB / 330 KB = 2.0, which is two bytes per character from the first record to the last.

Result: the log is wide-character text end to end, and an import set for single-byte text fails on it at any record count, which is why the single-record test changed nothing.

Decision: a ratio at 2.0 says encoding. Set the import to wide-character mode and leave the tabs alone. A ratio near 1.0 with the same error says terminator or layout, and only then is it worth changing how the log is delimited.

The comma-after-tag setup, and where it stops

The panel's logging configuration can append a comma to each tag name, so fields land comma-separated. Give the first tag a leading comma too, because the log prepends a date and time stamp to every record. One tab always survives between the stamp and the first tag, so the file never becomes a clean CSV. Use the setup when you want a layout the parser can predict, and if you want named columns to go with it, add column headers to the trend log file and point the parser at them. The commas change field separators and nothing about the bytes per character, so they clear no EOF error on a wide file.

Verify the load before you automate it

Check three things on the first clean import:

• Row count: records in the file against rows in the table.
• First and last record, not a middle one: 00:00:59, 05-19-2010 and 0.03500 must land in three separate fields, with the five decimal places of the value intact.
• Timestamps whole in one field, and values with their trailing zeros.

Then automate the path you chose: the import with the wide switch, or the scripted conversion plus import where a tool in the chain has no wide mode. Run the automated path once on a fresh log before the next one fills the stick.

Recommendation: Keep the panel's tab-delimited log exactly as configured, and set the bcp import to wide-character mode whenever the logged file is twice the size of its pasted copy. Add a scripted single-byte conversion only when a tool in the chain has no wide mode. Use the comma-after-tag setup for field layout alone, and treat the EOF termination error as an encoding fault until the size ratio says otherwise.

Questions