0

I have a .rpt file output from SSMS. I'm able to open it within a text editor or Excel, but format appears to be neither space, tab, or comma delimited, so resulting formatting in Excel isn't correct.

I've had similar issues loading to MySQL using the LOAD DATA INFILE call.

Namely, the load 'completes', but the majority of rows/columns are skipped due to apparent formatting discrepancies.

Input:

EffectiveDate           family Instrument  SedolCode    Name                                                         ICB         QZ                     VZ                     MZ                     SZ                     volZ                   LZ                     DYZ                    QS                     VS                     MS                     SS                     volS                   LS                     DYS                    price_o                fx_o                   shares                 cap_o_usd
----------------------- ------ ----------- ------------ ------------------------------------------------------------ ----------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ----------------------
1998-03-23 00:00:00.000 RU1000 1010846     2046789      Arco Chemical                                                1           0.332041753946526      3                      -0.701907649892818     0                      1.12824639032619       1.25544514744965       1.60431785387846       0.630071248806063      0.998650187313827      0.241368465365155      0.5                    0.870392090484651      0.895341500804304      0.945678041846726      48.56                  1                      16794000               815555357.27
1998-03-23 00:00:00.000 RU1000 1004415     2048804      Pinnacle West Capital Corp                                   7           0.197368136475134      0.295748219600254      0.0886833678439797     0                      1.05597173533583       -0.0327467636082814    0.75373010240488       0.578230241112579      0.616288903631446      0.535333094702153      0.5                    0.854509410778738      0.486938352167234      0.774494234589535      44.44                  1                      84718000               3764645032.54
1998-03-23 00:00:00.000 RU1000 1003705     2050832      Ameren Corp                                                  7           0.482269760349473      -0.0732144149931671    -0.623013812458784     0                      1.29932391788085       -0.218587562254685     1.72709462374024       0.685192934274198      0.470817875898523      0.266637746506796      0.5                    0.903083725664561      0.413485670414547      0.9579246320922        42.31                  1                      137215000              5805918920.82

Table format:

create table myTable(
    effectiveDate DATE NOT NULL,
    family CHAR(6) NOT NULL,
    instrument INT NOT NULL,
    sedol CHAR(8),
    name VARCHAR(100),
    icb INT,
    QZ DOUBLE,
    VZ DOUBLE,
    MZ DOUBLE,
    SZ DOUBLE,
    volZ DOUBLE,
    LZ DOUBLE,
    DYZ DOUBLE,
    QS DOUBLE,
    VS DOUBLE,
    MS DOUBLE,
    SS DOUBLE,
    volS DOUBLE,
    LS DOUBLE,
    DYS DOUBLE,
    priceO DOUBLE,
    fxO DOUBLE,
    capOUSD DOUBLE,
    PRIMARY KEY (effectiveDate, instrument));

Load call

load data infile '/ru/z1.rpt'
into table myTable
(effectiveDate, family, instrument, sedol, name, icb, qz, vz, mz, sz, volz, lz, dyz, qs, vs, ms, ss, vols, dys, priceO, fxO, @dummy, capOUSD);
Chris
  • 1,401
  • 4
  • 17
  • 28
  • 1
    *LOAD DATA **LOCAL** INFILE* is used when the source file is NOT local - this keyword causes src file copying to local drive before importing. *Has anyone successfully loaded a .rpt file to a MySQL DB/table.* Show this file content example (copy 3-4 lines as a text) and destination table structure (its CREATE TABLE script). In general there is no problem to import data from positional text file. – Akina Mar 02 '20 at 07:31
  • @Akina, thanks. Updated with details. Also, originally tried absent `LOCAL` addition originally with similar results. – Chris Mar 02 '20 at 08:12

2 Answers2

1

You must load the whole data line then parse it with proper data type convertion:

LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\z1.rpt'
INTO TABLE myTable
IGNORE 2 ROWS /* skip header */
(@tmp) /* load whole line into vartiable */
SET  /* then parse separate field values from the variable */
EffectiveDate = SUBSTRING(@tmp,1,23), /* datetime literal - get as-is */
family        = SUBSTRING(@tmp,25,6), /* string data - get needed length */
Instrument    = 0+SUBSTRING(@tmp,32,11), /* numeric data - convert from string implicitly */
Sedol         = SUBSTRING(@tmp,44,8),
Name          = SUBSTRING(@tmp,57,60),
ICB           = 0+SUBSTRING(@tmp,118,12),
QZ            = 0+SUBSTRING(@tmp,130,22),
VZ            = 0+SUBSTRING(@tmp,153,22),
MZ            = 0+SUBSTRING(@tmp,176,22),
SZ            = 0+SUBSTRING(@tmp,199,22),
volZ          = 0+SUBSTRING(@tmp,222,22),
LZ            = 0+SUBSTRING(@tmp,245,22),
DYZ           = 0+SUBSTRING(@tmp,268,22),
QS            = 0+SUBSTRING(@tmp,291,22),
VS            = 0+SUBSTRING(@tmp,314,22),
MS            = 0+SUBSTRING(@tmp,337,22),
SS            = 0+SUBSTRING(@tmp,360,22),
volS          = 0+SUBSTRING(@tmp,383,22),
LS            = 0+SUBSTRING(@tmp,406,22),
DYS           = 0+SUBSTRING(@tmp,429,22),
priceO        = 0+SUBSTRING(@tmp,452,22),
fxO           = 0+SUBSTRING(@tmp,475,22),
/* shares=0+SUBSTRING(@tmp,498,22), */ /* field is absent in table structure - skipped */
capOUSD       = 0+SUBSTRING(@tmp,521,22)
;

  1. Source data contains shares field which is absent in table structure (commented);

  2. name is defined as VARCHAR(100) in table structure whereas its value has only 60 symbols in data file.

  3. Each file may have its own fields lengths - so check this before importing (and correct if needed).

Akina
  • 39,301
  • 5
  • 14
  • 25
0

If you're on unix/linux then you can put it through sed to strip out spaces. The solution is here

I found a solution for myself using PHP:

<?php

$mysqli  =  new mysqli(
"***",
"***",
"***",
"***",
3306
);
mysqli_options($mysqli, MYSQLI_OPT_LOCAL_INFILE, true);

if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

function createTempFileWithDelimiter($filename, $path){
    $content = file_get_contents($filename);
    $replaceContent = preg_replace('/\ +/', ',', $content); 
    
    $onlyFileName = explode('\\',$filename);

    $newFileName = $path.end($onlyFileName);
    file_put_contents($newFileName, $replaceContent);
    
    return $newFileName;
}

$pathTemp = 'C:\\Temp\\';

$pathToFile = 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\z1.rpt';

$file = createFileWithDelimiter($pathToFile, $pathTemp);
$file = str_replace(DIRECTORY_SEPARATOR, '/', $file);

$sql = "LOAD DATA INFILE '".$file."' INTO TABLE `myTable` 
    COLUMNS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    IGNORE 2 LINES
    (effectiveDate, family, instrument, sedol, name, icb, qz, vz, mz, sz, volz, lz, dyz, qs, vs, ms, ss, vols, dys, priceO, fxO, @dummy, capOUSD);";

if (!($stmt = $mysqli->query($sql))) {
    echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
};

unlink($file);
?>

Don't use '/\s+/' in preg_replace because \s matches any whitespace character (equivalent to [\r\n\t\f\v ]) and the formatting will change, columns and line breaks will disappear.

devalurum
  • 58
  • 7