Solved

mySQL COLUMNS TERMINATED BY DELETE Character?

Posted on 2015-02-22
11
127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 43

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 43

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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

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
 

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 43

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 43

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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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