Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

mySQL COLUMNS TERMINATED BY DELETE Character?

Posted on 2015-02-22
11
Medium Priority
?
143 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 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
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
Course of the Month9 days, 14 hours left to enroll

927 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