LOAD DATA INFILE (fixed width file) Help Needed!

MySQL database
#1

Hello all,

I am writing to ask what is the best way to write the sql statement to upload a fixed width file?

like
0123456789Company name Boston MA02215-1234

first field starts on 0 and has 9 char
next filed starts on 9 and has 22 char
next field starts on 30 and has 10 char
and etc etc

the numbers that I am using may be wrong as I am just creating a example of the file which is much bigger

Thanks for the help!

Sincerely,
Christopher

#2

If your fields are all the same width you can easily use substr() in a PHP script to chunk the file up and INSERT those chunks into the database, in pseudocode:

while (newline of file) {
  $new_id = substr(currentline, 0, 9)
  $new_name = substr(currentline, 9, 22)
  [etc]
  $sql = "INSERT INTO db_table VALUES ($new_id, '$new_name', [etc])"
  mysql_query($sql)
}
#3

I am trying to use LOAD DATA INFILE as this is a better way of doing it its just giving trouble

Any ideas of getting LOAD DATA LOCAL INFILE to work?

Sincerely,
Christopher

#4

I don’t know if you checked the MySQL site, but it appears that is a pretty common problem.

If you scroll down to the bottom of this page, http://dev.mysql.com/doc/refman/6.0/en/load-data.html, it appears the people in the comments have found a workaround and what causes the problem.

It sounds like the key is to use the proper character format for your table, and that appears to be explained on the page by this paragraph…

If the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values are both empty (''), a fixed-row (non-delimited) 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 that fixed-size format does not work if you are using a multi-byte character set.

So the key seems to be not using a multi-byte character set. Hope that helps.

#5

I don’t understand this,

what char set should I use?, should I then also change the set in the database table settings?

should I add a char set thing to my LOAD DATA LOCAL INFILE statement

Thanks for the help!
Christopher

#6

Well, I haven’t tried the fix, but (and again I’m taking this from the comments mentioned previously) it seems like putting a character set in the LOAD DATA statement doesn’t do it, you need to change the character set of the table, it sounds like either latin1_general_c, or binary may work.

#7

Thanks will try later as I am working on another part of the project now :-)

Sincerely,
christopher

Sponsor our Newsletter | Privacy Policy | Terms of Service