Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

mySQL COLUMNS TERMINATED BY DELETE Character?

Posted on 2015-02-22
11
Medium Priority
?
135 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 44

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 44

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 44

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 44

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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

715 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