Solved

mySQL COLUMNS TERMINATED BY DELETE Character?

Posted on 2015-02-22
11
99 Views
Last Modified: 2015-03-03
I am trying to load large text files that are delimited by DEL. Using Coldfusion I can do chr(127), but that doesn't seem to work here

LOAD DATA LOCAL INFILE "C:\\inetpub\\vhosts\\mysite.com\\dataload.data"
INTO TABLE tbl_data
COLUMNS TERMINATED BY '\0x7F'
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Open in new window



I have been searching and trying a number of things that look like they can be DEL characters. Does anyone have any suggestions for the COLUMNS TERMINATED BY line?
0
Comment
Question by:theideabulb
  • 7
  • 4
11 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 40624574
Are you absolutely sure your columns are delimited by DEL - That seems really odd and something I've NEVER come across.

I might suggest you do a full search and replace using a decent text editor and use something more acceptable before trying to import into your database.
0
 

Author Comment

by:theideabulb
ID: 40624865
No this is 100% the case.  I have been parsing it through CF for years, but this is MUCH faster.   I need to do these files all the time time, so this is indeed what I am looking for.  I just need to know the character/code/terminator for DEL for this use.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 40624888
OK. No idea!!

Any chance you can post a sample of the DEL delimited file. May help us to figure it out. We can at least view the raw data which will give us something to go on.
0
 

Author Comment

by:theideabulb
ID: 40624894
Sure, here you go.  It is attached
0
 

Author Comment

by:theideabulb
ID: 40624896
Lets try that again, i don't think it let me upload something that had a .data extension
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:theideabulb
ID: 40624897
Ok, that didn't work either.  Lets try this. I uploaded it to box.com

https://app.box.com/s/jr1fge054svhklrgydzo4i7wwylzf1l9
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 40624935
No. Still no go...Sorry.

Maybe someone else has come across this before, but I certainly haven't (and I hope I never do!)

Whoever thought that was a good idea needs a serious talking to ;)
0
 

Author Comment

by:theideabulb
ID: 40624938
Thanks for trying.  I agree with you and that is why I am here asking.  It was actually quite simple doing in ColdFusion by using chr(127), but its slow, especially when you need to process 120+ files which is a few hundred thousand lines.  mySQL loader is MUUCCHHHHH faster :)
0
 

Accepted Solution

by:
theideabulb earned 0 total points
ID: 40624940
Here I found the answer

COLUMNS TERMINATED BY X'7F'
0
 
LVL 42

Expert Comment

by:Chris Stanyon
ID: 40625291
Good job. I'll try and remember that if I ever come across such a file :)
0
 

Author Closing Comment

by:theideabulb
ID: 40641309
I found my own answer.  Thank you.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue. Here we will try to understand some of the very common scenarios for loading data …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now