CODEWITHSUNDEEP
mysqlsql
Alexander
Alexander

Reputation: 149

Mysql - Data without spaces

I want to import the follow data (without separators between fields):

20232045001100000700002769T011998-01-22 0001-01-01 0001-01-01  0      1998-01-22-13.48.03.363642
20168752001100000700037166T011997-10-21 0001-01-01 0001-01-01  0      1997-10-21-19.00.50.529609
20195936001100000700793006T011997-11-18 0001-01-01 0001-01-01  0      1997-11-18-17.59.30.518192

and this is the structure.

CREATE TABLE `tabla008` (
  `numclien` varchar(8) NOT NULL DEFAULT '',
  `centidad` varchar(4) NOT NULL DEFAULT '',
  `coficina` varchar(4) NOT NULL DEFAULT '',
  `digcontr` varchar(2) NOT NULL DEFAULT '',
  `ccuenta` varchar(8) NOT NULL DEFAULT '',
  `clainter` varchar(1) NOT NULL DEFAULT '',
  `secinter` varchar(2) NOT NULL DEFAULT '',
  `fealrela` varchar(10) NOT NULL DEFAULT '0000-00-00',
  `space1` varchar(1) NOT NULL DEFAULT '',
  `fechape` varchar(10) NOT NULL DEFAULT '0000-00-00',
  `space2` varchar(1) NOT NULL DEFAULT '',
  `fecancel` varchar(10) NOT NULL DEFAULT '0000-00-00',
  `peyestat` varchar(1) NOT NULL DEFAULT '',
  `indaviso` varchar(1) NOT NULL DEFAULT '',
  `iugestor` varchar(6) NOT NULL DEFAULT '',
  `pehstamp` varchar(26) NOT NULL DEFAULT '',
  `indcoext` varchar(1) NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

(I have to put all in VARCHAR for test) Then, I want import this data with this sentence SQL

LOAD DATA LOCAL INFILE 'c:/mis/RBA/RBACalidad/Entradas/PEBC.PEFD.FIX.UNLO.TABLA008.D1140813.TXT'
INTO TABLE tabla008
FIELDS TERMINATED BY '' LINES TERMINATED BY '\r\n';

But, when run it, the result is:

https://i.sstatic.net/ClpyE.png

And the result must be:

https://i.sstatic.net/2NYou.png

I don't understand why this is the result, if my sentence SQL is fine!

Thanks

Upvotes: 0

Views: 241

Answers (2)

Alexander
Alexander

Reputation: 149

Well, I didn't want to do, but I did.

I use this:

LOAD DATA LOCAL INFILE 'c:/mis/RBA/RBACalidad/Entradas/TABLA008.D1140813.TXT'
INTO TABLE tabla008
(@row)
SET numclien=SUBSTRING(@row,1,8),
entidad=SUBSTRING(@row,9,4),
oficina=SUBSTRING(@row,13,4),
digcontrol=SUBSTRING(@row,17,2),
cuenta=SUBSTRING(@row,19,8),
clainter=SUBSTRING(@row,27,1),
secinter=SUBSTRING(@row,28,2),
fealrela=SUBSTRING(@row,30,10),
space1=SUBSTRING(@row,40,1),
fechape=SUBSTRING(@row,41,10),
space2=SUBSTRING(@row,51,1),
fecancel=SUBSTRING(@row,52,10),
space3=SUBSTRING(@row,62,1),
peyestat=SUBSTRING(@row,63,1),
indaviso=SUBSTRING(@row,64,1),
iugestor=SUBSTRING(@row,65,6),
pehstamp=SUBSTRING(@row,71,26),
indcoext=SUBSTRING(@row,97,1);

It isn't a elegant solution, but it works

Upvotes: 0

Axel Amthor
Axel Amthor

Reputation: 11096

Your problem is two-fold:

  • the import file doesn't fit to the tables column definition
  • your load statement doesn't consider the proper syntax of the csv file

If you have a definition "FIELDS TERMINATED BY ''" that means there is no field separator at all. Thus, the columns length apply:

Your row and the fields length:

   20232045001100000700002769T011998-01-22 0001-01-01 
           ^  split into "numclien"
                           ^split in to "centidad"
   ...

If your CSV contains tabs (\t), change to

     FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n';

which would be the normal csv syntax. As of your example, the file contains one or more spaces as field separator. You might test

     FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\r\n';

but I expect this to produce empty fields in the rows. It's better to prcess the file and replace sequences of spaces by tab.

And definitively the csv should fit to the columns length or the columns should have an according type set (like date, timestamp etc, see comments above)

Upvotes: 1

Related Questions