Mysql - Data without spaces

Question

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:

And the result must be:

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

Thanks

Could you provide what should be the output? with the image you provided I can only speculate that that is due to the length of you variables. — Saechel
– Saechel, Commented Sep 2, 2014 at 23:15
@Alexander You should learn more about MySQL datatypes. There are specific types of data for each different data and instead you're giving everything the type varchar which is REALLY BAD considering the data you have! — Prix
– Prix, Commented Sep 3, 2014 at 0:40

Axel Amthor · Accepted Answer · 2014-09-03 00:57:12Z

1

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)

answered Sep 3, 2014 at 0:57

Axel Amthor

11.1k1 gold badge28 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alexander Over a year ago

Hi I put VARCHAR() for all fields as test. The input I can't modify. I have verify that the import file yes fit to the tables column definition, both are 96 of lenght. I put FIELDS TERMINATED BY '' because just there isn't separatores. I have try with FIELDS TERMINATED BY ' ' but the result is other (bad) Thanks

Axel Amthor Over a year ago

Well, some are hopeless, this won't work as long as you can't distinguish between the fields either by a separator or by proper length; and as long as the input is not hardcoded in the bios of your server you can modify - and even if would be there you could make a copy ...

Alexander · Accepted Answer · 2014-09-03 18:24:03Z

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

Collectives™ on Stack Overflow

Mysql - Data without spaces

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related