Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Import a fixed width file

cghera's profile image cghera posted 6 months ago in Import/Export Permalink

I need to import a fixed width file (not comma separated). Is there a way to do that in HiediSQL. Because currently I am using MS Access to import it and create columns and then append it on th MYSQL DB. I tried the import TEXT BLOB choice but there is no option to define width of each column and match it with a field of a table.

ansgar's profile image ansgar posted 6 months ago Permalink

Fixed width columns without separator are quite problamatic. You should be able to leave the field separator and encloser empty in HeidiSQL's import dialog. But you should read what then happens, in the docs:

If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (nondelimited) format is used. With fixed-row format, no delimiters are used between fields (but you can still have a line terminator). Instead, column values are read and written using a field width wide enough to hold all values in the field. For TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT, the field widths are 4, 6, 8, 11, and 20, respectively, no matter what the declared display width is.

LINES TERMINATED BY is still used to separate lines. If a line does not contain all fields, the rest of the columns are set to their default values. If you do not have a line terminator, you should set this to ''. In this case, the text file must contain all fields for each row.

Fixed-row format also affects handling of NULL values, as described later. Note

Fixed-size format does not work if you are using a multibyte character set.

Please login to leave a reply, or register at first.